查看Oracle表空间增长过快原因

查看表空间爆长原因

一、查看表空间占用大小前10的数据库对象

---查看某个表空间占用情况:
SET LINESIZE 720
SET PAGESIZE 36
COL OWNER FOR A16
COL SEGMENT_TYPE FOR A20
COL SEGMENT_NAME FOR A38
COL TABLESPACE_NAME FOR A20

SELECT *
  FROM (SELECT OWNER,
               SEGMENT_TYPE,
               SEGMENT_NAME,
               ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 2) TOTAL_SIZE_GB,
               TABLESPACE_NAME
          FROM DBA_SEGMENTS
         WHERE TABLESPACE_NAME = UPPER('&TABLESPACE_NAME')
         GROUP BY OWNER, SEGMENT_TYPE, SEGMENT_NAME, TABLESPACE_NAME
         ORDER BY TOTAL_SIZE_GB DESC)
 WHERE ROWNUM <= 10;
 
 
---查看system或sysaux表空间占用情况:
 set lines 120
col owner for a30
col segment_name for a30
col owner for a30

SELECT *
  FROM (SELECT BYTES, segment_name, segment_type, owner
          FROM dba_segments
         WHERE tablespace_name = 'SYSTEM'
         ORDER BY BYTES DESC)
 WHERE ROWNUM < 10;

二、查看表空间最近7天增长情况

---10g/11g
set linesize 640
set pagesize 36
col snap_id for 999999
col con_id for 99999
col pdbname for a16
col ts_name for a20
col rtime for a18
col ts_size_mb for 999999.9
col ts_used_mb for 999999.9
col pct_used for 99.99
select u.snap_id,
       to_char(s.begin_interval_time, 'yyyy-mm-dd hh24') begin_time,
       to_char(s.end_interval_time, 'yyyy-mm-dd hh24') end_time,
       t.name,
       round(u.tablespace_size * ts.block_size / 1024 / 1024, 2) ts_size_mb,
       round(u.tablespace_usedsize * ts.block_size / 1024 / 1024, 2) ts_used_mb,
       round((u.tablespace_size - u.tablespace_usedsize) * ts.block_size / 1024 / 1024, 2) ts_free_mb,
       round(u.tablespace_usedsize / u.tablespace_size * 100, 2) pct_used
  from dba_hist_tbspc_space_usage u,
       v$tablespace               t,
       dba_hist_snapshot          s,
       dba_tablespaces            ts
 where u.tablespace_id = t.ts#
   and u.snap_id = s.snap_id
   and t.name = ts.tablespace_name
   and s.instance_number = 1
   and t.name = upper('&tablespace_name')
   and s.end_interval_time > sysdate - 7   
     order by snap_id desc;
     
   
 ---多租户
 set linesize 640
set pagesize 36
col snap_id for 999999
col con_id for 99999
col pdbname for a16
col ts_name for a20
col rtime for a18
col ts_size_mb for 999999.99
col ts_used_mb for 999999.99
col pct_used for 99.99
select a.snap_id,
       a.con_id,
       e.name pdbname,
       c.tablespace_name ts_name,
       to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
       round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
       round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
       round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
             2) ts_free_mb,
       round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
  from cdb_hist_tbspc_space_usage a, 
       (select tablespace_id,
               nb.con_id,
               substr(rtime, 1, 10) rtime,
               max(snap_id) snap_id
          from dba_hist_tbspc_space_usage nb
         group by tablespace_id, nb.con_id,substr(rtime, 1, 10)) b,
         cdb_tablespaces c,
         v$tablespace d,
         V$CONTAINERS e
 where a.snap_id = b.snap_id
   and a.tablespace_id = b.tablespace_id
   and a.con_id=b.con_id
   and a.con_id=c.con_id
   and a.con_id=d.con_id
   and a.con_id=e.con_id
   and a.tablespace_id=d.TS#
   and d.NAME=c.tablespace_name
   and c.tablespace_name=upper('&tablespace_name')
   and  to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
   order by a.con_id,a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;

三、查看sysaux表空间对象占用情况

SET LINESIZE 720;
SET PAGESIZE 36;
COL "Item" FOR A16;
COL SCHEMA_NAME FOR A30;
SELECT OCCUPANT_NAME "Item",
       SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
       SCHEMA_NAME ,
       MOVE_PROCEDURE 
FROM V$SYSAUX_OCCUPANTS
WHERE SPACE_USAGE_KBYTES > 1048576
    ORDER BY "Space Used (GB)" DESC;
    
    
根据OCCUPANT_NAME列的数据来判断：
如果该列为SM/AWR， 它是Server Manageability - Automatic Workload Repository的缩写，那么表示AWR信息占用空间的大小
如果该列为SM/OPTSTAT，它是Server Manageability - Optimizer Statistics History的缩写，那么表示统计信息占空间的大小
如果该列为AUDIT_TABLES，表示审计信息占用的空间大小。
如果该列为LOGMNR，表示logminer产生的数据占用了表空间
上面是从高一点的维度分析，其实我们还可以继续下钻分析SYSAUX表空间下面哪些对象/表占用了大量的空间，一般只需关注最大的几个表，例如AUD$,WRH$_ACTIVE_SESSION_HISTORY,WRR$_REPLAY_CALL_INFO

四、自动扩展表空间使用率

SET PAGESIZE 9999 LINESIZE 180;
TTI 'Tablespace Usage Status'
COL TABLESPACE_NAME FOR A20;
COL TBS_MAX_SIZE FOR 99999.99;
COL TABLESPACE_SIZE FOR 99999.99;
COL TBS_AVABLE_SIZE FOR 999999.99;
COL "USED_RATE(%)" FOR A16;
COL "ACT_USED_RATE(%)" FOR A16;
COL "FREE_SIZE(GB)" FOR 99999999.99;

SELECT  UPPER(F.TABLESPACE_NAME)                           AS "TABLESPACE_NAME",
        ROUND(D.MAX_BYTES,2)                               AS "TBS_MAX_SIZE" ,
        ROUND(D.AVAILB_BYTES ,2)                           AS "TBS_ACT_SIZE",
        ROUND((D.AVAILB_BYTES - F.USED_BYTES),2)           AS "TBS_USED_SIZE",
        ROUND(F.USED_BYTES, 2)                             AS "FREE_SIZE(GB)",
        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
                     2),
               '999.99')                                   AS "USED_RATE(%)",
        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES)/D.MAX_BYTES*100,
                     2),
               '999.99')                                   AS "ACT_USED_RATE(%)",
        ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2)  AS "TBS_AVABLE_SIZE"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6)  AVAILB_BYTES,
               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6)   MAX_BYTES
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY "ACT_USED_RATE(%)" DESC;

五、查看哪种审计占用大量审计空间

select action_name,count(*) from dba_audit_trail group by action_name;

---查看用户登录审计次数最多
select os_username,username,userhost,count(*) from dba_audit_trail group by os_username,username,userhost;
---查看登录用户的IP地址
select os_username,username,userhost,action_name,timestamp,returncode,comment_text from dba_audit_trail where os_username='' and username='';
select os_username,username,userhost,action_name,timestamp,returncode,comment_text from dba_audit_trail where os_username='root' and username='FDS'
and userhost in ('bdd0b430ac18','594749c48b5a') and timestamp > to_date('2025-07-25 00:00:00','yyyy-mm-dd hh24:mi:ss') ;


一般是LOGON和LOGOFF类型的审计数据最多。可以截断占用空间最大的AUD$表（需要确认审计信息是否需要保留）
---查看最近一年的审计记录
select * from aud$ where substr(sysdate-NTIMESTAMP#,2,9)<360;
--截断AUD$
truncate table sys.aud$ reuse storage;
alter table sys.aud$ deallocate unused keep 2000m;
alter table sys.aud$ deallocate unused keep 1000m;
alter table sys.aud$ deallocate unused keep 500m;

六、查看awr报表占用的空间情况

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql

---查看awr报表保留时间
SELECT * FROM DBA_HIST_WR_CONTROL;

---通过如下的SQL语句可以设置AWR信息的保留时间为N天（例如：72460），每隔1小时收集一次AWR信息：
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>7*24*60);

在以上设置完成后，可以删除不需要的AWR快照信息，从而释放SYSAUX表空间，相关SQL语句如下所示：
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;

BEGIN
     DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
      LOW_SNAP_ID => xxx,
      HIGH_SNAP_ID => xxx,
      DBID => xxxx);
END;

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通过DELETE操作来完全清理工作的。所以，执行完成后，并不会真正的释放空间归还给SYSAUX表空间。此时，应该对相关的大表执行降低高水位线操作来释放空间。

还有一些非常规操作，这些最好不要在生产环境操作，可用于测试环境或紧急情况下使用：
set linesize 680
col sql_cmd for a90;
select distinct 'truncate  table  '||segment_name||';' as sql_cmd
      ,s.bytes/1024/1024 as table_size
from dba_segments s
where s.segment_name like 'WRH$%'
  and segment_type in ('TABLE PARTITION', 'TABLE')
  and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;

七、查看统计信息保留时间

如果统计信息占用空间过大，那么可以修改统计信息的保留时间。统计信息默认保留31天，过期的统计信息会自动被删除。
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;  --查询统计信息的保留时间

EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);  --设置统计信息的保留时间

如果发现统计信息占用了SYSAUX表空间的大量空间，则可以考虑使用DBMS_STATS.PURGE_STATS过程实施清理或调整保留周期
exec dbms_stats.purge_stats(sysdate-&days);

--设置历史统计信息保留天数
exec dbms_stats.alter_stats_history_retention(&day);