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.