How to Find Out the size of your Oracle Database

How to Find Out the size of your Oracle Database

by

How to Find Out the size of your Oracle Database : In this Article we are going to discuss about How to Find Out the size of your Oracle Database.

Database Sizes :

We can find out Database Total Size, Database Used Size, Database Free Size using below queries.

SQL> set lines 400 pages 4000
SQL> select sum(bytes/1024/1024/1024) "Database Total Size in GB" from dba_data_files;

Database Total Size in GB
-------------------------
               1.81152344

SQL>
SQL> select sum(bytes/1024/1024/1024) "Database Used Size in GB" from dba_segments;

Database Used Size in GB
------------------------
              1.53179932

SQL>
SQL> select sum(bytes/1024/1024/1024) "Database Free Size in GB" from dba_free_space;

Database Free Size in GB
------------------------
              .274841309

SQL>

Also you can use below mentioned single query to find out Database Total Size, Database Used Size, Database Free Size at a time.

SQL> select sum(a.bytes+b.bytes)/1024/1024 "Database Total Size(MB)",sum(c.bytes)/1024/1024 "Database Used Space(MB)",sum(d.bytes)/1024/1024 "Database Free Space(MB)" from dba_data_files a,dba_temp_files b,dba_segments c,dba_free_space d;

Database Total Size(MB) Database Used Space(MB) Database Free Space(MB)
----------------------- ----------------------- -----------------------
               92640730                 68256.5                  762281

SQL>

How to check the database growth : Using below script we can calculate the database growth daily and weekly.

SQL> set lines 1000
SQL> set pages 1000
SQL> col "Database Size" format a13
SQL> col "used Space" format a11
SQL> col "used in %" format a11
SQL> col "free in %" format a11
SQL> col "Database Name" format a13
SQL> col "free Space" format a12
SQL> col "Growth DAY" format a11
SQL> col "Growth WEEK" format a12
SQL> col "Growth DAY in %" format a16
SQL> col "Growth WEEK in %" format a16
SQL> SELECT
  2  (select min(creation_time) from v$datafile) "Create Time",
  3  (select name from v$database) "Database Name",
  4  round((sum(used.bytes) / 1024 / 1024 ),2) || ' MB' "Database Size",
  5  round((sum(used.bytes) / 1024 / 1024 ) - round(free.P / 1024 / 1024 ),2) || ' MB' "used Space",
  6  round(((sum(used.bytes) / 1024 / 1024 ) - (free.P / 1024 / 1024 )) / round(sum(used.bytes) / 1024 / 1024 ,2)*100,2) || '% MB' "used in %",
  7  round((free.P / 1024 / 1024 ),2) || ' MB' "free Space",
  8  round(((sum(used.bytes) / 1024 / 1024 ) - ((sum(used.bytes) / 1024 / 1024 ) - round(free.P / 1024 / 1024 )))/round(sum(used.bytes) / 1024 / 1024,2 )*100,2) || '% MB' "free in %",
  9  round(((sum(used.bytes) / 1024 / 1024 ) - (free.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
 10  round(((sum(used.bytes) / 1024 / 1024 ) - (free.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/round((sum(used.bytes) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
 11  round(((sum(used.bytes) / 1024 / 1024 ) - (free.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
 12  round((((sum(used.bytes) / 1024 / 1024 ) - (free.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/round((sum(used.bytes) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
 13  FROM    (SELECT bytes FROM V$DATAFILE
 14  UNION ALL
 15  SELECT bytes FROM V$TEMPFILE
 16  UNION ALL
 17  SELECT bytes FROM V$LOG) used,
 18  (SELECT sum(bytes) AS P FROM dba_free_space) free
 19  GROUP BY free.P;

Create Ti Database Name Database Size used Space  used in %   free Space   free in %   Growth DAY  Growth DAY in %  Growth WEEK  Growth WEEK in %
--------- ------------- ------------- ----------- ----------- ------------ ----------- ----------- ---------------- ------------ ----------------
30-MAY-19 ORCL          1975 MB       1937 MB     98.06% MB   38.38 MB     1.92% MB    2.62 MB     .133% MB         18.33 MB     .928% MB

SQL>

Using below query we can check a specified table size in oracle.

SQL> select segment_name,sum(bytes)/1024/1024/1024 GB from user_segments where segment_type='TABLE' and segment_name=upper('&TABLE_NAME') group by segment_name;
Enter value for table_name: EMP
old   1: select segment_name,sum(bytes)/1024/1024/1024 GB from user_segments where segment_type='TABLE' and segment_name=upper('&TABLE_NAME') group by segment_name
new   1: select segment_name,sum(bytes)/1024/1024/1024 GB from user_segments where segment_type='TABLE' and segment_name=upper('EMP') group by segment_name

SEGMENT_NAME                           GB
------------------------------ ----------
EMP                            .000061035

SQL>

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.

Leave a Comment