The reason for the high usage of ORACLE temporary table space and the temporary solution

The database temp temporary table space is increased, typically set temporary tablespace size when data is installed, and is set to grow automatically by default. This raises the question that the temp table space expands automatically in the database usage, resulting in insufficient disk space usage.

The increase in temporary table space is mainly used in the following situations:
1, order BY or group by (disc sort accounted for the main part);
2, index creation and re-creation;
3, distinct operation;
4, Union & intersect & minus Sort-merge joins;

5, Analyze operation;
6, some anomalies will also cause the surge of the temp.

To resolve the temp temporary table space size problem:

A. Directly reduce the temp table space size

ALTER DATABASE tempfile ' D:POWERDMSDB\DMSDB\TEMP01.DBF ' resize 1024M;

This statement modifies the size of the temp table space directly, but may not be successful because the temp table space cannot be narrowed when the temp usage is 100% or if there is currently a session occupied.

To query the current session:

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

Use this SQL statement to query the current session, and then kill the current session:

Alter system kill session ' sid,serial# '

After you execute this statement, you can resize the space size of the temp table space.

Note: This method is only temporarily narrowing the temp table space and will continue to grow later.

Method Two:

To rebuild the temp temporary table space and set the growth cap value, set this value in advance to estimate that the setting is reasonable, otherwise there will be an error when you increase to this value, and the database will automatically release the temp temporary table space when the value is increased to the upper limit within a reasonable range.

To rebuild temp temporary table space:

1.--Start the database

Startup

2.--Create transit temporary table space
Create temporary tablespace TEMP2 tempfile ' D:\PowerDmsDB\DMSDB\TEMP02. DBF ' SIZE 2048M reuse autoextend on NEXT 640K MAXSIZE 5120M;
3.--Change the default temp table space to the new temporary table space just created TEMP2
ALTER DATABASE default temporary tablespace temp2;
4.--Delete original temporary table space
Drop Tablespace temp including contents and datafiles;

5.--temporary table space re-created
Create temporary tablespace TEMP tempfile ' D:\PowerDmsDB\DMSDB\TEMP01. DBF ' SIZE 2048M reuse autoextend on NEXT 640K MAXSIZE 5120M;

6.--resets the default temporary tablespace to the newly created temp table space
ALTER DATABASE default temporary tablespace temp;

7.--Delete Transit with temporary table space
Drop tablespace temp2 including contents and datafiles;

8.--the user table space for the rebuilt temporary tablespace
Alter user zxd temporary tablespace temp;

This temporary table space is growing too large to be changed.

The following is the SQL that the query uses in the sort sort area for time-consuming execution:
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

This statement can be used as a trace to view the analysis.