Check Database growth

To calculate the Data-file Growth on a yearly basis:
SQL> select to_char(CREATION_TIME,'RRRR') year,to_char(CREATION_TIME,'MM') month,round(sum(bytes)/1024/1024/1024) gb from v$datafile group by to_char(CREATION_TIME,'RRRR'),to_char(CREATION_TIME,'MM') order by 1,2;

YEAR MO         GB
---- -- ----------
2000 05          5
2000 12          1
2002 03          0
2002 05          1
2002 10          3
2003 01          2
2003 06          1
2003 11         19
2004 02          0
2004 03          0
2005 12          1

YEAR MO         GB
---- -- ----------
2007 01          5
2014 01         82

13 rows selected.
Take the sum of this two values which will be your total database size. Record this value daily/weekly/monthly basis and compare the difference.

SQL> select round((sum(bytes)/1048576/1024),2)
from V$tempfile;  2

ROUND((SUM(BYTES)/1048576/1024),2)
----------------------------------
                             46.07

SQL> select round((sum(bytes)/1048576/1024),2) from V$datafile;

ROUND((SUM(BYTES)/1048576/1024),2)
----------------------------------
                            119.66


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’);



Weekly growth of database
set feedback off
set pages 80
set linesize 150
spool /tmp/weekly_growth.txt
ttitle “Total Disk Used”
select sum(space_used_delta) / 1024 / 1024 “Space used (M)”, sum(c.bytes) / 1024 / 1024 “Total Schema Size (M)”,
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
see code depot for full script
where end_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_name’
and space_used_delta > 0;
title “Total Disk Used by Object Type”
select c.segment_type, sum(space_used_delta) / 1024 / 1024 “Space used (M)”, sum(c.bytes) / 1024 / 1024 “Total Space (M)”,
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
see code depot for full script
where end_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 space_used_delta > 0
and c.owner = ‘&schema_name’
group by rollup(segment_type);
spool off

Through  OEM


Here are the steps to know Database growth pattern for last one month/year using OEM


1) Login to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for last one Month.
4) Also we can get one year Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Table-space Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and click on Oracle Database Table-space Monthly Space Usage link.
select sum(a.bytes+b.bytes)/1024/1024 "Total Size(MB)",sum(c.bytes)/1024/1024 "Used Space(MB)",sum(d.bytes)/1024/1024 "Free Space(MB)" from dba_data_files a,dba_temp_files b,dba_segments c,dba_free_space d;
Total Size(MB)                   Used Space(MB)              Free Space(MB)
--------------                   --------------              --------------
    1060137494                     528906                      28491086.3
 
 
The Total Size includes the size of Temporary tablespaces also, which is excluded in the rest two figures.

Scripts: Database,Table,Tablespace Growth Report using AWR

Hello
The following code snippets are collected from various sources and updating here, (not sure even about copy write of those, if any found so, I am happy to remove from here), until then we will try to use them.
Some background,  Oracle AWR by default collects the information about the segment growth periodically. This information can be queried using views DBA_HIST_SEG_STAT.
Script #1 : script to display table size changes between two periods. 
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’);

Script #2:-  Database Growth in 1 hour intervals
Declare 
    v_BaselineSize    number(20); 
    v_CurrentSize    number(20); 
    v_TotalGrowth    number(20); 
    v_Space        number(20); 
    cursor usageHist is 
            select a.snap_id, 
            SNAP_TIME, 
            sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum 
        from 
            (select SNAP_ID, 
                sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA 
            from DBA_HIST_SEG_STAT 
            group by SNAP_ID 
            having sum(SPACE_ALLOCATED_TOTAL) <> 0 
            order by 1 ) a, 
            (select distinct SNAP_ID, 
                to_char(END_INTERVAL_TIME,’DD-Mon-YYYY HH24:Mi’) SNAP_TIME 
            from DBA_HIST_SNAPSHOT) b 
        where a.snap_id=b.snap_id; 
Begin 
    select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT; 
    select sum(bytes) into v_CurrentSize from dba_segments; 
    v_BaselineSize := v_CurrentSize – v_TotalGrowth ;
    dbms_output.put_line(‘SNAP_TIME           Database Size(MB)’);
    for row in usageHist loop 
            v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024); 
        dbms_output.put_line(row.SNAP_TIME || ‘           ‘ || to_char(v_Space) ); 
    end loop; 
end;

Script #3: Top 50 Query which can be used to query segments with highest growth. This will also report the present size of the segment which is very useful in identifying the growth percentage.
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 
/

Script #4: List object growth over last N days, sorted by growth desc 
set feedback on 
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 =’SIEBEL’ 
group by c.TABLESPACE_NAME,c.segment_name,b.object_type) 
order by 3 asc;

Script #5: generates tablespace usage trend/growth.
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF; 
set linesize 125 
set numwidth 20 
set pagesize 50 
COL NAME FOR A30 
col SNAP_ID for 9999999 
set serveroutput off; 
SPOOL TBS_TREND.xls; 
set verify off; 
set echo off;
SELECT 
distinct DHSS.SNAP_ID,VTS.NAME, 
TO_CHAR(DHSS.END_INTERVAL_TIME, ‘DD-MM HH:MI’) AS SNAP_Time, 
ROUND((DHTS.TABLESPACE_USEDSIZE*8192)/1024/1024)/&&max_instance_num AS USED_MB, 
ROUND((DHTS.TABLESPACE_SIZE*8192)/1024/1024)/&&max_instance_num AS SIZE_MB 
FROM DBA_HIST_TBSPC_SPACE_USAGE DHTS,V$TABLESPACE VTS,DBA_HIST_SNAPSHOT DHSS 
WHERE VTS.TS#=DHTS.TABLESPACE_ID 
AND DHTS.SNAP_ID=DHSS.SNAP_ID 
AND DHSS.INSTANCE_NUMBER=1 
AND TABLESPACE_ID=&id 
ORDER BY 1; 
SPOOL OFF;



No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...