************************************************************************* * Description: SQL of querying how much data growth for individual table * Date: 04:46 PM EST, 09/08/2018 ************************************************************************* <1> SQL of checking table size growth between 2 AWR snapshot id: | |__ SQL> select min(BEGIN_INTERVAL_TIME), max(END_INTERVAL_TIME) from DBA_HIST_SNAPSHOT; | | | MIN(BEGIN_INTERVAL_TIME) MAX(END_INTERVAL_TIME) | -------------------------------- --------------------------------- | 30-AUG-18 03.00.41.817 AM 07-SEP-18 09.00.57.798 PM | | | |__ SQL> SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE , t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB", (SELECT sum(bytes)/(1024*1024) FROM dba_segments WHERE segment_name=o.object_name) "Total Size(MB)" FROM DBA_OBJECTS o, ( SELECT TS#,OBJ#, SUM(SPACE_USED_DELTA) growth FROM DBA_HIST_SEG_STAT GROUP BY TS#,OBJ# HAVING SUM(SPACE_USED_DELTA) > 0 ORDER BY 2 DESC ) s, v$tablespace t WHERE s.OBJ# = o.OBJECT_ID AND s.TS#=t.TS# AND rownum < 51 ORDER BY 6 DESC / OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE Tablespace Name Growth in MB Total Size(MB) ------------------ ----------------------------- ----------------- ------------------- ----------------------- ------------ -------------- OCT_JAPANRPT FEED_RAW TABLE SGS_JAPANRPT_DATA 1801.53109 121538 OCT_CANADARPT FEED_RAW_HIST TABLE SGS_ASIARPT_DATA 828.662098 281506 OCT_RPT TAG_LOC_LOG_HIST TABLE KRA_RPT_DATA 322.063115 67652 OCT_JAPANRPT JOURNEY_LOC_LOG TABLE SGS_JAPANRPT_DATA 47.9660473 7743 OCT_JAPANRPT EVENT_DATA TABLE SGS_JAPANRPT_DATA 23.6402874 7590 OCT_CANADARPT EVENT_DATA_HIST TABLE SGS_ASIARPT_DATA 21.4834785 34605 OCT_CANADARPT I_SNAP$_FEED_RAW_HIST INDEX SGS_AFRICARPT_IDX 18.6720486 34217 OCT_JAPANRPT I_SNAP$_FEED_RAW INDEX SGS_JPNRPT_IDX 16.5227013 5881 OCT_CANADARPT I_SNAP$_EVENT_DATA_HIST INDEX SGS_AFRICARPT_IDX 15.6039715 19059 <2> Query the previous size by date on individual table: | |__ SQL> column "Percent of Total Disk Usage" justify right format 999.99 column "Space Used (MB)" justify right format 9,999,999.99 column "Total Object Size (MB)" justify right format 9,999,999.99 set linesize 150 set pages 80 set feedback off select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta)/1024/1024 "Space used (MB)", avg(c.bytes)/1024/1024 "Total Object Size (MB)", round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage" from dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c where begin_interval_time > trunc(sysdate) - &days_back and sn.snap_id = a.snap_id and b.object_id = a.obj# and b.owner = c.owner and b.object_name = c.segment_name and c.segment_name = '&segment_name' group by to_char(end_interval_time, 'MM/DD/YY')) order by to_date(mydate, 'MM/DD/YY'); MYDATE Space used (MB) Total Object Size (MB) Percent of Total Disk Usage -------- --------------- ---------------------- --------------------------- 09/05/18 .83 1.04 79.70 <3> Query DELTA size of all segments under sepcific schema by date: | |__ SQL> set feedback on col "object name" format a40 select * from (select c.TABLESPACE_NAME,c.segment_name "Object Name",b.object_type, sum(space_used_delta) / 1024 / 1024 "Growth (MB)" from dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c where begin_interval_time > trunc(sysdate) - &days_back and sn.snap_id = a.snap_id and b.object_id = a.obj# and b.owner = c.owner and b.object_name = c.segment_name and c.owner = '&schema' group by c.TABLESPACE_NAME,c.segment_name,b.object_type) order by 3 asc; TABLESPACE_NAME Object Name OBJECT_TYPE Growth (MB) ------------------------------ ------------------------------ ----------------------- ----------- NUST_DATA CORH_CORT INDEX .007711411 NUST_DATA PART_CORT INDEX .01448822 NUST_DATA PART_PARP INDEX 0 NUST_DATA PK_APPLICANT INDEX .02126503 NUST_DATA KLP_COUNTERS TABLE .0078125 NUST_DATA BIRTH_DATA TABLE 0 <4> Reference: | |__ o. http://db.geeksinsight.com/2012/10/15/scripts-databasetabletablespace-growth-report-using-awr/
Your Comments