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) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER  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$想到的刪除大表的方法

—查看數據庫審計情況（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 altered


SQL> alter table xx_xlog shrink space compact;
Table altered


SQL> 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;    

釋放表存儲空間