system 表空间满了怎么处理

一、查看所有的表空间的信息

set pagesize 9999 line 9999
col TS# format 9999
col TS_Name format a20
col NEXT_MAX_EXTENT_M for 9999999999
col USED_SIZE_G for 9999999999
col MAX_SIZE_G for 99999999999
col USED_PER_MAX for 999.999
WITH WT1 AS
(SELECT TS.TABLESPACE_NAME,
         DF.ALL_BYTES,
         DECODE(DF.TYPE,
                'D',
                NVL(FS.FREESIZ, 0),
                'T',
                DF.ALL_BYTES - NVL(FS.FREESIZ, 0)) FREESIZ,
         DF.MAXSIZ
    FROM DBA_TABLESPACES TS,
         (SELECT 'D' TYPE,
                 TABLESPACE_NAME,
                 SUM(BYTES) ALL_BYTES,
                 SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
            FROM DBA_DATA_FILES D
           GROUP BY TABLESPACE_NAME
          UNION ALL
          SELECT 'T',
                 TABLESPACE_NAME,
                 SUM(BYTES) ALL_BYTES,
                 SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))
            FROM DBA_TEMP_FILES D
           GROUP BY TABLESPACE_NAME) DF,
         (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
            FROM DBA_FREE_SPACE
           GROUP BY TABLESPACE_NAME
          UNION ALL
          SELECT TABLESPACE_NAME, SUM(D.BLOCK_SIZE * A.BLOCKS) BYTES
            FROM GV$SORT_USAGE A, DBA_TABLESPACES D
           WHERE A.TABLESPACE = D.TABLESPACE_NAME
           GROUP BY TABLESPACE_NAME) FS
   WHERE TS.TABLESPACE_NAME = DF.TABLESPACE_NAME
     AND TS.TABLESPACE_NAME = FS.TABLESPACE_NAME(+))
SELECT (SELECT A.TS#
          FROM V$TABLESPACE A
         WHERE A.NAME = UPPER(T.TABLESPACE_NAME)) TS#,
       T.TABLESPACE_NAME TS_NAME,
       ROUND((T.ALL_BYTES - T.FREESIZ)/1024/1024/1024) USED_SIZE_G,
       ROUND(MAXSIZ/1024/1024/1024, 3) MAX_SIZE_G,
       ROUND(DECODE(MAXSIZ, 0, TO_NUMBER(NULL), (T.ALL_BYTES - FREESIZ)) * 100 / MAXSIZ, 3) USED_PER_MAX
  FROM WT1 T
UNION ALL
SELECT TO_NUMBER('') TS#,
       'ALL TS:' TS_NAME,
       ROUND(SUM(T.ALL_BYTES - T.FREESIZ)/1024/1024/1024) USED_SIZE_G,
       ROUND(SUM(MAXSIZ)/1024/1024/1024) MAX_SIZE,
       TO_NUMBER('') "USED,% of MAX Size"
  FROM WT1 T
order by  USED_PER_MAX ;

发现system表空间已经达到95%

二、查看system表空间中的对象

SELECT owner, object_type,status, COUNT(*) count# 
FROM all_objects 
where owner='SYSTEM'
GROUP BY owner, object_type, status
order by 2; 

select segment_name,sum(bytes)/1024/1024 FROM user_segments where tablespace_name='SYSTEM' group by segment_name order by 2;

发现AUD$审计日志过于多，占据空间比较大

三、处理方式

（一）
如果审计日志并不是很大可以直接清理

truncate table SYS.AUD$;

（二）
注意的是，如果AUD$表过大，那么直接TRUNCATE AUD$表，系统要立即释放大量的EXTENTS，会严重影响系统性能。
以下2个步骤逐步释放EXTENTS：
1、清空数据并且保留原来的EXTENTS：

TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;

注意：TRUNCATE TABLE 语法
TRUNCATE TABLE [schema_name.]table_name
  [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
  [ DROP STORAGE | REUSE STORAGE ] ;

REUSE STORAGE和DROP STORAGE都是TRUNCATE的一个参数，前者表示保持原来的存储不变，后者是TRUNCATE TABLE的默认参数。
本质区别是：运用REUSE STORGE一般与deallocate一起使用

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;

当然在执行的时候，可以根据实际情况调整每次回缩空间的大小。

方法三：迁移system表空间，下次讲解