[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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.
0
ank5
Asked:
ank5
  • 3
  • 3
2 Solutions
 
sdstuberCommented:
sum the bytes in dba_datafiles and dba_temp_files
0
 
ank5Author Commented:
Thank you. Where can I find them?
0
 
OP_ZaharinCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OP_ZaharinCommented:
- 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

0
 
OP_ZaharinCommented:
- hmmm seems like i paste everything there. just sum the "Size (MB)" :)
0
 
sdstuberCommented:
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
)
0
 
sdstuberCommented:
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
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now