ORA-01652: Unable to extend temp segment by 128 (in Tablespace temp)

Yesterday, the system submitted approval flow has been reported "system busy", to view the background, found to be "ORA-01652: unable to extend the temp segment by 128 (in the tablespace temp)", this indicates that the temporary table space is insufficient.

Since there is a problem with the temp table space, of course it will start with the temp table space. The first thing to note is the role of the temp table space, which is primarily used as an operation that needs to be sorted.

1. Temporary tablespace is used to store temporary data for sorting operations, such as large queries, creating indexes, and federated queries, and each user has a temporary table space.
2. For large operations frequently, (large queries, large classification queries, large statistical analysis, etc.), a separate temporary table space should be specified for easy administration.
3. Assigning a User A separate temporary tablespace, typically for large product databases, OLTP databases, database warehouses for small products, you do not need to set up temporary table spaces, using the default temporary table space.

Normally, after a SQL execution, the system automatically reclaims the space allocated to the user after the result is returned. So that this part of the space can be redistributed to other users.

OK, now that the problem is in the temp table space, look at the temporary tablespace information:

SELECT * from Dba_tablespaces; --View database table space
SELECT * from dba_data_files--view tablespace file
SELECT * from V$tempfile; --View temporary tablespace files
SELECT * from Dba_temp_files; --View temporary table space

In the second statement you can see that the bytes is the size of the table space, where the units are bytes, so it is best to look at this:

Select tablespace_name,file_name,bytes/1024/1024 "Table space size (M)", autoextensible from Dba_temp_files;

You can see that the temporary table space size is 70 megabytes, too small, modify the Tablespace file (you should also add a tablespace file to expand the table space, but in order to tidy up, do not want to do so), increase to 100 trillion, the following steps:

1. Create temporary tablespace TEMP2 tempfile '/oradata/db/temp02.dbf ' SIZE 150M reuse autoextend on NEXT 640K MAXSIZE UNL imited; --Create transit temporary table space
2. Alter DATABASE default temporary tablespace temp2; --Change the default temporary table space to the new temporary tablespace that you just created Temp2
3. Drop Tablespace temp including contents and datafiles;--delete the original temporary table space
4. Create temporary tablespace TEMP tempfile '/oradata/db/temp01.dbf ' SIZE 150M reuse autoextend on NEXT 640K MAXSIZE Unli mited; --Re-create temporary table space
5. Alter DATABASE default temporary tablespace temp; --Resets the default temporary tablespace to the newly created temp table space
6. Drop tablespace Temp2 including contents and datafiles;--remove interim tablespace for staging
7. Alter USER roll temporary tablespace temp; --Re-specify the user tablespace as a temporary tablespace for rebuilding, which I did not actually execute.

Oh, look at the system, the exciting moment is up, the submission approval is successful.

Article derived from: http://www.cnblogs.com/rootq/archive/2009/02/05/1384539.html

Add:

To view the size and usage of all tablespace (excluding temporal tablespace) in the database

Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
D.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (ROUND (d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,
2),
' 990.99 ') "Use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE d.tablespace_name = f.tablespace_name;

to extend a tablespace by adding a tablespace file:

Alter tablespace sysaux add datafile '/oradata/db/sysaux02.dbf ' size 480m;