oracle 11g在线收回临默认临时表空间


--数据库版本
SQL> select * from v$version;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


--查看数据库默认临时表空间
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
select *
 from database_properties
  where property_name like 'DEFAULT%TABLESPACE';


PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP2                          Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace


--检查临时表空间当前空闲情况（收回前查看临时表空间的空间情况）
set line 150
col TABLESPACE_NAME for a30
col TABLESPACE_SIZE for 99999999999999
col ALLOCATED_SPACE for 99999999999999
col FREE_SPACE for 99999999999999
SELECT TABLESPACE_NAME,
TABLESPACE_SIZE/1024/1024/1024 as "TABLESPACE_SIZE(G)",
ALLOCATED_SPACE/1024/1024/1024  as "ALLOCATED_SPACE (G)",
FREE_SPACE/1024/1024/1024 as "FREE_SPACE(G)"
 from DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME                TABLESPACE_SIZE(G) ALLOCATED_SPACE (G) FREE_SPACE(G)
------------------------------ ------------------ ------------------- -------------
TEMP2                                  31.9999847          31.9999847    31.9980469


--查看当前临时文件大小
col name for a50
SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile ORDER BY name;
SQL> SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;


     FILE# NAME                                               TEMPFILE(G)
---------- -------------------------------------------------- -----------
         1 /oradata/app/oracle/oradata/img/temp2.dbf           31.9999847


--oracle 11g 使用命令释放不使用的临时表空间（仅locally managed temporary tablespaces可用：Doc ID 452697.1）
--语法如下：
--ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];


--ALTER TABLESPACE tablespace SHRINK SPACE [KEEP size];
SQL> alter tablespace temp2 shrink space keep 30G;


Tablespace altered.


SQL> SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;


     FILE# NAME                                               TEMPFILE(G)
---------- -------------------------------------------------- -----------
         1 /oradata/app/oracle/oradata/img/temp2.dbf           30.0009766


--ALTER TEMPFILE tablespace SHRINK tempfile tempfile_name [KEEP size];
SQL> alter tablespace temp2 shrink tempfile '/oradata/app/oracle/oradata/img/temp2.dbf' keep 29G;


Tablespace altered.


SQL> SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;


     FILE# NAME                                               TEMPFILE(G)
---------- -------------------------------------------------- -----------
         1 /oradata/app/oracle/oradata/img/temp2.dbf           29.0009689


总结：
11g的在线回收默认临时表空间功能很强大（仅locally managed temporary tablespaces可用：Doc ID 452697.1）
11g以前回收默认临时表空间：(How to Shrink the Datafile of Temporary Tablespace (Doc ID 273276.1))
1.建立新的小的默认临时表空间temp1
2.alter tablespace命令使temp1成为默认临时表空间
3.删除老的默认临时表空间temp