SET LINES 200 PAGES 200
COL OWNER FOR A10
WITH T1 AS
(SELECT TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD') SNAPDATE,
A.TS#,
A.OBJ#,
TRUNC(SUM(A.SPACE_ALLOCATED_DELTA) / 1024 / 1024) DELTA_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 * 10 -- LIMIT SIZE MB
GROUP BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'), A.TS#, A.OBJ#
ORDER BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'))
SELECT A.SNAPDATE,
A.TS#,
--A.OBJ#,
B.OBJECT_NAME,
B.OWNER,
SUM(A.DELTA_MB) DELTA_MB
FROM T1 A, DBA_HIST_SEG_STAT_OBJ B
WHERE A.OBJ# = B.OBJ#
AND A.TS# =
(SELECT TS# FROM V$TABLESPACE WHERE NAME = UPPER('&TABLESPACE_NAME'))
GROUP BY A.SNAPDATE,
A.TS#,
--A.OBJ#,
B.OBJECT_NAME,
B.OWNER
HAVING SUM (A.DELTA_MB) >= &SUMDELTASIZE
ORDER BY A.SNAPDATE;
标签:BEGIN,OBJ,排查,MB,TS,天表,DELTA,出近,SELECT
From: https://www.cnblogs.com/BY1314/p/18045365