• 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. Understanding the Global Resource Management Concepts in Oracle RAC
  2. Oracle Interview Questions : Grid Infrastructure Single Client Access Name (SCAN)
  3. tuned-adm and Oracle
  4. Rman Backups Failing with RMAN-20004
  5. Patching and Upgrading Databases in Oracle Data Guard Configuration
  6. Define redo log files in an Oracle RAC environment
  7. Oracle Interview Questions – Flash Recovery Area
  8. Oracle 12.2 : RMAN Cross-Platform Transport of PDB into Destination CDB
  9. How to set custom device names using udev in CentOS/RHEL 7
  10. Beginners Guide to Sequences in Oracle

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright