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


總結：
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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-1068856/，如需轉載，請註明出處，否則將追究法律責任。