asked on

Oracle database size

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.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ank5

ASKER

Thank you. Where can I find them?

ASKER CERTIFIED SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OP_Zaharin

- 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
UNION ALL
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
 ORDER BY 4 DESC;

Open in new window

OP_Zaharin

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

Sean Stuber

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
)

Sean Stuber

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