Tablespace _ error caused by temporary tablespace: Small ora-01652 example

Tablespace _ error caused by temporary tablespace: Small ora-01652 example

Error Reporting temporary tablespace Insufficiency: ora-01652

When performing the insert operation, the following error is reported: the temporary tablespace is insufficient.

The error message is as follows:

View the extended temporary tablespace, which has been automatically extended to 32 GB.

Review the usage of temporary tablespace:

For temporary tablespaces, sorting operations may use temporary tablespaces. When the sort area Memory is insufficient, temporary tablespace is used. For sorting, the create index, order by, and group by operations may all use temporary tablespace when collecting statistics. The sorting operation will first sort in the sort area Memory. Once the memory of the sort area (memory sorting area) is insufficient, the temporary tablespace sort in disk (disk sorting, temporary tablespace ).

The possibility of this error is that you need to create indexes at the same time during insertion, so you need to use a large number of temporary tablespace. In addition, to increase the speed, multiple insert tasks are started in parallel, so the temporary tablespace is insufficient.

 

Solution:

After an error occurs, all data insertion operations are stopped. The plan was adjusted as follows:

1. Expand the size of the temporary tablespace;

2. Reduce the number of parallel tasks;

3. Delete the index. After all data is inserted, re-create the index;

4. After the task is completed, reclaim the temporary tablespace;

 

The first two steps are attempted first, and the problem is successfully avoided:

1. Expand the temporary tablespace to 60 GB.

2. Reduce the concurrency by 4 to 2. Execute the insert action one by one.

Result: The large data volume is successfully inserted.