Solution to Oracle temp tablespace increasing too fast

Temporary tablespace is a temporary tablespace in Oracle. The temporary tablespace is mainly used to provide temporary computing space for database operations such as sorting, index management, and view access, after the computation is completed, the system automatically cleans it up. When sort is required in Oracle, when sort_area_size in PGA is not enough, the data will be sorted in the temporary tablespace. If any exception occurs, it will also be placed into the temporary tablespace. Normally, after the SELECT statement, create index, and other sorting operations using the temp tablespace are completed, Oracle will automatically release 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.
Sorting is resource-consuming. When the temp tablespace is full, the key is to optimize your statements and minimize the number of orders.
When the temp tablespace is full:
1. Modify parameters (for 8i and earlier versions only)
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;
Ii. Kill session
1. Run the following statement A to check who is using the temporary segment.
Select se. username, se. Sid, se. Serial #, se. SQL _address, se. Machine, se. Program, Su. tablespace,
Su. segtype, Su. Contents from V $ session se, V $ sort_usage Su
Where se. saddr = Su. session_addr
2. Kill the process using the temporary segment
SQL> alter system kill session 'sid, serial #';
3. shrink the temp tablespace.
SQL> alter tablespace temp coalesce;
Note:
This method can only be used to manage the tablespace (Dictionary managed tablespace) in the dictionary ). Local Management of tablespaces (LMT: Local managed tablespace. After 9i, you can only create local-managed tablespaces.
Create tablespace test datafile 'd:/test01.dbf 'size 5 m extent management dictionary
Create tablespace test datafile 'd:/test01.dbf 'size 5 m extent management local;
3. Restart the 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.
4. Using Event diagnosis is also the most effective method.
1. Determine the Ts of the temp tablespace #
SQL> select ts #, name from SYS. Ts $;
TS # Name
----------------------------------------
0 System
1 undotbs1
2 sysaux
3 temp
4 Users
5 undotbs2
2. Perform the cleanup operation.
SQL> alter session set events 'immediate trace name drop_segments level 4 ';
Note:
Temp tablespace ts # is 3, So ts # + 1 = 4

Recreate the temp tablespace:
Temporary tablespace cannot directly drop the default temporary tablespace, but we can do it using the following method.
Preparation: view the current temporary tablespace
SQL> select name from V $ tempfile;
Name
-----------------------
D:/Oracle/oradata/test/temp01.dbf
SQL> select username, temporary_tablespace from dba_users;
Username temporary_tablespace
------------------------------------------------------------
Mgmt_view temp
Sys temp
System temp
Dbsnmp temp
Sysman temp
1. create temporary tablespace for transit
Create temporary tablespace temp1 tempfile 'e:/Oracle/oradata/orcl/temp02.dbf' size 512 M reuse autoextend on next 1 m maxsize unlimited;
2. Change the default temporary tablespace to the newly created temporary tablespace temp1.
Alter database default temporary tablespace temp1;
3. Delete the original temporary tablespace
Drop tablespace temp including contents and datafiles;
4. Recreate the temporary tablespace
Create temporary tablespace temp tempfile 'e:/Oracle/oradata/orcl/temp01.dbf' size 512 M reuse autoextend on next 1 m maxsize unlimited;
5. Reset the default temporary tablespace to the new temp tablespace.
Alter database default temporary tablespace temp;
6. Delete temporary tablespace for transit
Drop tablespace temp1 including contents and datafiles;
7. If necessary, specify the user tablespace as the temporary tablespace for reconstruction.
Alter user Arbor temporary tablespace temp;
View the tablespace statement, but the temp tablespace cannot be found:
Select upper (F. tablespace_name) "tablespace name ",
D. tot_grootte_mb "tablespace 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 ),
'1970. 99') "usage ratio ",
F. total_bytes "Idle space (m )",
F. max_bytes "maximum 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

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/tianlesoftware/archive/2009/10/19/4697417.aspx

Alter table tb_ori_0004 add primary key (f0004_001d, f0004_002v, f0004_003v, f0004_004v, f0004_005v );
-- Create/recreate Indexes
-- Create index idx_tb_ori_0004 on tb_ori_0004 (f0004_001d, f0004_002v, f0004_003v, f0004_004v, f0004_005v );
-- Drop table tb_ori_0004
Create index seq_index_0004 on tb_ori_0004 (ob_seq_id );
Create index idx_04_01 on tb_ori_0004 (f0004_001d );
Create index idx_04_09 on tb_ori_0004 (f0004_009n );
Create index idx_04_01_02 on tb_ori_0004 (f0004_001d, f0004_002v );
Create index idx_04_03_04_05 on tb_ori_0004 (f0004_003v, f0004_004v, f0004_005v );
Create index idx_04_01_03_04_05 on tb_ori_0004 (f0004_001d, f0004_003v, f0004_004v, f0004_005v );
-- View tablespaces
Select * From dba_tablespaces
-- Delete a tablespace
Drop tablespace tbs_develop1
-- Delete table null and Data
Drop tablespace tbs_develop1 including contents;
-- Create a table empty
Create tablespace tbs_develop1 datafile
'/APP/Oracle/product/oradata/develop1/users02.dbf' size 50 m autoextend on next 64 K,
'/APP/Oracle/product/oradata/develop1/tbs_develop00001.dbf' size 50 m autoextend on next 64 K,
'/APP/Oracle/product/oradata/develop1/tbs_develop+2.dbf' size 50 m autoextend on next 64 K;
-- View temporary tablespace
Select * From dba_temp_files
-- Create temporary tablespace
Create temporary tablespace tbs_develop1 tempfile
'/APP/Oracle/product/oradata/develop1/temp01.dbf' size 50 m autoextend on next 64 K,
'/APP/Oracle/product/oradata/develop1/temp02.dbf' size 50 m autoextend on next 64 K,
'/APP/Oracle/product/oradata/develop1/temp03.dbf' size 50 m autoextend on next 64 K;

-- View the percentage of tablespaces used
Select * From dba_tablespace_usage_metrics
-- View data files
Select * From dba_data_files order by 3, 1
-- Add data files to the tablespace
Alter tablespace tbs_s1 add datafile '/APP/Oracle/product/oradata/S1/tbs_s1_6.dbf' size 50 m autoextend on next 64 K;