database_maintenance_scheduler.sql
-- ------------------------------------------------------------------------------------- File Name : database_maintenance_scheduler.sql-- Author : Pierre Montbleau-- Description : Oracle database maintenance task scheduling and management utility-- Purpose : Schedule, monitor, and manage routine database maintenance operations-- Call Syntax : @F:\DBA\Scripts\database_maintenance_scheduler.sql (action) (task_type) (schedule_time)-- Parameters : action - Action to perform (SCHEDULE, LIST, RUN, CANCEL, STATUS)-- task_type - Type of maintenance task (STATS, BACKUP, CLEANUP, REORG, VALIDATE)-- schedule_time - Schedule time in 24h format (HH24:MI) or 'IMMEDIATE'-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_maintenance_scheduler_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Maintenance Scheduler...PROMPT ===========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Task Type: &2PROMPT Schedule Time: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_task_type VARCHAR2(20) := UPPER('&2'); v_schedule_time VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('SCHEDULE', 'LIST', 'RUN', 'CANCEL', 'STATUS') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be SCHEDULE, LIST, RUN, CANCEL, or STATUS'); END IF; IF v_action = 'SCHEDULE' AND v_task_type NOT IN ('STATS', 'BACKUP', 'CLEANUP', 'REORG', 'VALIDATE') THEN RAISE_APPLICATION_ERROR(-20002, 'Task type must be STATS, BACKUP, CLEANUP, REORG, or VALIDATE'); END IF; IF v_action = 'SCHEDULE' AND v_schedule_time != 'IMMEDIATE' THEN IF NOT REGEXP_LIKE(v_schedule_time, '^[0-9]{2}:[0-9]{2}$') THEN RAISE_APPLICATION_ERROR(-20003, 'Schedule time must be HH24:MI format or IMMEDIATE'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- MAINTENANCE TASK DEFINITIONS: Available maintenance operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. MAINTENANCE TASK DEFINITIONSPROMPT ================================
COLUMN task_type FORMAT A15COLUMN description FORMAT A60COLUMN recommended_frequency FORMAT A20COLUMN estimated_duration FORMAT A20
SELECT 'STATS' as task_type, 'Gather database statistics for optimizer' as description, 'DAILY' as recommended_frequency, '30-60 minutes' as estimated_durationFROM dualUNION ALLSELECT 'BACKUP', 'Perform database backup operations', 'DAILY', 'Varies by size'FROM dualUNION ALLSELECT 'CLEANUP', 'Clean up old data and purge recycle bin', 'WEEKLY', '15-30 minutes'FROM dualUNION ALLSELECT 'REORG', 'Reorganize tables and indexes', 'MONTHLY', '1-4 hours'FROM dualUNION ALLSELECT 'VALIDATE', 'Validate database objects and integrity', 'WEEKLY', '30-90 minutes'FROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- CURRENT MAINTENANCE SCHEDULE: Existing scheduled jobs-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. CURRENT MAINTENANCE SCHEDULEPROMPT ===============================
COLUMN job_name FORMAT A30COLUMN job_type FORMAT A20COLUMN enabled FORMAT A8COLUMN next_run FORMAT A20COLUMN last_run FORMAT A20COLUMN status FORMAT A15
SELECT job_name, job_type, enabled, TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') as next_run, TO_CHAR(last_start_date, 'YYYY-MM-DD HH24:MI:SS') as last_run, state as statusFROM dba_scheduler_jobsWHERE job_name LIKE 'MAINT%' OR job_name LIKE 'DBMS%' OR owner = 'SYS'ORDER BY 4;
-- ------------------------------------------------------------------------------------- STATISTICS GATHERING STATUS: Current statistics collection state-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. STATISTICS GATHERING STATUSPROMPT ===============================
COLUMN statistic FORMAT A40COLUMN value FORMAT A30COLUMN last_updated FORMAT A20
SELECT 'Last Stats Collection' as statistic, TO_CHAR(MAX(last_analyzed), 'YYYY-MM-DD HH24:MI:SS') as value, 'N/A' as last_updatedFROM dba_tablesWHERE last_analyzed IS NOT NULL AND owner NOT LIKE 'SYS%'UNION ALLSELECT 'Tables Needing Stats', TO_CHAR(COUNT(*)), 'N/A'FROM dba_tablesWHERE (last_analyzed IS NULL OR last_analyzed < SYSDATE - 7) AND owner NOT LIKE 'SYS%'UNION ALLSELECT 'Auto Stats Collection', STATUS, TO_CHAR(LAST_CHANGE, 'YYYY-MM-DD HH24:MI:SS')FROM dba_autotask_clientWHERE client_name = 'auto optimizer stats collection'ORDER BY 1;
-- ------------------------------------------------------------------------------------- MAINTENANCE OPERATION EXECUTION: Run maintenance tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MAINTENANCE OPERATION EXECUTIONPROMPT ===================================
BEGIN IF UPPER('&1') = 'RUN' THEN DECLARE v_task_type VARCHAR2(20) := UPPER('&2'); BEGIN CASE v_task_type WHEN 'STATS' THEN DBMS_OUTPUT.PUT_LINE('Starting statistics collection...'); DBMS_STATS.GATHER_DATABASE_STATS( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, cascade => TRUE ); DBMS_OUTPUT.PUT_LINE('Statistics collection completed'); WHEN 'CLEANUP' THEN DBMS_OUTPUT.PUT_LINE('Starting database cleanup...'); -- Purge recycle bin EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; DBMS_OUTPUT.PUT_LINE('Recycle bin purged'); -- Clean up old audit records (if auditing enabled) BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, use_last_arch_timestamp => TRUE ); DBMS_OUTPUT.PUT_LINE('Audit trail cleaned'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Audit cleanup skipped: ' || SQLERRM); END; WHEN 'VALIDATE' THEN DBMS_OUTPUT.PUT_LINE('Starting database validation...'); -- Validate key database objects FOR tab_rec IN ( SELECT owner, table_name FROM dba_tables WHERE owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N') AND ROWNUM <= 5 -- Limit for demo ) LOOP BEGIN EXECUTE IMMEDIATE 'ANALYZE TABLE ' || tab_rec.owner || '.' || tab_rec.table_name || ' VALIDATE STRUCTURE'; DBMS_OUTPUT.PUT_LINE('Validated: ' || tab_rec.owner || '.' || tab_rec.table_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation failed for ' || tab_rec.owner || '.' || tab_rec.table_name || ': ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Database validation completed'); ELSE DBMS_OUTPUT.PUT_LINE('Manual execution not supported for task type: ' || v_task_type); END CASE; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error executing maintenance task: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('No maintenance operations executed (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- SCHEDULE MAINTENANCE TASK: Create scheduled maintenance jobs-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. MAINTENANCE TASK SCHEDULINGPROMPT ===============================
BEGIN IF UPPER('&1') = 'SCHEDULE' THEN DECLARE v_task_type VARCHAR2(20) := UPPER('&2'); v_schedule_time VARCHAR2(20) := UPPER('&3'); v_job_name VARCHAR2(50); v_schedule VARCHAR2(100); BEGIN -- Generate job name and schedule v_job_name := 'MAINT_' || v_task_type || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'); IF v_schedule_time = 'IMMEDIATE' THEN v_schedule := 'SYSDATE'; DBMS_OUTPUT.PUT_LINE('Scheduling immediate execution...'); ELSE v_schedule := 'TRUNC(SYSDATE) + ' || (TO_NUMBER(SUBSTR(v_schedule_time, 1, 2)) / 24) + (TO_NUMBER(SUBSTR(v_schedule_time, 4, 2)) / 1440); DBMS_OUTPUT.PUT_LINE('Scheduling for: ' || v_schedule_time); END IF; -- Create scheduled job based on task type CASE v_task_type WHEN 'STATS' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;', start_date => TO_TIMESTAMP_TZ(v_schedule), enabled => TRUE, auto_drop => TRUE ); WHEN 'CLEANUP' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN EXECUTE IMMEDIATE ''PURGE RECYCLEBIN''; END;', start_date => TO_TIMESTAMP_TZ(v_schedule), enabled => TRUE, auto_drop => TRUE ); WHEN 'VALIDATE' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN FOR t IN (SELECT owner, table_name FROM dba_tables WHERE owner NOT LIKE ''SYS%'' AND ROWNUM <= 10) LOOP BEGIN EXECUTE IMMEDIATE ''ANALYZE TABLE '' || t.owner || ''.'' || t.table_name || '' VALIDATE STRUCTURE''; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; END;', start_date => TO_TIMESTAMP_TZ(v_schedule), enabled => TRUE, auto_drop => TRUE ); ELSE DBMS_OUTPUT.PUT_LINE('Scheduling not implemented for task type: ' || v_task_type); RETURN; END CASE; DBMS_OUTPUT.PUT_LINE('Maintenance job scheduled successfully: ' || v_job_name); DBMS_OUTPUT.PUT_LINE('Schedule: ' || v_schedule); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error scheduling maintenance task: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('No scheduling operations performed (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- MAINTENANCE JOB HISTORY: Recent maintenance execution history-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. MAINTENANCE JOB HISTORYPROMPT ===========================
COLUMN job_name FORMAT A30COLUMN log_date FORMAT A20COLUMN status FORMAT A15COLUMN run_duration FORMAT A15COLUMN additional_info FORMAT A50
SELECT job_name, TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') as log_date, status, TO_CHAR((actual_start_date - log_date) * 24 * 60 * 60, '999999') || ' sec' as queue_time, TO_CHAR((run_duration) * 24 * 60 * 60, '999999') || ' sec' as run_duration, error#, additional_infoFROM dba_scheduler_job_run_detailsWHERE (job_name LIKE 'MAINT%' OR job_name LIKE 'DBMS_STATS%') AND log_date > SYSDATE - 7ORDER BY log_date DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MAINTENANCE RECOMMENDATIONS: Suggested maintenance operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MAINTENANCE RECOMMENDATIONSPROMPT ===============================
COLUMN recommendation FORMAT A60COLUMN priority FORMAT A10COLUMN estimated_impact FORMAT A20COLUMN schedule_suggestion FORMAT A30
SELECT 'Gather statistics for stale tables' as recommendation, 'HIGH' as priority, 'High - Query performance' as estimated_impact, 'IMMEDIATE' as schedule_suggestionFROM dualWHERE EXISTS ( SELECT 1 FROM dba_tab_statistics WHERE stale_stats = 'YES' AND owner NOT LIKE 'SYS%')UNION ALLSELECT 'Reorganize fragmented indexes', 'MEDIUM', 'Medium - Storage/Performance', 'WEEKLY'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_indexes i JOIN dba_tables t ON i.table_owner = t.owner AND i.table_name = t.table_name WHERE i.owner NOT LIKE 'SYS%' AND t.partitioned = 'NO' -- Simple tables first AND i.index_type IN ('NORMAL', 'NORMAL/REV', 'BITMAP') -- Common index types AND i.status = 'VALID')UNION ALLSELECT 'Purge old recycle bin objects', 'LOW', 'Low - Storage recovery', 'DAILY'FROM dualWHERE (SELECT COUNT(*) FROM dba_recyclebin) > 100UNION ALLSELECT 'Validate critical table structures', 'MEDIUM', 'High - Data integrity', 'WEEKLY'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_tables WHERE last_analyzed IS NULL AND owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N'))ORDER BY 4;
-- ------------------------------------------------------------------------------------- AUTOMATED MAINTENANCE TASKS: Oracle automated maintenance configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. AUTOMATED MAINTENANCE TASKSPROMPT ===============================
COLUMN client_name FORMAT A40COLUMN status FORMAT A15COLUMN enabled FORMAT A8COLUMN last_run FORMAT A20COLUMN next_run FORMAT A20
SELECT job_name, job_type, state, enabled, TO_CHAR(last_start_date, 'YYYY-MM-DD HH24:MI:SS') as last_start, TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') as next_run, run_count, failure_countFROM dba_scheduler_jobsWHERE job_class LIKE 'AUTO_TASKS%' OR job_name LIKE 'ORA$AT%'ORDER BY job_name;
-- ------------------------------------------------------------------------------------- MAINTENANCE WINDOWS: Configured maintenance windows-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. MAINTENANCE WINDOW CONFIGURATIONPROMPT ====================================
COLUMN window_name FORMAT A30COLUMN enabled FORMAT A8COLUMN next_start FORMAT A20COLUMN duration FORMAT A20COLUMN active FORMAT A6
SELECT window_name, enabled, TO_CHAR(next_start_date, 'YYYY-MM-DD HH24:MI:SS') as next_start, TO_CHAR(duration, 'HH24:MI:SS') as duration, activeFROM dba_scheduler_windowsWHERE enabled = 'TRUE'ORDER BY 3;
-- ------------------------------------------------------------------------------------- COMPLETION: Maintenance scheduling summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ===========================================PROMPT DATABASE MAINTENANCE SCHEDULING COMPLETEDPROMPT ===========================================PROMPT Action: &1PROMPT Task Type: &2PROMPT Schedule Time: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review scheduled maintenance jobsPROMPT 2. Monitor job execution historyPROMPT 3. Adjust schedules based on system loadPROMPT 4. Validate maintenance task completionPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_maintenance_scheduler_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Maintenance Scheduler...PROMPT ===========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Task Type: &2PROMPT Schedule Time: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_task_type VARCHAR2(20) := UPPER('&2'); v_schedule_time VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('SCHEDULE', 'LIST', 'RUN', 'CANCEL', 'STATUS') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be SCHEDULE, LIST, RUN, CANCEL, or STATUS'); END IF; IF v_action = 'SCHEDULE' AND v_task_type NOT IN ('STATS', 'BACKUP', 'CLEANUP', 'REORG', 'VALIDATE') THEN RAISE_APPLICATION_ERROR(-20002, 'Task type must be STATS, BACKUP, CLEANUP, REORG, or VALIDATE'); END IF; IF v_action = 'SCHEDULE' AND v_schedule_time != 'IMMEDIATE' THEN IF NOT REGEXP_LIKE(v_schedule_time, '^[0-9]{2}:[0-9]{2}$') THEN RAISE_APPLICATION_ERROR(-20003, 'Schedule time must be HH24:MI format or IMMEDIATE'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- MAINTENANCE TASK DEFINITIONS: Available maintenance operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. MAINTENANCE TASK DEFINITIONSPROMPT ================================
COLUMN task_type FORMAT A15COLUMN description FORMAT A60COLUMN recommended_frequency FORMAT A20COLUMN estimated_duration FORMAT A20
SELECT 'STATS' as task_type, 'Gather database statistics for optimizer' as description, 'DAILY' as recommended_frequency, '30-60 minutes' as estimated_durationFROM dualUNION ALLSELECT 'BACKUP', 'Perform database backup operations', 'DAILY', 'Varies by size'FROM dualUNION ALLSELECT 'CLEANUP', 'Clean up old data and purge recycle bin', 'WEEKLY', '15-30 minutes'FROM dualUNION ALLSELECT 'REORG', 'Reorganize tables and indexes', 'MONTHLY', '1-4 hours'FROM dualUNION ALLSELECT 'VALIDATE', 'Validate database objects and integrity', 'WEEKLY', '30-90 minutes'FROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- CURRENT MAINTENANCE SCHEDULE: Existing scheduled jobs-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. CURRENT MAINTENANCE SCHEDULEPROMPT ===============================
COLUMN job_name FORMAT A30COLUMN job_type FORMAT A20COLUMN enabled FORMAT A8COLUMN next_run FORMAT A20COLUMN last_run FORMAT A20COLUMN status FORMAT A15
SELECT job_name, job_type, enabled, TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') as next_run, TO_CHAR(last_start_date, 'YYYY-MM-DD HH24:MI:SS') as last_run, state as statusFROM dba_scheduler_jobsWHERE job_name LIKE 'MAINT%' OR job_name LIKE 'DBMS%' OR owner = 'SYS'ORDER BY 4;
-- ------------------------------------------------------------------------------------- STATISTICS GATHERING STATUS: Current statistics collection state-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. STATISTICS GATHERING STATUSPROMPT ===============================
COLUMN statistic FORMAT A40COLUMN value FORMAT A30COLUMN last_updated FORMAT A20
SELECT 'Last Stats Collection' as statistic, TO_CHAR(MAX(last_analyzed), 'YYYY-MM-DD HH24:MI:SS') as value, 'N/A' as last_updatedFROM dba_tablesWHERE last_analyzed IS NOT NULL AND owner NOT LIKE 'SYS%'UNION ALLSELECT 'Tables Needing Stats', TO_CHAR(COUNT(*)), 'N/A'FROM dba_tablesWHERE (last_analyzed IS NULL OR last_analyzed < SYSDATE - 7) AND owner NOT LIKE 'SYS%'UNION ALLSELECT 'Auto Stats Collection', STATUS, TO_CHAR(LAST_CHANGE, 'YYYY-MM-DD HH24:MI:SS')FROM dba_autotask_clientWHERE client_name = 'auto optimizer stats collection'ORDER BY 1;
-- ------------------------------------------------------------------------------------- MAINTENANCE OPERATION EXECUTION: Run maintenance tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MAINTENANCE OPERATION EXECUTIONPROMPT ===================================
BEGIN IF UPPER('&1') = 'RUN' THEN DECLARE v_task_type VARCHAR2(20) := UPPER('&2'); BEGIN CASE v_task_type WHEN 'STATS' THEN DBMS_OUTPUT.PUT_LINE('Starting statistics collection...'); DBMS_STATS.GATHER_DATABASE_STATS( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, cascade => TRUE ); DBMS_OUTPUT.PUT_LINE('Statistics collection completed'); WHEN 'CLEANUP' THEN DBMS_OUTPUT.PUT_LINE('Starting database cleanup...'); -- Purge recycle bin EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; DBMS_OUTPUT.PUT_LINE('Recycle bin purged'); -- Clean up old audit records (if auditing enabled) BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, use_last_arch_timestamp => TRUE ); DBMS_OUTPUT.PUT_LINE('Audit trail cleaned'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Audit cleanup skipped: ' || SQLERRM); END; WHEN 'VALIDATE' THEN DBMS_OUTPUT.PUT_LINE('Starting database validation...'); -- Validate key database objects FOR tab_rec IN ( SELECT owner, table_name FROM dba_tables WHERE owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N') AND ROWNUM <= 5 -- Limit for demo ) LOOP BEGIN EXECUTE IMMEDIATE 'ANALYZE TABLE ' || tab_rec.owner || '.' || tab_rec.table_name || ' VALIDATE STRUCTURE'; DBMS_OUTPUT.PUT_LINE('Validated: ' || tab_rec.owner || '.' || tab_rec.table_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation failed for ' || tab_rec.owner || '.' || tab_rec.table_name || ': ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Database validation completed'); ELSE DBMS_OUTPUT.PUT_LINE('Manual execution not supported for task type: ' || v_task_type); END CASE; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error executing maintenance task: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('No maintenance operations executed (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- SCHEDULE MAINTENANCE TASK: Create scheduled maintenance jobs-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. MAINTENANCE TASK SCHEDULINGPROMPT ===============================
BEGIN IF UPPER('&1') = 'SCHEDULE' THEN DECLARE v_task_type VARCHAR2(20) := UPPER('&2'); v_schedule_time VARCHAR2(20) := UPPER('&3'); v_job_name VARCHAR2(50); v_schedule VARCHAR2(100); BEGIN -- Generate job name and schedule v_job_name := 'MAINT_' || v_task_type || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'); IF v_schedule_time = 'IMMEDIATE' THEN v_schedule := 'SYSDATE'; DBMS_OUTPUT.PUT_LINE('Scheduling immediate execution...'); ELSE v_schedule := 'TRUNC(SYSDATE) + ' || (TO_NUMBER(SUBSTR(v_schedule_time, 1, 2)) / 24) + (TO_NUMBER(SUBSTR(v_schedule_time, 4, 2)) / 1440); DBMS_OUTPUT.PUT_LINE('Scheduling for: ' || v_schedule_time); END IF; -- Create scheduled job based on task type CASE v_task_type WHEN 'STATS' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;', start_date => TO_TIMESTAMP_TZ(v_schedule), enabled => TRUE, auto_drop => TRUE ); WHEN 'CLEANUP' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN EXECUTE IMMEDIATE ''PURGE RECYCLEBIN''; END;', start_date => TO_TIMESTAMP_TZ(v_schedule), enabled => TRUE, auto_drop => TRUE ); WHEN 'VALIDATE' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN FOR t IN (SELECT owner, table_name FROM dba_tables WHERE owner NOT LIKE ''SYS%'' AND ROWNUM <= 10) LOOP BEGIN EXECUTE IMMEDIATE ''ANALYZE TABLE '' || t.owner || ''.'' || t.table_name || '' VALIDATE STRUCTURE''; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; END;', start_date => TO_TIMESTAMP_TZ(v_schedule), enabled => TRUE, auto_drop => TRUE ); ELSE DBMS_OUTPUT.PUT_LINE('Scheduling not implemented for task type: ' || v_task_type); RETURN; END CASE; DBMS_OUTPUT.PUT_LINE('Maintenance job scheduled successfully: ' || v_job_name); DBMS_OUTPUT.PUT_LINE('Schedule: ' || v_schedule); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error scheduling maintenance task: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('No scheduling operations performed (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- MAINTENANCE JOB HISTORY: Recent maintenance execution history-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. MAINTENANCE JOB HISTORYPROMPT ===========================
COLUMN job_name FORMAT A30COLUMN log_date FORMAT A20COLUMN status FORMAT A15COLUMN run_duration FORMAT A15COLUMN additional_info FORMAT A50
SELECT job_name, TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') as log_date, status, TO_CHAR((actual_start_date - log_date) * 24 * 60 * 60, '999999') || ' sec' as queue_time, TO_CHAR((run_duration) * 24 * 60 * 60, '999999') || ' sec' as run_duration, error#, additional_infoFROM dba_scheduler_job_run_detailsWHERE (job_name LIKE 'MAINT%' OR job_name LIKE 'DBMS_STATS%') AND log_date > SYSDATE - 7ORDER BY log_date DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MAINTENANCE RECOMMENDATIONS: Suggested maintenance operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MAINTENANCE RECOMMENDATIONSPROMPT ===============================
COLUMN recommendation FORMAT A60COLUMN priority FORMAT A10COLUMN estimated_impact FORMAT A20COLUMN schedule_suggestion FORMAT A30
SELECT 'Gather statistics for stale tables' as recommendation, 'HIGH' as priority, 'High - Query performance' as estimated_impact, 'IMMEDIATE' as schedule_suggestionFROM dualWHERE EXISTS ( SELECT 1 FROM dba_tab_statistics WHERE stale_stats = 'YES' AND owner NOT LIKE 'SYS%')UNION ALLSELECT 'Reorganize fragmented indexes', 'MEDIUM', 'Medium - Storage/Performance', 'WEEKLY'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_indexes i JOIN dba_tables t ON i.table_owner = t.owner AND i.table_name = t.table_name WHERE i.owner NOT LIKE 'SYS%' AND t.partitioned = 'NO' -- Simple tables first AND i.index_type IN ('NORMAL', 'NORMAL/REV', 'BITMAP') -- Common index types AND i.status = 'VALID')UNION ALLSELECT 'Purge old recycle bin objects', 'LOW', 'Low - Storage recovery', 'DAILY'FROM dualWHERE (SELECT COUNT(*) FROM dba_recyclebin) > 100UNION ALLSELECT 'Validate critical table structures', 'MEDIUM', 'High - Data integrity', 'WEEKLY'FROM dualWHERE EXISTS ( SELECT 1 FROM dba_tables WHERE last_analyzed IS NULL AND owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N'))ORDER BY 4;
-- ------------------------------------------------------------------------------------- AUTOMATED MAINTENANCE TASKS: Oracle automated maintenance configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. AUTOMATED MAINTENANCE TASKSPROMPT ===============================
COLUMN client_name FORMAT A40COLUMN status FORMAT A15COLUMN enabled FORMAT A8COLUMN last_run FORMAT A20COLUMN next_run FORMAT A20
SELECT job_name, job_type, state, enabled, TO_CHAR(last_start_date, 'YYYY-MM-DD HH24:MI:SS') as last_start, TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') as next_run, run_count, failure_countFROM dba_scheduler_jobsWHERE job_class LIKE 'AUTO_TASKS%' OR job_name LIKE 'ORA$AT%'ORDER BY job_name;
-- ------------------------------------------------------------------------------------- MAINTENANCE WINDOWS: Configured maintenance windows-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. MAINTENANCE WINDOW CONFIGURATIONPROMPT ====================================
COLUMN window_name FORMAT A30COLUMN enabled FORMAT A8COLUMN next_start FORMAT A20COLUMN duration FORMAT A20COLUMN active FORMAT A6
SELECT window_name, enabled, TO_CHAR(next_start_date, 'YYYY-MM-DD HH24:MI:SS') as next_start, TO_CHAR(duration, 'HH24:MI:SS') as duration, activeFROM dba_scheduler_windowsWHERE enabled = 'TRUE'ORDER BY 3;
-- ------------------------------------------------------------------------------------- COMPLETION: Maintenance scheduling summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ===========================================PROMPT DATABASE MAINTENANCE SCHEDULING COMPLETEDPROMPT ===========================================PROMPT Action: &1PROMPT Task Type: &2PROMPT Schedule Time: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review scheduled maintenance jobsPROMPT 2. Monitor job execution historyPROMPT 3. Adjust schedules based on system loadPROMPT 4. Validate maintenance task completionPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------