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）