Assessing Database Storage Utilization in Oracle Database

How large is the Oracle Database?



SQL Code

 1col "Database Size" format a20
 2col "Free space" format a20
 3col "Used space" format a20
 4select  round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
 5       ,round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 6        round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
 7       ,round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
 8from    (select bytes
 9        from    v$datafile
10        union   all
11        select  bytes
12        from    v$tempfile
13        union   all
14        select  bytes
15        from    v$log) used
16       ,(select sum(bytes) as p
17        from dba_free_space) free
18group by free.p
19/

Sample Oracle Output:

 11 col "Database Size" format a20
 22 col "Free space" format a20
 33 col "Used space" format a20
 44select	round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
 55 ,	round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
 66	round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
 77 ,	round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
 88 from    (select	bytes
 99	from	v$datafile
1010	union	all
1111	select	bytes
1212	from 	v$tempfile
1313	union 	all
1414	select 	bytes
1515	from 	v$log) used
1616,	(select sum(bytes) as p
1717	from dba_free_space) free
1818 group by free.p
19/
20
21Database Size        Used space           Free space
22-------------------- -------------------- --------------------
23564 GB               493 GB               71 GB
24
25SQL>

Purpose:

  • To calculate and display an overview of the database's size, including used space and free space, providing key insights into storage utilization.

Breakdown:

  1. Formatting Output:

    • col "Database Size" format a20, col "Free space" format a20, col "Used space" format a20: Formats column headers for readability.
  2. Calculating Size and Space:

    • Gathering Data:

      • (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used: Retrieves total bytes from datafiles, tempfiles, and redo logs, representing used space.
      • (select sum(bytes) as p from dba_free_space) free: Calculates total free space in the database.
    • Calculations and Formatting:

      • round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size": Calculates total database size in GB.
      • round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space": Calculates used space in GB.
      • round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space": Calculates free space in GB.
  3. Presenting Results:

    • select ... group by free.p: Selects and groups the calculated values for display.

Key Points:

  • Combines information from multiple database views to provide a comprehensive overview.
  • Employs calculations and formatting to present size and space in a clear and concise GB format.
  • Groups results by free space for clarity.

Insights and Explanations:

  • Capacity Management: This code aids in understanding database capacity utilization, informing decisions regarding potential expansion or optimization needs.
  • Troubleshooting: Can help identify potential storage issues, such as unexpectedly low free space or excessive growth in used space.
  • Customization: Can be modified to focus on specific tablespaces or file types for more granular analysis.
  • Integration: Consider integrating this code into regular database monitoring and reporting processes to track storage trends and proactively address potential issues.

Posts in this Series