How to Check Database Growth in Oracle

In the actual world, “database growth prediction” is a crucial component of DBA life since it allows businesses to not only forecast the present situation at the moment of physical systems but also to budget for prospective hardware(s) and storage requirements.

Oracle provides a number of options for generating data growth reports, which will be discussed here. Leveraging Oracle’s views, we can monitor growth for an object (i.e., tables, indexes), tablespaces, schemas, and even whole databases.

Oracle provides a few key views that assist us in tracking our progress

These include the following:

DBA_SEGMENTS
DBA_TABLESPACES
DBA_HIST_SEG_STAT
DBA_HIST_TABLESPACE_STAT
DBA_HIST_TBSPC_SPACE_USAGE
DBA_HIST_SNAPSHOT
DBMS_SPACE

The “accumulated to date” information is provided by any view that does not contain HIST in its name. This implies you may use DBA_SEGMENTS & DBA_TABLESPACES to determine the current size of an object. This view keeps track of how much data was used in BYTES. To illustrate, when we build a table (with the segment allocation immediate option selected), Oracle creates spaces for this object that are not used until a row is added. In basic words, the utilized space is the “high water mark,” or the amount of space that Oracle has used “up to.”

This view, however, does not provide you with a comparable data growth chart that allows you to monitor historical data consumption and anticipate future storage requirements. To create a report like this, go to historical views.

First, look into DBA_HIST_SEG_STAT. This view records a huge amount of information well about segments, including logical reads, physical reads, buffer busy waits, row lock waits, table scans, and physical writes, as well as space allocation and consumption. When this historical perspective is paired with DBA_HIST_SNAPSHOT, the needed information is obtained in a comparison manner. This view may be used to discover the database growth for any item.

To compute the Data-file Growth on an annual basis, use the following command

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;

How to Check Database Growth in Oracle

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

Following are the procedures to determine the database growth trend over the preceding period or even a year using OEM.

Step #1: Open OEM and go to the Reports tab.

Step #2: Select Oracle Database Space Usage from the Reports–>Storage–>Oracle Database Space Usage path.

Step #3: Choose the destination database, and the Oracle Database space utilization for the last month is displayed.

Step #4: We can also receive a year’s worth of database growth by using the Set Time Period Option.

Step #5: We can also obtain Oracle Database Table-space Month-to-month Space Utilization by going to Reports–>Storage–>Oracle Database Space Usage and selecting Oracle Database Table-space Monthly Space Usage from the drop-down menu.

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;

The size of Temporary tablespaces has been included in the Total Size, which is not included in the other two values.

You can visit our website or explore our Oracle category to get answers to your most pressing oracle questions.