表空间可用空间告警,如何定位什么对象异常数据增长导致？

通过AWR查找近一段时间的SEGMENT增长记录

col OWNER format a20
col OBJECT_NAME format a30
col SUBOBJECT_NAME format a30
with t1 as (select to_char(B.BEGIN_INTERVAL_TIME,'YYYYMMDD HH24') snapdate,a.ts#,A.DATAOBJ#, trunc(sum(A.SPACE_ALLOCATED_DELTA)/1024/1024) MB from DBA_HIST_SEG_STAT a,DBA_HIST_SNAPSHOT b where A.SNAP_ID=B.SNAP_ID and A.INSTANCE_NUMBER=B.INSTANCE_NUMBER
and B.SNAP_ID>(select min(snap_id) from DBA_HIST_SNAPSHOT where begin_interval_time>sysdate-&days)   and a.SPACE_ALLOCATED_DELTA>1024*1024*5 -- limit  size  
group by to_char(B.BEGIN_INTERVAL_TIME,'YYYYMMDD HH24') ,a.ts#,A.DATAOBJ# order by to_char(B.BEGIN_INTERVAL_TIME,'YYYYMMDD HH24')
)
select a.snapdate,a.ts#,A.DATAOBJ#,b.owner,b.object_name,B.SUBOBJECT_NAME,a.mb from t1 a,DBA_HIST_SEG_STAT_OBJ b where a.dataobj#=b.DATAOBJ#
and a.ts#=(select ts# from v$tablespace where NAME=upper('&tablespace_name'))
order by a.snapdate;

--示例
Enter value for days: 2
Enter value for tablespace_name: SYSAUX

SNAPDATE           TS#   DATAOBJ# OWNER                OBJECT_NAME                    SUBOBJECT_NAME                         MB
----------- ---------- ---------- -------------------- ------------------------------ ------------------------------ ----------
20191221 10          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191221 11          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191221 12          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714             6
20191221 12          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191221 13          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191221 13          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714             8
20191221 15          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191221 16          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191221 17          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191221 19          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            24
20191221 20          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191221 21          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191221 22          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191221 22          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714            17
20191221 23          1   13029586 SYS                  WRH$_EVENT_HISTOGRAM_PK        WRH$_EVENT__343193180_81714            10
20191221 23          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191222 00          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 01          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714             8
20191222 01          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            24
20191222 02          1   13029524 SYS                  WRH$_LATCH_MISSES_SUMMARY      WRH$_LATCH__343193180_81714             5
20191222 02          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191222 03          1   13029504 SYS                  WRH$_FILESTATXS_PK             WRH$_FILEST_343193180_81714             5
20191222 03          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 04          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            24
20191222 05          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 06          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714            16
20191222 06          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191222 07          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 08          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 09          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191222 10          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 11          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191222 12          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191222 13          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191222 14          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 16          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            24
20191222 17          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 18          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            24
20191222 19          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 21          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            24
20191222 21          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714             8
20191222 22          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191222 23          1   13029586 SYS                  WRH$_EVENT_HISTOGRAM_PK        WRH$_EVENT__343193180_81714             9
20191222 23          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714             8
20191222 23          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191223 00          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191223 00          1   13029584 SYS                  WRH$_EVENT_HISTOGRAM           WRH$_EVENT__343193180_81714             7
20191223 01          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191223 01          1   13029520 SYS                  WRH$_LATCH                     WRH$_LATCH_343193180_81714              6
20191223 01          1   13029522 SYS                  WRH$_LATCH_PK                  WRH$_LATCH_343193180_81714              5
20191223 01          1   13029502 SYS                  WRH$_FILESTATXS                WRH$_FILEST_343193180_81714             6
20191223 02          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191223 02          1   13029546 SYS                  WRH$_PARAMETER_PK              WRH$_PARAME_343193180_81714             5
20191223 03          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191223 04          1   13029542 SYS                  WRH$_SYSSTAT_PK                WRH$_SYSSTA_343193180_81714             7
20191223 04          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191223 04          1   13029548 SYS                  WRH$_SEG_STAT                  WRH$_SEG_ST_343193180_81714             5
20191223 04          1   13029562 SYS                  WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_343193180_81714            16
20191223 05          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191223 06          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16
20191223 07          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714             8
20191223 08          1   13029560 SYS                  WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_343193180_81714            16

62 rows selected.