Oracle temp tablespace Processing Method

I. Check Process
(1) We can check the Database Alert Log
Example:/U0/admin/SM/bdump/alert_sm.log
The temp tablespace error is not reported in the database. Generally, no error is reported. You can ignore the error because no temp tablespace is required.

(2) We can use this statement to check
Select se. username, Sid, serial #, SQL _address, machine, program, tablespace, segtype, Contents
From v $ session se, V $ sort_usage Su
Where se. saddr = Su. session_addr;

In fact, V $ session is the session information, and V $ sort_usage is the session that uses the sorting segment.
Check whether statements in the current database are using sorting segments. If yes, use the following statement to delete the statements:
SQL> alter system kill session 'sid, serial #';

(3) check whether the temp tablespace is automatically expanded.
SQL> select file_name, Bytes/1024/1024 "MB", autoextensible, tablespace_name from dba_temp_files;

(4) query the default temporary tablespace in the current database
SQL> select * From database_properties where property_name like 'default % ';
Or SQL> select username, temporary_tablespace from dba_users;

Ii. handling process
(1) restart the database to see if the temporary tablespace has been released
The SMON process will release the temporary segments and clean up the temp tablespace. However, many times our database is not allowed to go down. Therefore, this method lacks some application opportunities, however, this method is quite useful.

(2) If the temp tablespace is automatically extended, we recommend that you first change it to non-auto-extended
Since the temp tablespace of the database is automatically extended, it is possible that it will continue to use new space. We recommend that you first change it to non-auto-extended.
For example:
Alter database tempfile '/U0/oradata/SM/temp01.dbf' autoextend off;

(3) If the temp tablespace is not automatically extended and the space is small
For example, if the temp tablespace of the database is only 1.6 GB, it can be expanded to about 3 GB.
SQL> alter system temp add tempfile '/U0/oradata/SM/temp02.dbf 'size 3096 m;

(4) If the temp tablespace becomes too large due to automatic expansion, you can create a new temp tablespace to replace it.
For details, see "how to reconstruct and switch the temporary tablespace in Oracle9i" in master eygle"
Http://www.eygle.com/archives/2006/04/oracle9i_default_temporary_tablespace.html
For example, replace the temp tablespace with the temp2 tablespace.
SQL> create temporary tablespace temp2 tempfile '/opt/Oracle/oradata/Conner/temp1.dbf' size 10 m autoextend off;
SQL> alter tablespace temp add tempfile '/opt/Oracle/oradata/Conner/temp2.dbf 'size 20 m;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp;
Finally, delete the temp file on the operating system to release the space.

(5) If the version is Oracle 10 Gb or above, you can shrink the tablespace.
Select se. username, Sid, serial #, SQL _address, machine, program, tablespace, segtype, Contents
From v $ session se, V $ sort_usage Su
Where se. saddr = Su. session_addr;
In fact, V $ session is the session information, and V $ sort_usage is the session that uses the sorting segment.
Check whether statements in the current database are using sorting segments. If yes, use the following statement to delete the statements:
SQL> alter system kill session 'sid, serial #';
SQL> alter tablespace temp coalesce;

(6) A method provided by Metalink on the Internet
Modify the storage parameters of the temp tablespace so that the SMON process can view the temporary segments to clean up and temp tablespace.
SQL> alter tablespace temp increase 1;
SQL> alter tablespace temp increase 0;

(7) An online method for event Diagnosis
Determine the Ts of the temp tablespace #
SQL> select ts #, name from SYS. Ts $;

TS # Name
----------------------------------------------------------------------
0 System
1 undotbs1
2 temp
3 indx
4 tools
5 Users
6 dkh_data
7 dkh_indx
8 phs_data
9 phs_indx

10 rows selected.

Perform cleanup
SQL> alter session set events 'immediate trace name drop_segments level 3 ';

Note:
Temp tablespace ts # is 2 *, So ts # + 1 = 3

PS notes:
(1) In Oracle, the temp tablespace is independent from other tablespaces (rollback tablespace undotbs, data table space datatbs, etc.), because its data is temporary and does not need to be saved
Therefore, data tables are different:
Temp: dba_temp_files v $ tempfile
Others: dba_data_files v $ datafile
In conclusion, the size of the entire database is determined by temp + other tablespaces. Generally, we only calculate the size of other tablespaces. For example, in the monthly report

(2) The tablespace to be created is different.
Temp tablespace:
SQL> create temporary tablespace temp2 tempfile '/opt/Oracle/oradata/Conner/temp1.dbf' size 10 m autoextend off;

Data Table space:
SQL> Create tablespace test_a datafile '/home/Oracle/oradata/test/test_a.dbf' size 100 m extent management local segment space management auto;

Undo tablespace
SQL> Create undo tablespace undotbs_01 datafile 'C:/Oracle/ordata/tsh1/undo0101.dbf' size 100 m reuse autoextend off;

(3) The methods for adding tablespaces are different.
Temp tablespace
SQL> alter tablespace temp add tempfile '/opt/Oracle/oradata/Conner/temp2.dbf 'size 20 m autoextend off;

The data table space is the same as the Undo tablespace.
SQL> alter tablespace test_a add datafile '/opt/Oracle/oradata/Conner/ABC. dbf' size 10 m autoextend off;
SQL> alter tablespace undotbs_01 add datafile '/opt/Oracle/oradata/Conner/undotbs_02.dbf' size 10 m autoextend off;

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/eastsea/archive/2009/06/26/4299797.aspx