Normally, after completing the SELECT statement, Create INDEX, etc. After the sort operation using the TEMP tablespace, Oracle is automatically released to the temporal segment A. However, some have that we will encounter until the time period is not released, and the TEMP table space is almost full, and even we restarted the database still does not solve the problem. This problem is often asked by netizens in the forum. Here I summarize it, give several processing methods.
French one, restart library
When the library reboots, the SMON process completes the release of the temporal segment, the cleaning operation of the TEMP table space, but many when our library does not allow Down, so this method lacks a little application opportunity, but this method is still very It is easy to use.
A method given by Metalink
Modify the Storage parameters of the TEMP table space to make the SMON process to the temporal segment to achieve the purpose of cleaning and TEMP table space.
SQL> ALTER TABLESPACE TEMP INCREASE 1; SQL> ALTER TABLESPACE TEMP INCREASE 0;
A method I often use, the specific content is as follows:
1. Use the following statement a to see who is in use
Select Username, SID, Serial #, SQL_Address, Machine, Program, TableSpace, SegType, Contents from V $ Session SE, V $ sort_usage suwhere se.saddr = su.session_addr
2, those processes that are using the temporary section
SQL> ALTER SYSTEM KILL Session 'SID, Serial #';
3, retract the TEMP table space
SQL> ALTER TABLESPACE TEMP COALESCE;
A method of using diagnostic events is also a way I think it is "killer"
1. Determine TS # of the TEMP table space
SQL> SELECT TS #, Name from sys.ts $; ts # name ----------------------- 0 SYSYEM 1 RBS 2 Uses 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: TEMP table space TS # is 3 *, SO TS # 1 = 4
other:
1. Reasons for the above problems I think it may be due to large sorting beyond the space allowable range of TEMP tablespace. It is also possible to contain other abnormal factors.
2. Note that TEMP and other spaces are one of the daily duties of DBA. We can use the tools such as Toad, Object Browser, or use the following statement:
