先查看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  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;    

释放表存储空间

：

您可能感兴趣的文章: