Monday, June 27, 2011

Script to find Oracle database size -

To find out how many MBs are allocated to ALL datafiles:
select sum(bytes)/1024/1024 "MB" from dba_data_files;

Find size of the online redo logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Find the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "MB" from dba_temp_files;

Find size of the control files usage,
SQL> select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MB" from v$controlfile;


Find all in one go

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

No comments:

Post a Comment