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;

释放表存储空间