How to Automate Oracle Database Health Exports with Python

The real-world scenario

Imagine you are a Senior Database Administrator or a DevOps Engineer responsible for a mission-critical Oracle cluster. Every morning at 8 AM, you manually log into SQL Developer, run a series of health-check queries to monitor tablespace usage or active sessions, and copy the results into a report. This is the equivalent of manually checking the oil in your car by dipping a stick every ten miles. It is repetitive, prone to human error, and keeps you away from high-level architecture tasks. This script automates that entire flow, generating timestamped data snapshots and saving them to organized directories.

The solution

We use the python-oracledb library, which is the modern successor to cx_Oracle. It supports a Thin mode that does not require the Oracle Instant Client binaries, making deployment significantly easier. We combine this with pandas for data structuring and pathlib for robust, cross-platform file system management.

Prerequisites

Ensure you have Python 3.8 or higher installed. Run the following command to install the necessary libraries:

pip install oracledb pandas

The code

"""
-----------------------------------------------------------------------
Authors: Sharanam & Vaishali Shah
Recipe: Oracle Automated Health Export
Intent: Connects to Oracle DB in Thin mode, executes SQL, and exports results.
-----------------------------------------------------------------------
"""
import oracledb
import pandas as pd
from pathlib import Path
from datetime import datetime
import sys

def export_oracle_data():
    # Configuration - Replace with your environment variables or vault secrets
    DB_CONFIG = {
        "user": "SYSTEM",
        "password": "YourSecurePassword",
        "dsn": "localhost:1521/xe"  # Format: host:port/service_name
    }

    # Define the SQL query for health check (e.g., Tablespace Usage)
    SQL_QUERY = """
    SELECT 
        tablespace_name, 
        used_percent, 
        status 
    FROM dba_tablespace_usage_metrics
    """

    # Setup file paths using pathlib
    base_path = Path.cwd() / "reports"
    base_path.mkdir(exist_ok=True)
    
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_file = base_path / f"db_health_{timestamp}.json"

    try:
        # Connect using Thin mode (no Oracle Client required)
        print(f"Connecting to Oracle Database at {DB_CONFIG['dsn']}...")
        with oracledb.connect(
            user=DB_CONFIG["user"],
            password=DB_CONFIG["password"],
            dsn=DB_CONFIG["dsn"]
        ) as connection:
            
            print("Executing health check query...")
            # Load data into a Pandas DataFrame directly
            df = pd.read_sql(SQL_QUERY, connection)

            if df.empty:
                print("No data retrieved from the query.")
                return

            # Export data to JSON
            df.to_json(output_file, orient="records", indent=4)
            print(f"Success! Report generated at: {output_file}")

    except oracledb.Error as e:
        error_obj, = e.args
        print(f"Oracle Error: {error_obj.message}")
        sys.exit(1)
    except Exception as e:
        print(f"General Error: {str(e)}")
        sys.exit(1)

if __name__ == "__main__":
    export_oracle_data()

Code walkthrough

The script begins by importing oracledb, the standard driver for Oracle connectivity. We use Path from the pathlib library to ensure the script works perfectly on both Windows and Linux without worrying about backslashes or forward slashes in file paths.

The DB_CONFIG dictionary holds the connection credentials. In a production environment, you would typically fetch these from environment variables or a secret manager like AWS Secrets Manager. The oracledb.connect function is called in Thin mode by default, which means it talks directly to the database without needing heavy client software.

We leverage pd.read_sql because it handles the cursor lifecycle automatically. It opens the cursor, fetches all rows, converts them into a tabular format, and closes the cursor in one step. Finally, the to_json method persists the data to the reports directory with a unique timestamp, ensuring you never overwrite previous diagnostic data.

Sample output

When you execute the script in your terminal, you will see the following progression:

Connecting to Oracle Database at localhost:1521/xe...
Executing health check query...
Success! Report generated at: /home/user/project/reports/db_health_20231027_080001.json

The resulting db_health_20231027_080001.json file will contain structured data like this:

[
    {
        "TABLESPACE_NAME": "SYSTEM",
        "USED_PERCENT": 84.2,
        "STATUS": "ONLINE"
    },
    {
        "TABLESPACE_NAME": "USERS",
        "USED_PERCENT": 12.5,
        "STATUS": "ONLINE"
    }
]

Conclusion

Automating Oracle maintenance with Python eliminates the drudgery of manual monitoring. By using the Thin driver and pandas, you create a lightweight, portable tool that can be scheduled via cron or Task Scheduler. This approach ensures your database health data is consistently captured, archived, and ready for analysis or alerting systems.