• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to find the Oracle Database size

by admin

Question: How to calculate the total size of an Oracle database?

1. Size of datafiles in MB:

sql> select sum(bytes)/(1024*1024) from dba_data_files;

2. Size of tempfiles in MB:

sql> select sum(bytes)/(1024*1024) from dba_temp_files;

3. Size of redo logs in MB:

– Not accounting for mirrored redolog files:

sql> select sum(bytes)/(1024*1024) from v$log;

4. To get the used-up space of your datafiles:
This will give you the total used-up space inside the database in MB.

sql> select sum(bytes)/(1024*1024) from dba_segments;

Total Size of the database

Also accounting for controlfiles and mirrored redolog files.

select a.data_size+b.temp_size+c.redo_size+d.cont_size "total_size"
from ( select sum(bytes) data_size
       from dba_data_files ) a,
     ( select nvl(sum(bytes),0) temp_size
       from dba_temp_files ) b,
     ( select sum(bytes) redo_size
       from sys.v_$logfile lf, sys.v_$log l
       where lf.group# = l.group#) c,
     ( select sum(block_size*file_size_blks) cont_size
       from v$controlfile ) d;

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to check which options or features are used in the Oracle database
  2. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  3. Oracle OS watcher (OSWatcher) – Understanding oswiostat
  4. Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited
  5. How to duplicate a Oracle Database to a previous Incarnation
  6. How to Enable or Disable Veritas ODM for Oracle database 12.1.0.1
  7. Oracle 11G RMAN – Understanding UNDO backup optimisation
  8. How to change static parameters through SPFILE parameter file in Oracle Database
  9. How to backup and delete archivelogs older than # number of days
  10. Oracle Database 18c new feature – Scalable Sequences

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright