How do I handle the problem of temp table space full in Oracle?

oracle| problem
Normally, Oracle automatically frees up temporary segment A when it completes a sort operation that uses the temp table space, such as a SELECT statement, create INDEX, and so on. But some of us will encounter the temporary segment has not been released, the temp table space is almost full situation, even if we restart the database still does not solve the problem. This issue in the forum is also often asked by netizens, the following I sum up, give several processing methods.



Law One, restart the library



Library restart, the Smon process will complete temporary segment release, temp table space cleanup operations, but many times our library is not allowed down, so this method is missing a bit of application opportunities, but this method is still very useful.



A method given by law two and Metalink



Modify the temp table Space storage parameter, let the Smon process view the temporary segment, so as to achieve cleanup and temp table space.



Sql>alter tablespace temp Increase 1;
Sql>alter tablespace temp Increase 0;



Method Three, I commonly used a method, the specific contents are as follows:



1. Use the following statement A to see who is using the temporary paragraph



SELECT username,
Sid
serial#,
Sql_address,
Machine
Program
Tablespace,
Segtype,
Contents
From V$session SE,
V$sort_usage su
WHERE se.saddr=su.session_addr



2, those who are using the temporary segment of the process

Sql>alter system kill session ' sid,serial# ';



3, the temp table space back to shrink

Sql>alter tablespace TEMP Coalesce;



Method of using diagnostic events is also a method that I consider to be "killer"



1, determine the temp table space ts#



Sql>select ts#, name from sys.ts$;

ts# NAME
-----------------------
0 Sysyem
1 RBS
2 USERS
3* TEMP
4 TOOLS
5 INDX
6 Drsys

2. Perform cleanup operations


Sql>alter session Set Events ' immediate trace name drop_segments level 4 ';

Description
The temp table space is ts# to 3*, so ts#+ 1 = 4



Other:



1, the reasons for the above problems I think may be due to the large sort of the temp table space to allow the scope of space caused. may also contain other anomalous factors.



2, the view of the state of space such as temp is one of the day-to-day responsibilities of the DBA, we can through the toad, Object browser and other tools to do, you can use the following statement:



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)/(1024 * 1024), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024 * 1024), 2 max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD. Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024 * 1024), 2 TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER BY 4 DESC