5 Replies Latest reply: Jan 9, 2012 1:58 AM by $Amogh K RSS

    sql script for Database size

    578069
      Hi All,

      I need a database script for calculating the database size.

      I am preparing a report in which in need to show the database growth pattern.

      Let me know some useful scripts.


      Also, if possible need to know how to find out the space used and free within a Datafile of a tablespace in oracle 10g or 11g database.


      Thanks
      Peeks
        • 1. Re: sql script for Database size
          839439
          check this scripts :

          >
          select (a.data_size+b.temp_size+c.redo_size+d.controlfile_size)/1024 "total_size in GB" from ( select sum(bytes)/1024/1024 data_size from dba_data_files) a,( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,(select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
          from v$controlfile) d;
          >

          for database growth , check the below link :

          http://neeraj-dba.blogspot.com/2011/04/tracking-oracle-database-growth.html
          http://neeraj-dba.blogspot.com/2011/12/estimate-tablespace-growth-in-oracle.html


          Hope thos help you :)

          --neeraj                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
          • 2. Re: sql script for Database size
            sb92075
            http://www.lmgtfy.com/?q=oracle+space+usage
            • 3. Re: sql script for Database size
              Helios-GunesEROL
              Hi;

              You can try to use:

              SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size
              "total_size in GB"
              FROM (SELECT SUM (bytes) / 1024 / 1024/1024 data_size FROM dba_data_files) a,
              (SELECT NVL (SUM (bytes), 0) / 1024 / 1024/1024 temp_size
              FROM dba_temp_files) b,
              (SELECT SUM (bytes) / 1024 / 1024/1024 redo_size FROM sys.v_$log) c,
              (SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1024/1024
              controlfile_size
              FROM v$controlfile) d;

              Regard
              Helios
              • 4. Re: sql script for Database size
                damorgan
                Define "database."

                I think we can all agree that your calculation must include datafiles, which includes undo) and tempfiles. But consider too the size of multiplexed and archived redo logs, the storage of backups, flashback logs, and trace files.

                Also consider the storage layer. A lot of installs these days are mixing RAID 5 (datafiles) with RAID 10 (undo, redo, and temp) or have succeeded in ridding themselves of RAID5 altogether. The storage requirement in a RAID environment requires a multiplication factor that can be as large as 100%.
                • 5. Re: sql script for Database size
                  $Amogh K
                  I need a database script for calculating the database size.
                  You can use below scripts for the same

                  --Total size
                  select sum(bytes)/1024/1024/1024 "Size in MB"
                  from dba_data_files;

                  --Total size of database with tempfiles
                  select (a.x+b.y+c.z)/(1024*1024*1024) DB_SIZE from
                  (select sum(bytes) x from dba_data_files)a,
                  (select sum(bytes)y from v$tempfile)b,
                  (select sum(bytes)z from v$log)c;

                  --Used Space in Database
                  select sum(bytes)/1024/1024/1024 from dba_Segments;
                  Also, if possible need to know how to find out the space used and free within a Datafile of a tablespace in oracle 10g or 11g database.
                  SELECT SUBSTR (a.file_id, 1, 3) FILE#, SUBSTR (file_name, 1, 80) file_name,
                  a.BYTES / 1024 / 1024 tot_bytes, b.free_bytes / 1024 / 1024 free
                  FROM dba_data_files a,
                  (SELECT file_id, SUM (BYTES) free_bytes
                  FROM dba_free_space
                  GROUP BY file_id) b
                  WHERE a.file_id = b.file_id(+) AND a.tablespace_name = '&tablespace_name'
                  ORDER BY 2;

                  Thanks,
                  Amogh