Query to Check Database Size

 
Query to Check database size

— Version 1
— Includes only USED SPACE in db_data_files. Redo log files, undo files and temporary tablespace are included
SELECT DB_FILES.DB_SIZE +
REDO_LOG_FILES.DB_SIZE +
TEMP_FILES.DB_SIZE +
UNDO_FILES.DB_SIZE
FROM (SELECT ((max.meg - free.meg) /1024) DB_SIZE
FROM (SELECT sum( bytes / 1024 / 1024 ) meg
FROM dba_data_files
WHERE tablespace_name <> 'UNDO'
OR tablespace_name not like '%UNDO%' ) max,
(SELECT sum( bytes / 1024 / 1024 ) meg
FROM dba_free_space WHERE tablespace_name <> 'UNDO'
OR tablespace_name not like '%UNDO%') free) DB_FILES,
(SELECT sum((bytes*members))/1024/1024/1024 DB_SIZE
FROM v$log) REDO_LOG_FILES,
(SELECT sum(bytes)/1024/1024/1024 DB_SIZE
FROM dba_temp_files) TEMP_FILES,
(SELECT sum(bytes)/1024/1024/1024 DB_SIZE
FROM dba_data_files
WHERE tablespace_name = 'UNDO'
OR tablespace_name like '%UNDO%') UNDO_FILES

— Version 2
— Includes only Full data file size (used and unused space) in db_data_files. Redo log files, undo files and temporary tablespace are included
SELECT DB_FILES.DB_SIZE +
REDO_LOG_FILES.DB_SIZE +
TEMP_FILES.DB_SIZE +
UNDO_FILES.DB_SIZE
FROM (SELECT sum( bytes )/( 1024 * 1024 * 1024 ) DB_SIZE
FROM dba_data_files
WHERE tablespace_name <> 'UNDO'
OR tablespace_name not like '%UNDO%') DB_FILES,
(SELECT sum((bytes*members))/1024/1024/1024 DB_SIZE
FROM v$log) REDO_LOG_FILES,
(SELECT sum(bytes)/1024/1024/1024 DB_SIZE
FROM dba_temp_files) TEMP_FILES,
(SELECT sum(bytes)/1024/1024/1024 DB_SIZE
FROM dba_data_files
WHERE tablespace_name = 'UNDO'
OR tablespace_name like '%UNDO%') UNDO_FILES

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart