Database 11g

Thursday, October 29, 2015

Query to Check Database Growth in Oracle


Hi Guy's,


> Monitoring daily database growth is helpful for a DBA to Track the data-files growth and to  understand how much your database is growing, which will be easy for you to plan the storage.

> You need to run anyone of these script on daily basis to know the data growth per day.

> I showed in Bytes,MB and GB, execute it as per your requirement.



SQL>  Select a.tablespace_name, a.file_name, a.bytes allocated_bytes,b.free_bytes FROM dba_data_files  a,(SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id order by a.tablespace_name;


TABLESPACE               FILE_NAME                                  ALLOCATED_BYTES        FREE_BYTES
---------- -----------------------------------------------------------------------------------------------------------------------
SYSAUX        +DATA/ieclive/datafile/sysaux.264.860628911           5368709120            3801284608
SYSAUX        +DATA/ieclive/datafile/sysaux.278.909316069           5368709120            5113905152
SYSTEM        +DATA/ieclive/datafile/system.258.860628911           5368709120            2097152
SYSTEM        +DATA/ieclive/datafile/system.292.953295613           5368709120            5166333952
SYSTEM        +DATA/ieclive/datafile/system.288.909316707           5368709120            2745106432
UNDOTBS1    +DATA/ieclive/datafile/undotbs1.262.860628913       5368709120            4472242176
UNDOTBS1    +DATA/ieclive/datafile/undotbs1.295.923579343       5368709120            4604297216
UNDOTBS1    +DATA/ieclive/datafile/undotbs1.298.923684595       5368709120            5336203264
UNDOTBS2    +DATA/ieclive/datafile/undotbs2.297.923684447       5368709120            5276434432
UNDOTBS2    +DATA/ieclive/datafile/undotbs2.296.923579441       5368709120            5209325568
UNDOTBS2    +DATA/ieclive/datafile/undotbs2.269.860629051       5368709120            4956094464
USERS          +DATA/ieclive/datafile/users.294.923569969               1.0737E+10             3300917248
USERS          +DATA/ieclive/datafile/users.256.860630027               1.2885E+10             3210149888
USERS          +DATA/ieclive/datafile/users.274.860630073               1.3959E+10             4204593152
USERS          +DATA/ieclive/datafile/users.282.909316279               1.5032E+10             8168407040
USERS          +DATA/ieclive/datafile/users.259.860628913               1.5032E+10             4981063680
         




SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,(select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE        SIZEMB           FREEMB
--------------------------------------------------------------------
SYSAUX                   10240               8501
UNDOTBS1              15360              13742.06
USERS                      64512              22759.56
SYSTEM                   15360              7546.94
UNDOTBS2              15360              14765.5



SQL>  select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from (select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space from dba_free_space group by tablespace_name UNION select tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from dba_temp_free_space) a, (select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name UNION select tablespace_name,      sum(bytes)/1024/1024/1024 tbs_size from dba_temp_files group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEGB     FREEGB
---------------------------------------------------------------------------
SYSAUX                                              10                8.3
SYSTEM                                              15                7.4
TEMP                                    63.9999695                 64
UNDOTBS1                                        15                 13.4
UNDOTBS2                                        15                 14.5
USERS                                                63                22.3





Happy Learning ....

2 comments:

  1. Hit,

    This script does not show growth, it shows current state.

    Obviously to determine growth you could run this script regularly and then work out the differences between the results which would be the growth, this is quite a manual process.

    I don't know why Oracle can't automate this in the DB, wouldn't it be much easier if we could query some views?

    I've seen AWR/Statspack snapshot data mentioned but haven't investigated this myself.

    Jeremy

    ReplyDelete
  2. Hi Jezzer,

    You are absolutely right, we need to run this script on daily at certain time to calculate the actual growth. I forgot to mention. which i will do now.

    Thanks for Reminding.

    ReplyDelete