Temporary tablespace 100% solution

After the Select statement, create index, and other sorting operations using the TEMP tablespace are completed, Oracle Automatically releases the temporary segment. However, if some temporary segments are not released, the TEMP tablespace is almost full, and even the database is restarted, the problem remains unsolved. The following describes several solutions:
Restart Database
When the database is restarted, the Smon process will release the temporary segment and clean up the TEMP tablespace. However, in many cases, our database cannot be down, therefore, this method lacks some application opportunities, but it is still very useful.

Modify the storage parameters of the TEMP tablespace
This method comes from Metalink. It allows the Smon process to view the temporary segments to clean up and TEMP tablespaces.
SQL> alter tablespace temp increase 1;
SQL> alter tablespace temp increase 0;
The above method does not work for 9I and 10 Gb,
SQL> alter tablespace temp PCTINCREASE 1;
Alter tablespace temp PCTINCREASE 1
*
Row 3 has an error:
ORA-02142: the alter tablespace option is missing or invalid
Kill session, recycle and contract

A. check who is using the temporary segment.
SQL> 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
Kill a process using a temporary segment
SQL> Alter system kill session 'sid, serial #';
Shrink TEMP tablespace
SQL> Alter tablespace TEMP coalesce;
Use diagnostic events
Determine the ts of the TEMP tablespace #
SQL> select ts #, name from sys. ts $;
TS # NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3 * TEMP
4 TOOLS
5 INDX
6 DRSYS
Perform cleanup
SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 4 ';
Note:
Temp tablespace TS # is 3 *, So TS # + 1 = 4
Here is the description from Metalink.
The DROP_SEGMENTS event
~~~~~~~~~~~~~~~~~~~~~~~
Available from 8.0 onwards.
 
DESCRIPTION
Finds all the temporary segments in a tablespace which are not
Currently locked and drops them.
For the purpose of this event a "temp" segment is defined as
Segment (seg $ entry) with TYPE # = 3. Sort space in a TEMPORARY
Tablespace does not qualify under this definition as such
Space is managed independently of SEG $ entries.

PARAMETERS
Level-tablespace number + 1. If the value is 2147483647 then
Temp segments in ALL tablespaces are dropped, otherwise, only
Segments in a tablespace whose number is equal to the LEVEL
Specification are dropped.

NOTES
This routine does what SMON does in the background, I. e. drops
Temporary segments. It is provided as a manual intervention tool which
The user may invoke if SMON misses the post and does not get
Clean the temp segments for another 2 hours. We do not know whether
Missed post is a real possibility or more theoretical situation, so
We provide this event as an insurance against SMON misbehaviour.

Under normal operation there is no need to use this event.

It may be a good idea
Alter tablespace <tablespace> coalesce;
After dropping lots of extents to tidy things up.

* SQL Session (if you can connect to the database ):
Alter session set events 'immediate trace name DROP_SEGMENTS level TS # + 1 ';

The TS # can be obtained from v $ tablespace view:
Select ts # from v $ tablespace where name = '<Tablespace name> ';

Or from SYS. TS $:

Select ts # from sys. ts $ where name = '<Tablespace name>' and online $! = 3;

If ts # is 5, an example of dropping the temporary segments in that tablespace
Wocould be:

Alter session set events 'immediate trace name DROP_SEGMENTS level 6 ';
Common SQL statements related to temporary tablespace

View the usage of temporary tablespace (the GV _ $ TEMP_SPACE_HEADER view can only be queried under the sys user)
The GV _ $ TEMP_SPACE_HEADER view records the usage and unused sizes of temporary tablespace.
The bytes field in the dba_temp_files view records the total size of the temporary tablespace.
SELECT temp_used.tablespace_name,
Total-used as "Free ",
Total as "Total ",
Round (nvl (total-used, 0) * 100/total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM (bytes_used)/1024/1024 used
From gv _ $ TEMP_SPACE_HEADER
Group by tablespace_name) temp_used,
(SELECT tablespace_name, SUM (bytes)/1024/1024 total
FROM dba_temp_files
Group by tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name

Query SQL statements that consume temporary tablespace Resources
Select se. username,
Se. sid,
Su. extents,
Su. blocks * to_number (rtrim (p. value) as Space,
Tablespace,
Segtype,
SQL _text
From v $ sort_usage su, v $ parameter p, v $ session se, v $ SQL s
Where p. name = 'db _ block_size'
And su. session_addr = se. saddr
And s. hash_value = su. sqlhash
And s. address = su. sqladdr
Order by se. username, se. sid

SQL statement used to view the size of the current temporary tablespace AND THE OCCUPIED temporary tablespace
Select sess. SID, segtype, blocks * 8/1000 "MB", SQL _text
From v $ sort_usage sort, v $ session sess, v $ SQL
Where sort. SESSION_ADDR = sess. SADDR
And SQL. ADDRESS = sess. SQL _ADDRESS
Order by blocks desc;
In my opinion, temporary tablespace groups can effectively reduce the usage of temporary tablespace by 100%.

For more information, see