Oracle database size

Posted on 2011-05-05
Last Modified: 2013-12-18
Is there any query that I can execute to find out the size of the database? We need to migrate our database to another system so I need to make sure that the new platform has sufficient space. Oracle is deployed in Solaris.
Question by:ank5
    LVL 73

    Assisted Solution

    sum the bytes in dba_datafiles and dba_temp_files
    LVL 1

    Author Comment

    Thank you. Where can I find them?
    LVL 23

    Accepted Solution

    as pointed by sdstuber:

    - select the dba_data_files. this will display MB  allocated for all tablespaces
    select sum(bytes)/1024/1024 from dba_data_files;

    Open in new window

    - select the  dba_temp_files. this will calculate in MB allocated for temp
    select sum(bytes)/1024/1024 from dba_temp_files;

    Open in new window

    sum both result and you get the total database size in MB.
    LVL 23

    Expert Comment

    - i use the following query to monitor all our tablespace usage. this is a modified sql that display partly of - each tablespace allocated size. you can also sum this up to get the total database size:

    SELECT df.tablespace_name "Tablespace",
           df.bytes / (1024 * 1024) "Size (MB)",
           SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
           Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
           Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
      FROM dba_free_space fs,
           (SELECT tablespace_name,SUM(bytes) bytes
              FROM dba_data_files
             GROUP BY tablespace_name) df
     WHERE fs.tablespace_name (+)  = df.tablespace_name
     GROUP BY df.tablespace_name,df.bytes
    SELECT df.tablespace_name tspace,
           fs.bytes / (1024 * 1024),
           SUM(df.bytes_free) / (1024 * 1024),
           Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
           Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
      FROM dba_temp_files fs,
           (SELECT tablespace_name,bytes_free,bytes_used
              FROM v$temp_space_header
             GROUP BY tablespace_name,bytes_free,bytes_used) df
     WHERE fs.tablespace_name (+)  = df.tablespace_name
     GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

    Open in new window

    LVL 23

    Expert Comment

    - hmmm seems like i paste everything there. just sum the "Size (MB)" :)
    LVL 73

    Expert Comment

    I'm not sure what you are asking by "where can I find them"

    they are views within the database

    select sum(bytes) from (
    select bytes from dba_data_files
    union all
    select bytes from dba_temp_files
    LVL 73

    Expert Comment

    you'll need to add a little space for control files ,pfile/spfiles,  alert log, etc

    and your archive logs which could be a lot of space or a little depending on how frequently you roll them off to tape or other backup storage

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to recover a database from a user managed backup

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now