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

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

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）