Temp Table Space 

Performing a health check on your Oracle database involves running several scripts and commands to gather important information about the database's current state and performance. Below is a list of scripts and commands you might run to perform a health check, along with brief explanations of what each script does:

 **1. Database Configuration**

#### **1.1. Database Details**

SELECT * FROM v$database;

#### **1.2. Instance Details**

SELECT * FROM v$instance;

### **2. Performance Metrics**

#### **2.1. Session Information**

SELECT * FROM v$session WHERE status = 'ACTIVE';

#### **2.2. Wait Events**

SELECT * FROM v$system_event WHERE total_waits > 0 ORDER BY total_waits DESC;

### **3. Storage and Space**

#### **3.1. Tablespace Usage**

SELECT 

    tablespace_name, 

    ROUND(SUM(bytes)/1024/1024) Total_MB,

    ROUND(SUM(bytes)/1024/1024) - ROUND(SUM(bytes_free)/1024/1024) Used_MB,

    ROUND(SUM(bytes_free)/1024/1024) Free_MB

FROM 

    (SELECT 

        tablespace_name, 

        bytes,

        NULL bytes_free

    FROM 

        dba_data_files

    UNION ALL

    SELECT 

        tablespace_name,

        NULL bytes,

        bytes bytes_free

    FROM 

        dba_free_space)

GROUP BY tablespace_name;

#### **3.2. Segment Space Usage**

SELECT segment_name, bytes/1024/1024 MB FROM dba_segments;

### **4. Resource Utilization**

#### **4.1. CPU Utilization**

SELECT 

    value 

FROM 

    v$sysmetric 

WHERE 

    metric_name = 'CPU Usage Per Sec' 

    AND group_id = 2;

#### **4.2. Memory Utilization**

SELECT 

    ROUND((BYTES/1024/1024),2) "MB" 

FROM 

    v$sgastat 

WHERE 

    name = 'free memory';

### **5. Backup and Recovery**

#### **5.1. Last Backup Details**

SELECT * FROM v$rman_backup_job_details ORDER BY start_time DESC;

#### **5.2. Archive Log Status**

SELECT * FROM v$archive_dest_status;

### **6. Security**

#### **6.1. Expired Users**

SELECT * FROM dba_users WHERE account_status = 'EXPIRED';

#### **6.2. Profiles with Default Password**

SELECT * FROM dba_users_with_defpwd;

### **Note:**

- Make sure to run these scripts with necessary privileges. You might need to log in as a SYSDBA or as a user with sufficient privileges.

- Adjust the queries according to your database version and the specific health metrics you are interested in.

- Always test scripts in a non-production environment first to ensure they work correctly and provide the desired information.

By running these scripts, you'll gather a wide variety of information about your Oracle database's health and configuration. After gathering this information, you'll be able to identify any areas that might need attention or further optimization.