Description of Oracle Database SQL code for Tablespace Usage

This code provides a comprehensive overview of tablespace usage in your Oracle database. Here's a breakdown of its purpose, components, and key points:

Sample SQL Command

 1set pages 999
 2col tablespace_name format a40
 3col "size MB" format 999,999,999
 4col "free MB" format 99,999,999
 5col "% Used" format 999
 6select 	tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
 7,	decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
 8,	decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
 9               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
10from	(select tablespace_name, sum(bytes)/1024/1024 used_mb
11	from 	dba_data_files group by tablespace_name union all
12	select 	tablespace_name || '  **TEMP**'
13	,	sum(bytes)/1024/1024 used_mb
14	from 	dba_temp_files group by tablespace_name) tsu
15,	(select tablespace_name, sum(bytes)/1024/1024 free_mb
16	from 	dba_free_space group by tablespace_name) tsf
17where	tsu.tablespace_name = tsf.tablespace_name (+)
18order	by 4
19/

Sample Oracle Output:

 1TABLESPACE_NAME                               size MB     free MB % used
 2---------------------------------------- ------------ ----------- ------
 3USERS_VIT                                          25          25      0
 4INDX                                                5           5      1
 5PERFSTAT                                            5           5      1
 6USERS                                              25          25      3
 7UNDOTBS2                                        2,000       1,886      5
 8APP_USER_DATA                                      10          10      6
 9ODM                                                10          10      6
10TOOLS                                             600         509     15
11DRSYS                                              20          15     25
12WEB_DATA                                          100          72     28
13CAP_DATA                                        3,500       2,316     33
14CAP_INDEXES                                     4,500       2,705     39
15SYSAUX                                            350         132     62
16XDB                                                60          13     79
17SYSTEM                                            650          84     87
18ANDY                                               10           2     88
19TEMP  **TEMP**                                  1,050           0    100
20TEMP_VIT  **TEMP**                                 25           0    100
21
2218 rows selected.
23SQL>

Purpose:

• Calculate and display the total size, free space, and percentage used for each tablespace, including both permanent and temporary tablespaces.
• Highlight tablespaces with high utilization (> 90% used) for easier identification.

Breakdown:

1. Page size and column formatting:

• set pages 999: Increases the number of rows displayed on a single page for better visibility.
• Column formatting specifications:
  • tablespace_name: Maximum display length of 40 characters.
  • "size MB" and "free MB": Numerical values formatted with commas for readability.
  • "% Used": Percentage formatted with three decimal places.

2. Tablespace usage data:

• The code uses two nested SELECT statements to combine data from three views:
  • dba_data_files: Provides information about datafiles in permanent tablespaces.
  • dba_temp_files: Provides information about datafiles in temporary tablespaces.
  • dba_free_space: Provides information about free space within each tablespace.
• The first SELECT (aliased as tsu) calculates the total used space in MB for each tablespace by summing the size of all datafiles for both permanent and temporary tablespaces.
• The second SELECT (aliased as tsf) calculates the total free space in MB for each tablespace by summing the available free space.
• The WHERE clause joins the two selections based on the tablespace_name to match usage and free space information for each tablespace.

3. Percentage used calculation:

• The DECODE function handles potential null values for free space:
  • If free space is null, set it to 0 to avoid division by zero errors.
  • Otherwise, calculate the percentage used by subtracting the ratio of free space to used space from 100 and rounding up.

4. Ordering and highlighting:

• The ORDER BY clause sorts the results by percentage used in descending order, showcasing heavily utilized tablespaces first.
• The code might have additional logic (not shown) to highlight tablespaces exceeding a certain usage threshold (e.g., > 90%) for easier identification.

Key points:

• This code offers a convenient way to monitor tablespace utilization across both permanent and temporary tablespaces.
• The formatting helps visualize the information quickly.
• The percentage used calculation ensures accuracy even if some tablespaces have no free space.
• Highlighting critical tablespaces aids in proactive management.

Insights and explanations:

• Understanding tablespace usage is crucial for managing database performance and ensuring efficient resource allocation.
• Monitoring tablespace growth helps prevent sudden space exhaustion and potential downtime.
• Analyzing the distribution of utilized space across different tablespaces can point to specific tables or applications requiring optimization.
• Consider adjusting thresholds and highlighting logic based on your specific database needs and monitoring preferences.

Additional notes:

• This code snippet represents a basic implementation. Specific formatting and highlighting logic might vary depending on your environment and preferences.
• For advanced monitoring and analysis, consider exploring Oracle's built-in tools like Automatic Storage Management (ASM) or Dynamic Performance Views (DPVs).