DB version: 11.2.0.4
OS version: RHEL 6.8
For capacity planning, I wanted to know the monthly DB growth for few of my DBs.
Basically , I just need the sum of all the tablespaces' growth in a DB. In the below mentioned MOS note, I came across the following query based on v$datafile view.
But, I don't understand how V$DATAFILE can help in finding the DB level growth. V$DATAFILE view has 1 row for each datafile.
It has BYTES column which just stores the size of a datafile not the trend or growth.
Below is the output I got. It just returned 5 rows for one of my prod DBs which is 18 Terabytes in size (physical). I don't know what it means.
Query found in "Script to List the Details of Database Growth per Month (Doc ID 135294.1)"
SQL> SET FEEDBACK ON
SQL> select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024/1024 "Growth in GBs"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month') 2 3 4 5 ;
Month Growth in GBs
----------------------------------------- -------------
2016 December 124
2017 August 287.973633
2017 April 31.9970703
2017 December 20
2017 November 31.9970703
5 rows selected.
SQL> desc sys.v_$datafile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
<snipped>
SQL> select sum(bytes/power(1024,4)) sizeTB from dba_Data_Files;
SIZETB
----------
18.7303333
To find the monthly DB growth, I came across few queries using DBA_HIST_TBSPC_SPACE_USAGE . But, it all points to tablespace level information.
I just want to know how much the DB size has grown from January --> February ---> March ---> April ... December.