Oracle資料表空間過大時候的處理

Oracle資料表空間過大時候的處理

先查看Oracle所有資料表空間的資料使用方式

SELECT Upper(F.TABLESPACE_NAME)         "資料表空間名",       D.TOT_GROOTTE_MB                 "資料表空間大小(M)",       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')       || '%'                           "使用比",       F.TOTAL_BYTES                    "空閑空間(M)",       F.MAX_BYTES                      "最大塊(M)"FROM   (SELECT TABLESPACE_NAME,               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES        FROM   SYS.DBA_FREE_SPACE        GROUP  BY TABLESPACE_NAME) F,       (SELECT DD.TABLESPACE_NAME,               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB        FROM   SYS.DBA_DATA_FILES DD        GROUP  BY DD.TABLESPACE_NAME) DWHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER  BY 1

–查詢使用空間比較大的前15個表

select a.*   from(    select owner,segment_name,SEGMENT_TYPE, round(bytes/1024/1024/1024,2) all_size      from dba_segments      where SEGMENT_TYPE='TABLE'     order by  all_size desc   ) a where rownum <15;

—查資料表空間對應資料檔案大小

select t1.name, t2.name,Round(t2.BYTES / ( 1024 * 1024 ), 2) "大小(M)"  from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts# order by 1,3

**

開始處理

**

—-清理資源回收筒

purge recyclebin;Purge recyclebin;--清空目前使用者的資源回收筒Purge dba_recyclebin;--清空所有使用者的資源回收筒

—-從truncate sys.aud$想到的刪除大表的方法

—查看Database Audit情況（NONE為沒有開啟審計）

SQL> show parameter  audit_trail NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------audit_trail                          string      DB

—-最後直接處理

(1) truncate table aud$ ; 釋放SYSTEM資料表空間的大小

SQL> alter table xx_xlog enable row movement;Table alteredSQL> alter table xx_xlog shrink space compact;Table alteredSQL> alter table xx_xlog shrink space;Table altered

釋放表所佔用空間的大小

確認以下表清單為不用保留資料表

truncate table JXNCPC.XX_XLOG reuse storage; truncate table JXPC141224.XX_XLOG  reuse storage;truncate table TEST001.XX_XLOG  reuse storage;truncate table NCJXPC2013.XX_XLOG  reuse storage;truncate table NCJXPC2014.XX_XLOG  reuse storage;truncate table NCJXPCTEMP.XX_XLOG  reuse storage;truncate table NCJXPCERP.XX_XLOG  reuse storage;alter table JXNCPC.XX_XLOG enable row movement;alter table JXPC141224.XX_XLOG enable row movement;alter table TEST001.XX_XLOG enable row movement;alter table NCJXPC2013.XX_XLOG  enable row movement;alter table NCJXPC2014.XX_XLOG  enable row movement;alter table NCJXPCTEMP.XX_XLOG  enable row movement;alter table NCJXPCERP.XX_XLOG enable row movement;alter table JXNCPC.XX_XLOG shrink space compact; alter table JXPC141224.XX_XLOG  shrink space compact;alter table TEST001.XX_XLOG  shrink space compact;alter table NCJXPC2013.XX_XLOG  shrink space compact;alter table NCJXPC2014.XX_XLOG  shrink space compact;alter table NCJXPCTEMP.XX_XLOG  shrink space compact;alter table NCJXPCERP.XX_XLOG  shrink space compact;alter table JXNCPC.XX_XLOG shrink space; alter table JXPC141224.XX_XLOG  shrink space;alter table TEST001.XX_XLOG  shrink space;alter table NCJXPC2013.XX_XLOG  shrink space;alter table NCJXPC2014.XX_XLOG  shrink space;alter table NCJXPCTEMP.XX_XLOG  shrink space;alter table NCJXPCERP.XX_XLOG  shrink space;----truncate table JXNCPC.SM_OPERATELOG;truncate table JXPC141224.SM_OPERATELOG; truncate table TEST001.SM_OPERATELOG;    truncate table NCJXPCTEMP.SM_OPERATELOG;     truncate table NCJXPCERP.SM_OPERATELOG;  truncate table NCJXPC2013.SM_OPERATELOG;     truncate table NCJXPC2014.SM_OPERATELOG;    

釋放表格儲存體空間