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);
SELECT output FROM (
SELECT 1 as ord, '===========================================================' AS output FROM dual
UNION ALL
SELECT 2, 'Total Database Physical Size = ' || ROUND(redolog_size_gb+dbfiles_size_gb+tempfiles_size_gb+ctlfiles_size_gb,2) || ' GB'
FROM (
SELECT
(SELECT SUM(bytes)/1024/1024/1024 FROM v$log) redolog_size_gb,
(SELECT SUM(bytes)/1024/1024/1024 FROM dba_data_files) dbfiles_size_gb,
(SELECT NVL(SUM(bytes),0)/1024/1024/1024 FROM dba_temp_files) tempfiles_size_gb,
(SELECT SUM(block_size*file_size_blks)/1024/1024/1024 FROM v$controlfile) ctlfiles_size_gb
FROM dual
)
UNION ALL
SELECT 3, '===========================================================' FROM dual
UNION ALL
SELECT 4, 'Redo Logs Size : ' || ROUND((SELECT SUM(bytes)/1024/1024/1024 FROM v$log),3) || ' GB' FROM dual
UNION ALL
SELECT 5, 'Data Files Size : ' || ROUND((SELECT SUM(bytes)/1024/1024/1024 FROM dba_data_files),3) || ' GB' FROM dual
UNION ALL
SELECT 6, 'Temp Files Size : ' || ROUND((SELECT NVL(SUM(bytes),0)/1024/1024/1024 FROM dba_temp_files),3) || ' GB' FROM dual
UNION ALL
SELECT 7, 'Control Files Size : ' || ROUND((SELECT SUM(block_size*file_size_blks)/1024/1024/1024 FROM v$controlfile),3) || ' GB' FROM dual
UNION ALL
SELECT 8, '===========================================================' FROM dual
UNION ALL
SELECT 9, 'Actual Database Size = ' || ROUND(SUM(bytes)/1024/1024/1024,3) || ' GB'
FROM (SELECT bytes FROM v$datafile UNION ALL SELECT bytes FROM v$tempfile)
UNION ALL
SELECT 10, '===========================================================' FROM dual
UNION ALL
SELECT 11, 'Used Database Size : ' || ROUND((SELECT SUM(bytes)/1024/1024/1024 FROM (SELECT bytes FROM v$datafile UNION ALL SELECT bytes FROM v$tempfile)) - (SELECT SUM(bytes)/1024/1024/1024 FROM dba_free_space),3) || ' GB' FROM dual
UNION ALL
SELECT 12, 'Free Database Size : ' || ROUND((SELECT SUM(bytes)/1024/1024/1024 FROM dba_free_space),3) || ' GB' FROM dual
UNION ALL
SELECT 13, 'Data Pump Directory Size : ' || ROUND((SELECT SUM(filesize)/1024/1024/1024 FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))),3) || ' GB' FROM dual
UNION ALL
SELECT 14, 'BDUMP Size : ' || ROUND((SELECT SUM(filesize)/1024/1024/1024 FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('BDUMP'))),3) || ' GB' FROM dual
UNION ALL
SELECT 15, 'ADUMP Size : ' || ROUND((SELECT SUM(filesize)/1024/1024/1024 FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('ADUMP'))),3) || ' GB' FROM dual
UNION ALL
SELECT 16, '===========================================================' FROM dual
) ORDER BY ord;
No comments:
Post a Comment