below query will show usage by datafiles, tempfiles, controlfiles, redo log files, dump and logs directories and is useful to find where your disk usage distributed on database components
SQL> select'===========================================================' || chr(10) ||
'Total Database Physical Size = ' || round(redolog_size_gb+dbfiles_size_gb+tempfiles_size_gb+ctlfiles_size_gb,2) || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
' Redo Logs Size : ' || round(redolog_size_gb,3) || ' GB' || chr(10) ||
' Data Files Size : ' || round(dbfiles_size_gb,3) || ' GB' || chr(10) ||
' Temp Files Size : ' || round(tempfiles_size_gb,3) || ' GB' || chr(10) ||
' Control Files Size : ' || round(ctlfiles_size_gb,3) || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
'Actual Database Size = ' || db_size_gb || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
' Used Database Size : ' || used_db_size_gb || ' GB' || chr(10) ||
' Free Database Size : ' || free_db_size_gb || ' GB' ||chr(10) ||
' Data Pump Directory Size : ' || dpump_db_size_gb || ' GB' || chr(10) ||
' BDUMP Size : ' || bdump_db_size_gb || ' GB' || chr(10) ||
' ADUMP Size : ' || adump_db_size_gb || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
'Total Size (including Dump and Log Files) = ' || round(round(redolog_size_gb,2) +round(dbfiles_size_gb,2)+round(tempfiles_size_gb,2)+round( ctlfiles_size_gb,2) +round(adump_db_size_gb,2) +round(dpump_db_size_gb,2)+round(bdump_db_size_gb,2),2) || ' GB' || chr(10) ||
'===========================================================' || chr(10)
as summary
from (
select sys_context('USERENV', 'DB_NAME') db_name,
(select sum(bytes)/1024/1024/1024 redo_size from v$log ) redolog_size_gb,
(select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) dbfiles_size_gb
,(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) tempfiles_size_gb
,(select sum(blocks*block_size/1024/1024/1024) size_mb from v$archived_log where DELETED = 'NO') archlog_size_gb
,(select sum(block_size*file_size_blks)/1024/1024/1024 controlfile_size from v$controlfile) ctlfiles_size_gb
,round(sum(used.bytes)/1024/1024/1024,3) db_size_gb
,round(sum(used.bytes)/1024/1024/1024,3) - round(free.f/1024 /1024/ 1024) used_db_size_gb
,round(free.f/1024/1024/1024,3) free_db_size_gb
,(select round(sum(filesize)/1024/1024/1024,3) from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('BDUMP'))) bdump_db_size_gb
,(select round(sum(filesize)/1024/1024/1024,3) from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('ADUMP'))) adump_db_size_gb
,(select round(sum(filesize)/1024/1024/1024,3) from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))) dpump_db_size_gb
from (select bytes from v$datafile
union all
select bytes from v$tempfile) used
,(select sum(bytes) as f from dba_free_space) free
group by free.f);
Sample Result looks like :-
SUMMARY
-----------------------------------------------------------------------------------------------------
===========================================================
Total Database Physical Size = 1360.61 GB
===========================================================
Redo Logs Size : 4 GB
Data Files Size : 1355.515 GB
Temp Files Size : 1.074 GB
Control Files Size : .022 GB
===========================================================
Actual Database Size = 1356.589 GB
===========================================================
Used Database Size : 1294.589 GB
SUMMARY
-----------------------------------------------------------------------------------------------------
Free Database Size : 62.2 GB
Data Pump Directory Size : .002 GB
BDUMP Size : .007 GB
ADUMP Size : .02 GB
===========================================================
Total Size (including Dump and Log Files) = 1360.64 GB
===========================================================
No comments:
Post a Comment