ORA-01652 Error: Unable to extend temp segment through 128 (in table space temp)

In an Oracle database, order by or group by, index creation and re-creation, distinct operations, Union & intersect & Minus Sort-merge joins, Analyze operations, Exceptions , and so on, can produce a lot of temporary data. If you have a table of employee information, the database is the time to set up records to save. If a user queries by using the ORDER by sort statement to specify the sort by employee number, all the records that are created after sorting are temporary data. Typically, Oracle databases first store these temporary data in the PGA (Program Global Area) of memory. If the amount of data is too large for the PGA to survive, it will be placed in the temporary table space.

By default, temporary tablespace spaces are shared by all users. Of course, you can specify a separate temporary table space for special users. Temporary table spaces can be reused.

When the temp table is out of space, it will report: ORA-01652: Unable to extend the temp segment through 128 (in table space temp)

At this time, take it for granted that you want to see the use of temporary table space:

Select Tablespace_name, Bytes, user_bytes, user_bytes/bytes,file_name from Dba_temp_files;

99%? But this usage does not have much meaning, because when a new temporary file (such as TEMP02.DBF), and then run an order by, this rate of use directly to 99%. Then change the other order BY statement Test, found that the data can be normally detected, indicating that the use of temporary table space with the normal table space usage rate, there are different (specifically how a different, it is not known).

In other words, when the error, its usage rate may be 99%, the normal operation, the usage rate may also be 99%.

When the error, the temporary table space is really inadequate. But because the temporary table space performance is the DBF file, that expands the space to be much more convenient. The original temporary files can be increased directly, and temporary files can be added to achieve the objective of extending the temporary tablespace. If you have a temporary table full of space too fast, this approach will not cure the symptoms. You can use the V$sort_usage and v$sort_segment two views to analyze which users and which SQL caused temporary table space to skyrocket, and then targeted resolution. In addition, optimizing SQL and indexing the fields of the query can also reduce the amount of temporary tablespace space. In Oracle, if a table has no indexes, all of the data is copied to the temporary tablespace, and if there is an index, the data is typically copied into the temporary tablespace.

In addition to the above temporary table space really full of the situation, the query will be an error, there is a situation, will report the same mistake, that is, temporary file offline.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

In the V_$tempfile view, the status of the temporary file is recorded, one is online, and the other is offline. When the state is offline, the query may also report this error.

Of course, if a temporary file offline, the size of the query is also not found.

Statements related to temporary table spaces:

--Query the temporary tablespace space used by the user:

Select Username,default_tablespace,temporary_tablespace from Dba_users;

--Query temporary table space size and usage:

Select Tablespace_name, Bytes, user_bytes, user_bytes/bytes,file_name from Dba_temp_files;

--Query whether the temporary file is online:

Select Name,status from V$tempfile;

--Modify temporary files online (offline) status:

ALTER DATABASE Tempfile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02. DBF ' online (offline);

--Increase the temporary file size (increase the original file):

ALTER DATABASE Tempfile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01. DBF ' resize 100m;

--Increase temporary tablespace space by adding new temporary files:

Alter tablespace temp add tempfile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02. DBF ' size 4000m;

--Delete temporary files:

ALTER DATABASE Tempfile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02. DBF ' drop;

--Set temporary files to expand automatically:

ALTER DATABASE Tempfile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01. DBF ' autoextend on next 5m MaxSize Unlimited;

--Turn off (start) automatic growth of temporary files:

ALTER DATABASE Tempfile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01. DBF ' Autoextend off (on);