ORA-01652 cannot extend the temp segment through 256 (in table space temp)

"It's an old system." Oracle9i a table with more than 100 million data in a database, and when the table was recently queried for a long time, the Web page was blank, so consider optimizing the table. The optimization plan is as follows:

1. Create a temporary table

Create  Global  temporary  table  tmptable on  commit  preserve  rows  as  Select  * from  water;

2. Delete water table data

Delete from water;
Commit

3. Temporary table data assigned to water table
INSERT INTO water select * from tmptable;

The first step is an error: ORA-01652 cannot extend the temp segment by 256 (in table space temp). This error tells us that there is not enough temporary table space.

The primary purpose of a temporary tablespace is to sort operations in a database [such as creating indexes, orders by and group BY, DISTINCT, union/intersect/minus/, sort-merge and join, analyze commands], managing indexes [ such as the creation of indexes, IMP for data import], access to views, and so on to provide a temporary computing space, when the operation is completed, the system will automatically clean up.
When the temporary table space is low, the operation speed is unusually slow, and the temporary table space grows rapidly to the maximum space (the limit of the extension), and generally does not clean up automatically.

If the temporary table space is not set to auto expand, then the transaction execution will report ora-01652 that cannot extend the temporary segment if the temporary table space is not sufficient: 1, set up the temporary data file automatic expansion, or 2, increase the temporary table space.

Temporary table space Related actions:

To query the default temp table space:

Sql> SELECT * from database_properties where property_name= ' default_temp_tablespace ';

Query temp table space status:

Sql> Select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from Dba_temp_file

To extend a temporary table space:

Method One, increase the temporary file size:

sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF ' resize100m;

Method Two, set the temporary data file to automatic expansion:

sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF ' autoextend on next 5m MaxSize Unlimited;