 |
 |
*************************************************************************
* 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