As an Oracle Database Administrator (DBA), having a curated set of SQL scripts is akin to having a trusty toolkit that assists in various scenarios. These scripts serve as invaluable resources for diagnosing performance issues, assessing configurations, addressing user inquiries, and more. Here are seven essential Oracle DBA scripts that can streamline your database management tasks:

1. Tablespace Usage Script: This script provides a comprehensive overview of tablespace usage, displaying details like allocated space, free space, used space, and growth trends over time. It helps in proactive monitoring and managing storage space efficiently.

SELECT tablespace_name,

       ROUND(SUM(bytes) / 1024 / 1024, 2) AS “Total Size (MB)”,

       ROUND(SUM(bytes – NVL(free_space, 0)) / 1024 / 1024, 2) AS “Used Size (MB)”,

       ROUND(NVL(SUM(free_space), 0) / 1024 / 1024, 2) AS “Free Size (MB)”,

       ROUND((SUM(bytes – NVL(free_space, 0)) / SUM(bytes)) * 100, 2) AS “Used %”

FROM dba_data_files ddf

LEFT JOIN (SELECT file_id, SUM(bytes) free_space

           FROM dba_free_space

           GROUP BY file_id) dfs ON ddf.file_id = dfs.file_id

GROUP BY tablespace_name;

2. Session and Locks Script: This script helps identify active sessions and their associated locks. It’s instrumental in troubleshooting performance bottlenecks caused by locks or long-running queries.

SELECT s.username,

       s.sid,

       s.serial#,

       l.blocking_session status,

       s.status,

       s.osuser,

       s.machine,

       l.type,

       l.id1,

       l.id2

FROM v$session s

JOIN v$lock l ON s.sid = l.sid

WHERE l.blocking_session IS NOT NULL OR l.request = 0;

3. Long-Running Queries Script: This script identifies SQL statements that are consuming excessive resources and running for an extended duration, aiding in performance optimization.

SELECT sql_text,

       elapsed_time / 1000000 AS elapsed_secs,

       cpu_time / 1000000 AS cpu_secs,

       fetches,

       executions

FROM v$sql

WHERE elapsed_time > 300000000; — Adjust threshold as needed

4. Database Configuration Script: This script provides a snapshot of critical database parameters and configurations essential for troubleshooting and auditing purposes.

SHOW PARAMETER memory_target; — Example parameter, add more as needed

5. Backup Status Script: Monitoring backup status is crucial. This script fetches information on the last backup taken and its completion status.

SELECT * FROM v$rman_backup_job_details

ORDER BY start_time DESC;

6. Index Fragmentation Script: Identifying fragmented indexes is crucial for maintaining optimal database performance. This script identifies fragmented indexes.

SELECT index_name,

       tablespace_name,

       blevel,

       leaf_blocks,

       DISTINCT_KEYS,

       CLUSTERING_FACTOR

FROM dba_indexes

WHERE table_owner = ‘YOUR_SCHEMA’

ORDER BY blevel DESC;

7. Temp Space Usage Script: Monitoring temporary tablespace usage is essential. This script tracks space utilization within temporary tablespaces.

SELECT tablespace_name,

       ROUND(SUM(bytes) / 1024 / 1024, 2) AS “Total Size (MB)”,

       ROUND(SUM(bytes_used) / 1024 / 1024, 2) AS “Used Size (MB)”,

       ROUND(SUM(bytes_free) / 1024 / 1024, 2) AS “Free Size (MB)”

FROM v$temp_space_header

GROUP BY tablespace_name;

These scripts serve as foundational tools for Oracle DBAs, offering insights and solutions to common challenges encountered in database management.

DBAs can proactively monitor, diagnose, and resolve issues that commonly arise in database environments. Whether it’s ensuring optimal storage allocation, identifying and resolving performance bottlenecks, or maintaining backup integrity, these scripts provide critical insights and actionable information.

Moreover, customization of these scripts based on specific requirements and environments further enhances their utility. Continuous refinement and adaptation of these scripts to match evolving database needs are key to staying ahead in managing Oracle databases efficiently.

Ultimately, these scripts are more than just code snippets—they represent the expertise, foresight, and adaptability of a skilled Oracle DBA. By integrating these scripts into regular operational practices, DBAs can streamline workflows, improve responsiveness, and ensure the overall health and performance of Oracle databases.

In the dynamic landscape of database administration, these foundational scripts stand as pillars of support, enabling DBAs to navigate challenges, optimize performance, and ensure the seamless functioning of Oracle database systems.