11g回收临时表空间的命令增强

OERR: ORA 1652临时表空间不能扩展

 

SQL>  alter tablespace TEMP2 shrink tempfile  '+DATA/PROD/TEMPFILE/TEMP9.dbf' keep 8192M;

Tablespace altered.

SQL>  ALTER DATABASE TEMPFILE '+DATA/PROD/TEMPFILE/TEMP9.dbf' DROP INCLUDING DATAFILES;
 ALTER DATABASE TEMPFILE '+DATA/PROD/TEMPFILE/TEMP9.dbf' DROP INCLUDING DATAFILES
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

--注意，没有Drop掉文件，临时文件显示OFFLINE了

 

9  +DATA/prod/tempfile/temp9.dbf 9 TEMP2   OFFLINE   

 

 SQL> ALTER DATABASE TEMPFILE '+DATA/PROD/TEMPFILE/TEMP9.dbf' online;

Database altered.

SQL> alter tablespace TEMP2 shrink space; 

Tablespace altered.

 

 

 

 

First of all we need to understand that significant temp tablespace growth is normal. If there has been any unusual activity such as large amounts of DML or DDL or creating or altering constraints, this can cause the temp tablespace to grow at a very fast rate.

One of the most frequently asked questions is "Why is my temp tablespace full?". This is common because the temp tablespace grows as needed but the space is not automatically released until the database is restarted.

The fact that temp tablespace is full does not mean it is unusable. Physical space is allocated, but there can be lot of free blocks inside. You can use this SQL statement to help you to check the utilization of the temp tablespace. Compare the FREE_BLOCKS vs TOTAL_BLOCKS.

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

How can we reclaim the space taken by the temporary tablespace?

i)
In 11g, there is a new SQL command that can be used to shrink LOCALLY MANAGED temporary tablespaces:
ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

ii)
If you are using LOBS, please see the following document:
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 NOTE: 802897.1

iii)
These notes apply from 8i to 11g:
How to Shrink the Datafile of Temporary Tablespace NOTE: 273276.1
Resizing (or Recreating) the Temporary Tablespace NOTE: 409183.1

The following links provide may also prove helpful:
Temporary Tablespace Enhancements in Oracle Database 11g Release 1 NOTE: 1064846.1
Queries To Monitor Temporary Tablespace Usage NOTE: 289894.1
OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s  NOTE: 19047.1
How To Shrink A Temporary Tablespace in 11G ? NOTE: 452697.1