Tablespace Monitoring Script for Oracle Database

Monitoring tablespace usage in an Oracle database is essential for maintaining optimal performance and preventing space-related issues. This guide will walk you through creating a SQL script and a shell script to help you easily monitor your tablespaces.

Explanation:

• Permanent Tablespaces: The script retrieves tablespace name, allocated space, used space, free space, and usage percentage. If a tablespace’s usage exceeds 90%, an “ALERT” appears in the output.
• Temporary Tablespaces: Similar to permanent tablespaces

Step 1: Create the SQL Script (tbs.sql)

Open a terminal and create the tbs.sql file using vi:

vi tbs.sql

Press i to enter insert mode, and then paste the following SQL code:

SET PAGESIZE 50 LINESIZE 100 COLSEP '|'

COLUMN tablespace_name FORMAT A20
COLUMN allocated_mb FORMAT 999,999
COLUMN used_mb FORMAT 999,999
COLUMN free_mb FORMAT 999,999
COLUMN pct_used FORMAT 999.99
COLUMN alert FORMAT A10

SELECT
    df.tablespace_name,
    ROUND(SUM(df.bytes) / (1024 * 1024), 2) AS allocated_mb,
    ROUND(SUM(df.bytes - NVL(fs.bytes, 0)) / (1024 * 1024), 2) AS used_mb,
    ROUND(SUM(NVL(fs.bytes, 0)) / (1024 * 1024), 2) AS free_mb,
    ROUND((SUM(df.bytes - NVL(fs.bytes, 0)) / SUM(df.bytes)) * 100, 2) AS pct_used,
    CASE 
        WHEN ROUND((SUM(df.bytes - NVL(fs.bytes, 0)) / SUM(df.bytes)) * 100, 2) >= 90 THEN 'ALERT'
        ELSE ''
    END AS alert FROM dba_data_files df LEFT JOIN (SELECT tablespace_name,file_id,SUM(bytes) AS bytes
    FROM dba_free_space GROUP BY tablespace_name, file_id) fs ON df.file_id = fs.file_id AND                        df.tablespace_name = fs.tablespace_name GROUP BY df.tablespace_name ORDER BY pct_used DESC;

SET PAGESIZE 50 LINESIZE 100 COLSEP '|'
COLUMN tablespace_name FORMAT A20
COLUMN temp_allocated_mb FORMAT 999,999
COLUMN temp_used_mb FORMAT 999,999
COLUMN temp_free_mb FORMAT 999,999
COLUMN temp_pct_used FORMAT 999.99
COLUMN temp_alert FORMAT A10

SELECT
    tsh.tablespace_name,
    ROUND(SUM(tsh.bytes_used + tsh.bytes_free) / (1024 * 1024), 2) AS temp_allocated_mb,
    ROUND(SUM(tsh.bytes_used) / (1024 * 1024), 2) AS temp_used_mb,
    ROUND(SUM(tsh.bytes_free) / (1024 * 1024), 2) AS temp_free_mb,
    ROUND((SUM(tsh.bytes_used) / SUM(tsh.bytes_used + tsh.bytes_free)) * 100, 2) AS temp_pct_used,
    CASE 
    WHEN ROUND((SUM(tsh.bytes_used) / SUM(tsh.bytes_used + tsh.bytes_free)) * 100, 2) >= 90 THEN 'ALERT'
    ELSE ''
    END AS temp_alert FROM v$temp_space_header tsh GROUP BY  tsh.tablespace_name ORDER BY temp_pct_used DESC;

After pasting, press Esc to exit insert mode. Save and exit vi by typing :wq

Explanation:

• The script connects to the database as sysdba, runs tbs.sql, and outputs the result to tablespace_report.log.
• Finally, it displays the report content in the terminal.

Step 2: Create the Shell Script (monitor_tbs.sh)

Create the monitor_tbs.sh file using vi:

vi monitor_tbs.sh

Press i to enter insert mode, and then paste the following shell script code:

sqlplus -s "/ as sysdba" <<EOF > tablespace_report.log
@tbs.sql
EXIT
EOF
cat tablespace_report.log

After pasting, press Esc to exit insert mode. Save and exit vi by typing :wq

Step 3:Running the Monitoring Script

You can now run the script to generate the tablespace report:

sh monitor_tbs.sh

Sample Output:

You can also refer to the image below for a visual representation of the output:

Check the Output

You can also check the contents of the generated log file, tablespace_report.log, to review the tablespace usage information. This log file provides a detailed overview of allocated, used, and free space for each tablespace in your Oracle database.

Alert: The information provided on this website is for educational purposes only. It has been tested internally, but there is no guarantee it will work in every environment. It is strongly recommended to first test the process in a non-production environment, such as a User Acceptance Testing (UAT) setup. This allows you to verify configurations, identify any issues, and ensure a smooth transition to production, minimizing the risk of disruptions in your live environment. Always conduct thorough testing before applying changes to production systems.

I hope you found this blog helpful! We’d love to hear about your experiences or any questions you might have, so please share your thoughts in the comments. Your feedback is greatly appreciated. Stay tuned for more tips and advanced topics on managing Oracle Database!