Forgot your password?    
+ Reply to Thread
Results 1 to 4 of 4

Thread: Calculate Total Size of an Oracle Database

  1. #1
    Oracle DBA
    Join Date
    Oct 2011
    Posts
    3

    Calculate Total Size of an 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.

    The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to all datafiles:
    select sum(bytes)/1024/1024 "Meg" from dba_data_files;

    To get the size of all TEMP files:
    select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

    To get the sum total size of all the on-line redo-logs:
    select sum(bytes)/1024/1024 "Meg" from v$log;


    To get the size of the control files use,
    SQL> select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MEG" from v$controlfile;

    You can also get information regarding the size of the database by combining multiple queries Into a single query as follows:

    SQL> select (a.data_size+b.temp_size+c.redo_size+d.controlfile _size)/1024/1024 "Size_in_MB” 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 v$log ) c,
    ( select sum(block_size*file_size_blks) controlfile_size from v$controlfile) d;

    Size_in_MB
    --------------
    986.5
    Last edited by imran; 11-03-2011 at 01:00 PM.

  2. #2
    Oracle DBA
    Join Date
    Oct 2011
    Posts
    11
    Thanks Imran for nice post.

  3. #3
    gud work Imran

  4. #4
    Oracle DBA
    Join Date
    Oct 2011
    Posts
    3
    "thanx.. sir.."

+ Reply to Thread

Similar Threads

  1. Finding size and automating it in CRON
    By dbaANKIT in forum OS for Oracle Administration
    Replies: 0
    Last Post: 12-26-2012, 06:02 PM
  2. How to see the top10 Requests based on total runtime on a given day.
    By dbaANKIT in forum SCRIPTS : Concurrent Requests & Concurrent Request Sessions
    Replies: 0
    Last Post: 08-18-2012, 12:53 AM
  3. how to know the size of cf,df,rf?
    By saioracleappsdba in forum Core Database Administration and Monitoring
    Replies: 1
    Last Post: 05-11-2012, 05:50 PM
  4. How we can find out the size of database and when the database bounced last time ???
    By swapan in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 05-11-2012, 04:42 PM
  5. Calculating the UNDO SIZE and Sizing the Undo Tablespace
    By Pradeep in forum Core Database Administration and Monitoring
    Replies: 1
    Last Post: 01-09-2012, 05:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics