ORA-01652 cannot extend temp segments through 256 (in tablespace temp)

When the data volume of a table in the Oracle9i database exceeds 0.1 billion, the web page remains blank for a long time when the table is queried recently. Therefore, we need to optimize the table. The optimization plan is as follows: Temporary tablespace mainly

When the data volume of a table in the Oracle9i database exceeds 0.1 billion, the web page remains blank for a long time when the table is queried recently. Therefore, we need to optimize the table. The optimization plan is as follows: Temporary tablespace mainly

[Old system] When the data volume of a table in Oracle9i database exceeds 0.1 billion, the web page remains blank for a long time when the table is queried recently. Therefore, optimize the table. The optimization plan is as follows:

1. Create a temporary table

[SQL]

2. Delete water table data

[SQL]

3. assign temporary table data to the water table
Insert into water select * from tmptable;

The first error is: The ORA-01652 cannot extend the temp segment through 256 (in tablespace temp. This error tells us that the temporary tablespace is insufficient.

Temporary tablespace is mainly used for sorting operations in the database [such as creating indexes, order by and group by, distinct, union/intersect/minus/, sort-merge, join, and analyze commands], provides temporary computing space for managing indexes, such as creating indexes, importing data to IMP, and accessing views, after the computation is completed, the system automatically cleans it up.

When the temporary tablespace is insufficient, the operation speed is abnormally slow, and the temporary tablespace rapidly increases to the maximum space (the limit of expansion), and is generally not automatically cleared.

If the temporary tablespace is not set to auto-scale, when the temporary tablespace is not enough, the current affairs execution will report an error where the ora-01652 cannot extend the temporary segment, and the solution is also simple: 1. Set automatic expansion of temporary data files, or 2. Increase the temporary tablespace.

Temporary tablespace operations:

Query the default temporary tablespace:

SQL> select * from database_properties where property_name = 'default _ TEMP_TABLESPACE ';

Query the temporary tablespace status:

SQL> select tablespace_name, file_name, bytes/1024/1024 file_size, autoextensible from dba_temp_file

Extended temporary tablespace:

Method 1: Increase the temporary file size:

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize100m;

Method 2: Set the temporary data file to automatic extension:

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5 m maxsize unlimited;