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
总结：
Oracle 11g的在线回收默认临时表空间功能很强大（仅locally managed temporary tablespaces可用：Doc ID 452697.1）
Oracle 11g以前回收默认临时表空间：(How to Shrink the Datafile of Temporary Tablespace (Doc ID 273276.1))
1.建立新的小的默认临时表空间temp1
2.alter tablespace命令使temp1成为默认临时表空间
3.删除老的默认临时表空间temp
相关阅读：
Oracle 临时表空间的管理与受损恢复
Oracle 临时表空间过大问题解决
解决ORA-14450:试图访问已经在使用的事务处理临时表
Oracle创建基于事务和基于会话的临时表及临时表建索引的实验
Oracle 临时表之临时表空间组（TTG）