1 SET LINES 200 PAGES 200 2 COL OWNER FOR A10 3 WITH T1 AS 4 (SELECT TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD') SNAPDATE, 5 A.TS#, 6 A.OBJ#, 7 TRUNC(SUM(A.SPACE_ALLOCATED_DELTA) / 1024 / 1024) DELTA_MB 8 FROM DBA_HIST_SEG_STAT A, DBA_HIST_SNAPSHOT B 9 WHERE A.SNAP_ID = B.SNAP_ID 10 AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER 11 AND B.SNAP_ID > 12 (SELECT MIN(SNAP_ID) 13 FROM DBA_HIST_SNAPSHOT 14 WHERE BEGIN_INTERVAL_TIME > SYSDATE - &DAYS) 15 --AND A.SPACE_ALLOCATED_DELTA > 1024 * 1024 * 10 -- LIMIT SIZE MB 16 GROUP BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'), A.TS#, A.OBJ# 17 ORDER BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD')) 18 SELECT A.SNAPDATE, 19 A.TS#, 20 --A.OBJ#, 21 B.OBJECT_NAME, 22 B.OWNER, 23 SUM(A.DELTA_MB) DELTA_MB 24 FROM T1 A, DBA_HIST_SEG_STAT_OBJ B 25 WHERE A.OBJ# = B.OBJ# 26 AND A.TS# = 27 (SELECT TS# FROM V$TABLESPACE WHERE NAME = UPPER('&TABLESPACE_NAME')) 28 GROUP BY A.SNAPDATE, 29 A.TS#, 30 --A.OBJ#, 31 B.OBJECT_NAME, 32 B.OWNER 33 HAVING SUM (A.DELTA_MB) >= &SUMDELTASIZE 34 ORDER BY A.SNAPDATE;