Oracle Tablespace and Object Growth

 

>>>>>>>>>>  Tablespace Growth >>>>>>>>>>  

col ts_mb for 999,999,999,999.90

 col max_mb for 999,999,999,999.90

 col used_mb for 999,999,999,999.90

 col last_mb for 999,999,999,999.90

 col incr for 999,999.90

 

  select * from (

  select v.name

 , v.ts#

 , s.instance_number

 , h.tablespace_size

  * p.value/1024/1024 ts_mb

 , h.tablespace_maxsize

  * p.value/1024/1024 max_mb

 , h.tablespace_usedsize

  * p.value/1024/1024 used_mb

 , to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time

 , lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)

  over (partition by v.ts# order by h.snap_id) last_mb

 , (h.tablespace_usedsize * p.value/1024/1024)

  - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)

  over (partition by v.ts# order by h.snap_id) incr

  from dba_hist_tbspc_space_usage h

 , dba_hist_snapshot s

 , v$tablespace v

 , dba_tablespaces t

 , v$parameter p

  where h.tablespace_id = v.ts#

  and v.name = t.tablespace_name

  and t.contents not in ('UNDO', 'TEMPORARY')

  and p.name = 'db_block_size'

  and h.snap_id = s.snap_id

  /* For a specific time */

  and s.begin_interval_time > sysdate - 1/12

  /* For a specific tablespace */

  and v.ts# = 1

 order by v.name, h.snap_id asc)

  where incr > 0;

 

SET COLSEP '|'

SET LINESIZE 190

select TABLESPACE_ID,NAME,TABLESPACE_SIZE*8192/1024/1024 "TABLESPACE_SIZE",TABLESPACE_USEDSIZE*8192/1024/1024 "TABLESPACE_USEDSIZE",BEGIN_INTERVAL_TIME

from DBA_HIST_TBSPC_SPACE_USAGE,V$TABLESPACE,DBA_HIST_SNAPSHOT

where DBA_HIST_TBSPC_SPACE_USAGE.snap_id in (

select snap_id from dba_hist_snapshot

where DECODE(SUBSTR(TO_CHAR(BEGIN_INTERVAL_TIME,'DD-MM-YY HH24:MI:SS'),10,2),'10',1,0)= 1)

AND DBA_HIST_TBSPC_SPACE_USAGE.TABLESPACE_ID=V$TABLESPACE.TS#

AND NAME LIKE '&TABLESPACE_NAME'

AND DBA_HIST_SNAPSHOT.SNAP_ID=DBA_HIST_TBSPC_SPACE_USAGE.SNAP_ID

ORDER BY DBA_HIST_SNAPSHOT.SNAP_ID;

select 

   dhs.begin_interval_time,

   dt.tablespace_name,

   trunc(dhtsu.tablespace_size*dt.block_size/1024/1024/1024)  gb,

   trunc(dhtsu.tablespace_usedsize*dt.block_size/1024/1024/1024) gb_used

from

   dba_hist_tbspc_space_usage dhtsu,

   v$tablespace vts,

   dba_tablespaces dt,

   dba_hist_snapshot dhs

where dhtsu.snap_id = dhs.snap_id

and   dhtsu.tablespace_id = vts.ts#

and   vts.name          = dt.tablespace_name 

order by 2,1;

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb 

from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts#

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

>>>>>>>>>>  Object Growth >>>>>>>>>>

column owner format a16

column object_name format a36

column start_day format a11

column block_increase_bytes 9999999999

select   obj.owner, obj.object_name,

         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,

         sum(a.SPACE_USED_DELTA) block_increase_bytes

from     dba_hist_seg_stat a,

         dba_hist_snapshot sn,

         dba_objects obj

where    sn.snap_id = a.snap_id

and      a.instance_number = sn.instance_number

and      obj.object_id = a.obj#

and      obj.owner not in ('SYS','SYSTEM')

and      end_interval_time between to_timestamp('01-JAN-2012','DD-MON-RRRR')

         and to_timestamp('02-FEB-2012','DD-MON-RRRR')

group by obj.owner, obj.object_name,

         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')

order by obj.owner, obj.object_name

/

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')

/

How To Get Table Growth History Information? (Doc ID 1395195.1)