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 ....
Hit,
ReplyDeleteThis 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
Hi Jezzer,
ReplyDeleteYou 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.