Wednesday, June 8, 2011

How can I know the size of my Oracle database?

How can I know the size of my Oracle database?
=====================================================


An oracle database consists of data files, redo log files, control files, temporary files.
Whenever you say the size of the database this actually means the summation of these files.

Also, if you are in archive log mode, you will generate files in your archive log
destination. Issue the following query to see where your archived redo
logs get placed:

select * from v$parameter where name = 'log_archive_dest';



overall Database Size Query will be ...


SELECT round( a.data_size
+ b.temp_size
+ c.redo_size
+ d.controlfile_size,4) "total_size in GB"
FROM (SELECT SUM (BYTES) / 1024 / 1024/ 1024 data_size
FROM dba_data_files) a,
(SELECT NVL (SUM (BYTES), 0) / 1024 / 1024/ 1024 temp_size
FROM dba_temp_files) b,
(SELECT SUM (BYTES) / 1024 / 1024/ 1024 redo_size
FROM SYS.v_$log) c,
(SELECT SUM (block_size * file_size_blks) / 1024/ 1024
/ 1024 controlfile_size
FROM v$controlfile) d



or you can see it

---its only oracle datafiles summation size

SELECT name, highwater/ 1024 / 1024/ 1024, last_value / 1024 / 1024/ 1024
FROM dba_high_water_mark_statistics where name = 'DB_SIZE';

No comments: