Metadata Backup

 That's an important clarification. You want to execute the backup and then stop, leaving the metadata dump file safely in your S3 bucket without immediately importing it into a new database.

In that case, you only need the first four steps of the previous comprehensive script, but with a slight modification to ensure the local files are cleaned up properly after being uploaded to S3.

Here is the refined, comprehensive script designed specifically for backup and storage only. This script is a self-contained unit that you can run on your old Oracle RDS instance.

Important Note: You must replace the placeholder your-s3-bucket with your actual bucket name.

---

backup_metadata_and_store_to_s3.sql

SQL

SET SERVEROUTPUT ON;

-- -----------------------------------------------------------------------------
-- This script performs a metadata-only export of an Oracle RDS database,
-- uploads the resulting dump file and log file to an S3 bucket, and
-- cleans up the local files on the RDS instance.
--
-- This script does NOT perform an import. It is for backup and storage purposes only.
--
-- Prerequisites:
-- 1. An IAM Role with S3 permissions attached to your RDS instance.
-- 2. An S3 bucket in the same region as the RDS instance.
-- 3. You must be connected as the master user or a user with
--    appropriate privileges to use the rdsadmin and UTL_FILE packages.
-- -----------------------------------------------------------------------------

DECLARE
  -- Variables for file and job management
  l_file_handle       UTL_FILE.FILE_TYPE;
  l_dp_job_handle     NUMBER;
  l_job_state         VARCHAR2(200);
  l_dumpfile_name     VARCHAR2(200);
  l_log_file_name     VARCHAR2(200);
  l_task_id_dump      VARCHAR2(100);
  l_task_id_log       VARCHAR2(100);

  -- Customizable parameters
  c_s3_bucket_name    CONSTANT VARCHAR2(100) := 'your-s3-bucket';
  c_s3_prefix         CONSTANT VARCHAR2(100) := 'oracle/metadata_backups/';

BEGIN
  -- ---------------------------------------------------------------------
  -- Step 1: Create a Data Pump Parameter File
  -- ---------------------------------------------------------------------
  DBMS_OUTPUT.PUT_LINE('Creating Data Pump parameter file...');

  -- Use a dynamic filename with a timestamp to ensure uniqueness
  l_dumpfile_name := 'metadata_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.dmp';
  l_log_file_name := 'metadata_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.log';

  l_file_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'metadata_export.par', 'w');
  UTL_FILE.PUT_LINE(l_file_handle, 'CONTENT=METADATA_ONLY');
  UTL_FILE.PUT_LINE(l_file_handle, 'FULL=Y');
  UTL_FILE.PUT_LINE(l_file_handle, 'EXCLUDE=STATISTICS');
  UTL_FILE.PUT_LINE(l_file_handle, 'DUMPFILE=' || l_dumpfile_name);
  UTL_FILE.PUT_LINE(l_file_handle, 'LOGFILE=' || l_log_file_name);
  UTL_FILE.PUT_LINE(l_file_handle, 'PARALLEL=4');
  
  -- Exclude system schemas and other unwanted objects to prevent errors
  UTL_FILE.PUT_LINE(l_file_handle, 'EXCLUDE=SCHEMA:"IN(''APEX_040200'',''APEX_050000'',''APEX_050100'',''APEX_050200'',''APEX_050300'',''APEX_050400'',''APEX_180100'',''APEX_180200'',''APEX_190100'',''APEX_190200'',''APEX_200100'',''APEX_200200'',''APEX_210100'',''APEX_210200'',''APEX_220100'',''APEX_220200'',''APPQOSSYS'',''AUDSYS'',''CTXSYS'',''DBSNMP'',''DIP'',''DVSYS'',''GGSYS'',''GSMADMIN_INTERNAL'',''MDADM'',''MDSYS'',''OJVMSYS'',''ORACLE_OCM'',''ORDDATA'',''ORDPLUGINS'',''ORDSYS'',''OUTLN'',''PDBADMIN'',''SYS'',''SYSTEM'',''WMSYS'',''XDB'',''XS$NULL'') "');
  
  UTL_FILE.FCLOSE(l_file_handle);
  DBMS_OUTPUT.PUT_LINE('Parameter file `metadata_export.par` created.');
  
  -- ---------------------------------------------------------------------
  -- Step 2: Run the Data Pump Export Job
  -- ---------------------------------------------------------------------
  DBMS_OUTPUT.PUT_LINE('Starting Data Pump export job...');
  l_dp_job_handle := DBMS_DATAPUMP.OPEN(
    operation => 'EXPORT',
    job_mode => 'FULL',
    job_name => 'METADATA_EXPORT_JOB',
    version => 'LATEST'
  );
  
  DBMS_DATAPUMP.ADD_FILE(
    handle => l_dp_job_handle,
    filename => 'metadata_export.par',
    directory => 'DATA_PUMP_DIR',
    filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_PARAMETER_FILE
  );

  DBMS_DATAPUMP.START_JOB(l_dp_job_handle);
  DBMS_DATAPUMP.DETACH(l_dp_job_handle);

  DBMS_OUTPUT.PUT_LINE('Data Pump export job started. Monitoring status...');
  
  -- ---------------------------------------------------------------------
  -- Step 3: Wait for the Export to Complete (Polling)
  -- ---------------------------------------------------------------------
  LOOP
    BEGIN
      SELECT state INTO l_job_state
      FROM dba_datapump_jobs
      WHERE job_name = 'METADATA_EXPORT_JOB';
      
      IF l_job_state = 'COMPLETING' OR l_job_state = 'COMPLETED' THEN
        EXIT;
      END IF;
      
      DBMS_OUTPUT.PUT_LINE('Job state: ' || l_job_state || '... waiting 30 seconds.');
      DBMS_LOCK.SLEEP(30);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Data Pump job completed or detached.');
        EXIT;
    END;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Data Pump export job finished.');
  
  -- ---------------------------------------------------------------------
  -- Step 4: Upload the Backup Files to S3
  -- ---------------------------------------------------------------------
  DBMS_OUTPUT.PUT_LINE('Uploading backup files to S3 bucket: ' || c_s3_bucket_name);

  -- Upload the dump file
  SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
    p_bucket_name => c_s3_bucket_name,
    p_directory_name => 'DATA_PUMP_DIR',
    p_s3_prefix => c_s3_prefix,
    p_file_name => l_dumpfile_name
  ) INTO l_task_id_dump FROM DUAL;
  
  DBMS_OUTPUT.PUT_LINE('Dump file upload task ID: ' || l_task_id_dump);

  -- Upload the log file
  SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
    p_bucket_name => c_s3_bucket_name,
    p_directory_name => 'DATA_PUMP_DIR',
    p_s3_prefix => c_s3_prefix,
    p_file_name => l_log_file_name
  ) INTO l_task_id_log FROM DUAL;
  
  DBMS_OUTPUT.PUT_LINE('Log file upload task ID: ' || l_task_id_log);

  -- ---------------------------------------------------------------------
  -- Step 5: Clean Up Local Files
  -- ---------------------------------------------------------------------
  DBMS_OUTPUT.PUT_LINE('Cleaning up local files on RDS instance...');
  
  -- It's crucial to delete the dump and log files to free up RDS storage.
  EXECUTE IMMEDIATE 'BEGIN UTL_FILE.FREMOVE(''DATA_PUMP_DIR'', ''' || 'metadata_export.par' || '''); END;';
  EXECUTE IMMEDIATE 'BEGIN UTL_FILE.FREMOVE(''DATA_PUMP_DIR'', ''' || l_dumpfile_name || '''); END;';
  EXECUTE IMMEDIATE 'BEGIN UTL_FILE.FREMOVE(''DATA_PUMP_DIR'', ''' || l_log_file_name || '''); END;';

  DBMS_OUTPUT.PUT_LINE('All backup files and parameter file purged from DATA_PUMP_DIR.');
  DBMS_OUTPUT.PUT_LINE('Backup process completed successfully!');

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Please check the log file for details.');
    RAISE;
END;
/

Parallel Queries:

/* AWR Query: Fetch SQL queries with parallel hint from DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT */

SELECT DISTINCT

hst.sql_id,

hst.sql_text,

hss.executions_total AS executions,

hss.elapsed_time_total / 1e6 AS elapsed_time_secs,

hss.disk_reads_total AS disk_reads,

hss.buffer_gets_total AS buffer_gets,

hss.cpu_time_total / 1e6 AS cpu_time_secs,

hsn.begin_interval_time,

hsn.end_interval_time

FROM

dba_hist_sqltext hst

JOIN

dba_hist_sqlstat hss

ON hst.sql_id = hss.sql_id

JOIN

dba_hist_snapshot hsn

ON hss.snap_id = hsn.snap_id

AND hss.instance_number = hsn.instance_number

WHERE

UPPER(hst.sql_text) LIKE '%/*+ PARALLEL%'

AND hsn.begin_interval_time >= SYSDATE - 7 -- Last 7 days, adjust as needed

ORDER BY

hsn.begin_interval_time DESC;

/* ASH Query (Historical): Fetch SQL queries with parallel hint from DBA_HIST_ACTIVE_SESS_HISTORY */

/* ASH Query (Historical): Fetch SQL queries with parallel hint from DBA_HIST_ACTIVE_SESS_HISTORY, including username */

SELECT DISTINCT

ash.sql_id,

(SELECT sql_text

FROM dba_hist_sqltext hst

WHERE hst.sql_id = ash.sql_id

AND ROWNUM = 1) AS sql_text,

(SELECT username

FROM dba_users u

WHERE u.user_id = ash.user_id

AND ROWNUM = 1) AS username,

COUNT(DISTINCT ash.session_id) AS parallel_sessions,

MIN(ash.sample_time) AS first_seen,

MAX(ash.sample_time) AS last_seen

FROM

dba_hist_active_sess_history ash

WHERE

(ash.event LIKE 'PX%' OR ash.event LIKE 'parallel%')

AND ash.session_type = 'FOREGROUND'

AND ash.program LIKE '%(P%)' -- Parallel slave processes, e.g., P000, P001

AND ash.sample_time >= SYSDATE - 1 -- Last 1 day, adjust as needed

AND EXISTS (

SELECT 1

FROM dba_hist_sqltext hst

WHERE hst.sql_id = ash.sql_id

AND UPPER(hst.sql_text) LIKE '%/*+ PARALLEL%'

)

GROUP BY

ash.sql_id,

ash.user_id

ORDER BY

last_seen DESC;

/* ASH Query (Real-Time): Fetch SQL queries with parallel hint from V$ACTIVE_SESSION_HISTORY */

SELECT DISTINCT

ash.sql_id,

(SELECT sql_text

FROM v$sqltext hst

WHERE hst.sql_id = ash.sql_id

AND hst.piece = 0

AND ROWNUM = 1) AS sql_text,

COUNT(DISTINCT ash.session_id) AS parallel_sessions,

MIN(ash.sample_time) AS first_seen,

MAX(ash.sample_time) AS last_seen

FROM

v$active_session_history ash

WHERE

(ash.event LIKE 'PX%' OR ash.event LIKE 'parallel%')

AND ash.session_type = 'FOREGROUND'

AND ash.program LIKE '%(P%)' -- Parallel slave processes, e.g., P000, P001

AND ash.sample_time >= SYSDATE - 1 -- Last 1 day, adjust as needed

AND EXISTS (

SELECT 1

FROM v$sqltext hst

WHERE hst.sql_id = ash.sql_id

AND UPPER(hst.sql_text) LIKE '%/*+ PARALLEL%'

)

GROUP BY

ash.sql_id

ORDER BY

last_seen DESC;

PLAN

 SELECT ash.session_id, ash.session_serial#, ash.user_id, ash.program, ash.machine, 

       ash.sql_id, sql.sql_text, ash.event

FROM v$active_session_history ash

LEFT JOIN v$sql sql ON ash.sql_id = sql.sql_id

WHERE UPPER(sql.sql_text) LIKE '%%'

   OR UPPER(sql.sql_text) LIKE '%%'

  AND ash.sample_time BETWEEN SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP

ORDER BY ash.sample_time DESC;

AWR Plan change

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

    -- Define a collection for SQL IDs

    TYPE t_sql_id_array IS TABLE OF VARCHAR2(23);

    v_sql_ids t_sql_id_array := t_sql_id_array(

        'g2969b8287x4',

        '281v89047b7n1',

        'b507v024r3g6a',

        'another_sql_id' -- Replace with your full list of up to 50 valid SQL IDs

    );

    v_sql_id VARCHAR2(23);

    v_found NUMBER;

    v_prev_plan_hash NUMBER;

    v_plan_change VARCHAR2(30); -- Increased size for safety

    -- Variables to track last plan change

    v_last_change_time DATE;

    v_last_old_plan_hash NUMBER;

    v_last_new_plan_hash NUMBER;

    v_last_old_elapsed NUMBER;

    v_last_new_elapsed NUMBER;

    v_plan_changed BOOLEAN;

BEGIN

    -- Enable DBMS_OUTPUT with unlimited buffer

    DBMS_OUTPUT.ENABLE(NULL);

    DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT buffer set to unlimited.');

    -- Loop through each SQL ID

    FOR i IN 1..v_sql_ids.COUNT LOOP

        v_sql_id := v_sql_ids(i);

        BEGIN

            -- Validate SQL ID exists in AWR

            SELECT COUNT(*)

            INTO v_found

            FROM dba_hist_sqlstat

            WHERE sql_id = v_sql_id

            AND ROWNUM = 1;

            

            IF v_found = 0 THEN

                DBMS_OUTPUT.PUT_LINE('Warning: No AWR data found for SQL_ID ' || v_sql_id);

                CONTINUE;

            END IF;

            

            -- Initialize plan change tracking

            v_prev_plan_hash := NULL;

            v_last_change_time := NULL;

            v_last_old_plan_hash := NULL;

            v_last_new_plan_hash := NULL;

            v_last_old_elapsed := NULL;

            v_last_new_elapsed := NULL;

            v_plan_changed := FALSE;

            

            -- Print header for detailed output

            DBMS_OUTPUT.PUT_LINE(CHR(10));

            DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

            DBMS_OUTPUT.PUT_LINE('-- AWR History for SQL_ID: ' || v_sql_id);

            DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

            DBMS_OUTPUT.PUT_LINE(CHR(10));

            DBMS_OUTPUT.PUT(RPAD('Snapshot Time', 22) || ' | ');

            DBMS_OUTPUT.PUT(RPAD('Plan Hash', 12) || ' | ');

            DBMS_OUTPUT.PUT(RPAD('Avg Elapsed (s)', 15) || ' | ');

            DBMS_OUTPUT.PUT(RPAD('Executions', 10) || ' | ');

            DBMS_OUTPUT.PUT_LINE('Plan Change');

            DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));

            -- Query AWR data for the SQL ID

            FOR rec IN (

                SELECT

                    s.begin_interval_time AS begin_time,

                    st.plan_hash_value,

                    ROUND(st.elapsed_time_delta / DECODE(st.executions_delta, 0, 1, st.executions_delta) / 1000000, 3) AS avg_elapsed_sec,

                    st.executions_delta AS executions

                FROM

                    dba_hist_sqlstat st

                JOIN

                    dba_hist_snapshot s

                ON

                    st.snap_id = s.snap_id

                WHERE

                    st.sql_id = v_sql_id

                AND

                    st.dbid = (SELECT dbid FROM v$database)

                AND

                    s.begin_interval_time > SYSDATE - 7 -- Limit to last 7 days

                ORDER BY

                    s.begin_interval_time

            ) LOOP

                -- Determine if plan hash changed

                IF v_prev_plan_hash IS NULL THEN

                    v_plan_change := 'Initial';

                ELSIF v_prev_plan_hash != rec.plan_hash_value THEN

                    v_plan_change := 'Changed';

                    -- Update last plan change details

                    v_last_change_time := rec.begin_time;

                    v_last_old_plan_hash := v_prev_plan_hash;

                    v_last_new_plan_hash := rec.plan_hash_value;

                    v_last_old_elapsed := v_last_new_elapsed; -- Previous row's elapsed time

                    v_last_new_elapsed := rec.avg_elapsed_sec;

                    v_plan_changed := TRUE;

                ELSE

                    v_plan_change := 'Same';

                END IF;

                

                -- Print row with plan change indicator (split to avoid buffer issues)

                DBMS_OUTPUT.PUT(RPAD(TO_CHAR(rec.begin_time, 'YYYY-MM-DD HH24:MI:SS'), 22) || ' | ');

                DBMS_OUTPUT.PUT(RPAD(rec.plan_hash_value, 12) || ' | ');

                DBMS_OUTPUT.PUT(RPAD(TO_CHAR(rec.avg_elapsed_sec, '99990.000'), 15) || ' | ');

                DBMS_OUTPUT.PUT(RPAD(rec.executions, 10) || ' | ');

                DBMS_OUTPUT.PUT_LINE(v_plan_change);

                

                -- Update previous plan hash and elapsed time

                v_prev_plan_hash := rec.plan_hash_value;

                v_last_new_elapsed := rec.avg_elapsed_sec; -- Store for next iteration

            END LOOP;

            

            -- Print summary of last plan change

            DBMS_OUTPUT.PUT_LINE(CHR(10));

            DBMS_OUTPUT.PUT_LINE('--- Last Plan Change Summary for SQL_ID: ' || v_sql_id || ' ---');

            IF v_plan_changed THEN

                DBMS_OUTPUT.PUT('Last Change Time: ' || TO_CHAR(v_last_change_time, 'YYYY-MM-DD HH24:MI:SS') || ' | ');

                DBMS_OUTPUT.PUT('Old Plan Hash: ' || v_last_old_plan_hash || ' | ');

                DBMS_OUTPUT.PUT('Old Avg Elapsed: ' || TO_CHAR(v_last_old_elapsed, '99990.000') || 's | ');

                DBMS_OUTPUT.PUT('New Plan Hash: ' || v_last_new_plan_hash || ' | ');

                DBMS_OUTPUT.PUT_LINE('New Avg Elapsed: ' || TO_CHAR(v_last_new_elapsed, '99990.000') || 's');

            ELSE

                DBMS_OUTPUT.PUT_LINE('No plan change detected in the last 7 days.');

            END IF;

            DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));

            

            IF SQL%NOTFOUND THEN

                DBMS_OUTPUT.PUT_LINE('No data found for SQL_ID ' || v_sql_id || ' in the last 7 days.');

            END IF;

            

        EXCEPTION

            WHEN OTHERS THEN

                DBMS_OUTPUT.PUT_LINE('Error processing SQL_ID ' || v_sql_id || ': ' || SQLERRM);

                CONTINUE;

        END;

    END LOOP;

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);

END;

/

==

For single query:

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE -- Single SQL ID v_sql_id VARCHAR2(13) := 'g2969b8287x4'; -- Replace with your SQL ID v_found NUMBER; v_prev_plan_hash NUMBER; v_last_change_time DATE; v_last_old_plan_hash NUMBER; v_last_new_plan_hash NUMBER; v_last_old_elapsed NUMBER; v_last_new_elapsed NUMBER; v_plan_changed BOOLEAN; BEGIN -- Enable DBMS_OUTPUT with unlimited buffer DBMS_OUTPUT.ENABLE(NULL); DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT buffer set to unlimited.');

BEGIN
    -- Validate SQL ID exists in AWR
    SELECT COUNT(*)
    INTO v_found
    FROM dba_hist_sqlstat
    WHERE sql_id = v_sql_id
    AND ROWNUM = 1;
    
    IF v_found = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Error: No AWR data found for SQL_ID ' || v_sql_id);
        RETURN;
    END IF;
    
    -- Initialize plan change tracking
    v_prev_plan_hash := NULL;
    v_last_change_time := NULL;
    v_last_old_plan_hash := NULL;
    v_last_new_plan_hash := NULL;
    v_last_old_elapsed := NULL;
    v_last_new_elapsed := NULL;
    v_plan_changed := FALSE;
    
    -- Query AWR data for the SQL ID
    FOR rec IN (
        SELECT
            s.begin_interval_time AS begin_time,
            st.plan_hash_value,
            ROUND(st.elapsed_time_delta / DECODE(st.executions_delta, 0, 1, st.executions_delta) / 1000000, 3) AS avg_elapsed_sec,
            st.executions_delta AS executions
        FROM
            dba_hist_sqlstat st
        JOIN
            dba_hist_snapshot s
        ON
            st.snap_id = s.snap_id
        WHERE
            st.sql_id = v_sql_id
        AND
            st.dbid = (SELECT dbid FROM v$database)
        AND
            s.begin_interval_time > SYSDATE - 7 -- Limit to last 7 days
        ORDER BY
            s.begin_interval_time
    ) LOOP
        -- Check for plan change
        IF v_prev_plan_hash IS NOT NULL AND v_prev_plan_hash != rec.plan_hash_value THEN
            -- Update last plan change details
            v_last_change_time := rec.begin_time;
            v_last_old_plan_hash := v_prev_plan_hash;
            v_last_new_plan_hash := rec.plan_hash_value;
            v_last_old_elapsed := v_last_new_elapsed; -- Previous row's elapsed time
            v_last_new_elapsed := rec.avg_elapsed_sec;
            v_plan_changed := TRUE;
        END IF;
        
        -- Update previous plan hash and elapsed time
        v_prev_plan_hash := rec.plan_hash_value;
        v_last_new_elapsed := rec.avg_elapsed_sec; -- Store for next iteration
    END LOOP;
    
    -- Print last plan change summary
    DBMS_OUTPUT.PUT_LINE(CHR(10));
    DBMS_OUTPUT.PUT_LINE('--- Last Plan Change Summary for SQL_ID: ' || v_sql_id || ' ---');
    IF v_plan_changed THEN
        DBMS_OUTPUT.PUT('Last Change Time: ' || TO_CHAR(v_last_change_time, 'YYYY-MM-DD HH24:MI:SS') || ' | ');
        DBMS_OUTPUT.PUT('Old Plan Hash: ' || v_last_old_plan_hash || ' | ');
        DBMS_OUTPUT.PUT('Old Avg Elapsed: ' || TO_CHAR(v_last_old_elapsed, '99990.000') || 's | ');
        DBMS_OUTPUT.PUT('New Plan Hash: ' || v_last_new_plan_hash || ' | ');
        DBMS_OUTPUT.PUT_LINE('New Avg Elapsed: ' || TO_CHAR(v_last_new_elapsed, '99990.000') || 's');
    ELSIF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found for SQL_ID ' || v_sql_id || ' in the last 7 days.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No plan change detected in the last 7 days.');
    END IF;
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error processing SQL_ID ' || v_sql_id || ': ' || SQLERRM);
END;

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM); END; /

Session Kill

 

/********************************************************************

   DYNAMIC SAFE KILLER - SNIPER & PURGE MODES

   → Includes protections for SYS/RDSADMIN

   → Auto-detects Scheduler Jobs vs User Sessions

********************************************************************/

SET SERVEROUTPUT ON

DECLARE

    -- =================================================================

    -- CONFIGURATION: CHOOSE ONE METHOD

    -- =================================================================

    -- METHOD A: Specific Session (Sniper Mode)

    v_sid         NUMBER        := &sid;          -- e.g., 123 (Or NULL if using Method B)

    v_serial      NUMBER        := &serial;       -- e.g., 4567 (Or NULL if using Method B)

    -- METHOD B: All Sessions for User (Purge Mode)

    -- Enter username in UPPERCASE. Leave NULL if using Method A.

    v_target_user VARCHAR2(128) := '&username';   -- e.g., 'SCOTT' or NULL

    -- =================================================================

    -- Internal Variables

    CURSOR c_sessions IS

        SELECT sid, serial#, username, type, program, status

        FROM v$session

        WHERE (v_target_user IS NOT NULL AND username = v_target_user)

           OR (v_target_user IS NULL AND sid = v_sid AND serial# = v_serial);

    v_killed_cnt  NUMBER := 0;

    -- Procedure to perform the actual kill logic

    PROCEDURE kill_session(p_sid NUMBER, p_serial NUMBER, p_program VARCHAR2, p_status VARCHAR2) IS

        l_job_name VARCHAR2(128);

        l_check    NUMBER;

    BEGIN

        DBMS_OUTPUT.PUT_LINE('   Targeting SID: ' || p_sid || ' | Serial: ' || p_serial);

        -- 1. SCHEDULER JOB CHECK

        IF p_program LIKE '%(J%' THEN

            BEGIN

                SELECT job_name INTO l_job_name FROM dba_scheduler_running_jobs WHERE session_id = p_sid;

                DBMS_OUTPUT.PUT_LINE('Detected Job: ' || l_job_name);

                DBMS_SCHEDULER.STOP_JOB(l_job_name, force => TRUE);

                DBMS_OUTPUT.PUT_LINE('STOP_JOB executed.');

                RETURN; 

            EXCEPTION WHEN OTHERS THEN NULL; 

            END;

        END IF;

        -- 2. ALREADY DEAD CHECK

        IF p_status = 'KILLED' THEN

            DBMS_OUTPUT.PUT_LINE('Already KILLED. Moving to NUCLEAR PROCESS kill.');

            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');

            DBMS_OUTPUT.PUT_LINE('OS Process terminated.');

            RETURN;

        END IF;

        -- 3. STANDARD KILL

        BEGIN

            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'IMMEDIATE');

            DBMS_OUTPUT.PUT_LINE('KILL IMMEDIATE sent.');

            

            -- Quick check if it worked

            SELECT COUNT(*) INTO l_check FROM v$session WHERE sid = p_sid AND serial# = p_serial;

            IF l_check > 0 THEN

                 rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');

                 DBMS_OUTPUT.PUT_LINE('Session stubborn -> OS Process terminated.');

            END IF;

        EXCEPTION WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

        END;

    END;

BEGIN

    -- [NEW] 1. VISIBILITY: Tell the user exactly what mode we are in

    DBMS_OUTPUT.PUT_LINE('=== OPERATION START ===');

    DBMS_OUTPUT.PUT_LINE('Mode: ' || CASE WHEN v_target_user IS NOT NULL 

                                     THEN 'PURGE USER [' || v_target_user || ']'

                                     ELSE 'SNIPER [SID=' || v_sid || ']' END);

    -- SAFETY CHECK 1: Ensure input is valid

    IF v_sid IS NULL AND v_target_user IS NULL THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: You must provide either a SID/SERIAL or a USERNAME.');

        RETURN;

    END IF;

    -- SAFETY CHECK 2: Prevent killing yourself

    IF v_target_user = USER THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: You cannot purge your own username ('||USER||') while connected.');

        RETURN;

    END IF;

    -- [NEW] SAFETY CHECK 3: Block mass-killing of privileged accounts

    IF UPPER(v_target_user) IN ('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB') THEN

        DBMS_OUTPUT.PUT_LINE('BLOCKED: Cannot mass-kill privileged/system accounts ('||v_target_user||').');

        DBMS_OUTPUT.PUT_LINE('   If you must kill a session here, use Sniper Mode (SID/Serial) individually.');

        RETURN;

    END IF;

    -- EXECUTION LOOP

    FOR r IN c_sessions LOOP

        -- SAFETY CHECK 4: Background Processes are untouchable

        IF r.type = 'BACKGROUND' THEN

            DBMS_OUTPUT.PUT_LINE('SKIPPING SID ' || r.sid || ' (Background Process).');

            CONTINUE;

        END IF;

        

        -- SAFETY CHECK 5: Don't kill the current session

        IF r.sid = SYS_CONTEXT('USERENV', 'SID') THEN

             CONTINUE;

        END IF;

        -- EXECUTE

        kill_session(r.sid, r.serial#, r.program, r.status);

        v_killed_cnt := v_killed_cnt + 1;

    END LOOP;

    IF v_killed_cnt = 0 THEN

        DBMS_OUTPUT.PUT_LINE('No matching sessions found to kill.');

    ELSE

        DBMS_OUTPUT.PUT_LINE('=== COMPLETED. Killed ' || v_killed_cnt || ' session(s). ===');

    END IF;

END;

/

==================================

Step 1: Create the Package Specification

This defines the "Public Interface"—the two commands your team is allowed to use.

SQL

CREATE OR REPLACE PACKAGE admin_kill_tools AS
    /****************************************************************
    * ADMIN_KILL_TOOLS
    * ----------------
    * Centralized session management for Oracle RDS.
    * Includes safety rails for background processes and system users.
    ****************************************************************/

    -- Sniper Mode: Kill a specific SID/Serial
    PROCEDURE kill_session(
        p_sid    IN NUMBER,
        p_serial IN NUMBER
    );

    -- Purge Mode: Kill ALL sessions for a specific user
    PROCEDURE purge_user(
        p_username IN VARCHAR2
    );

END admin_kill_tools;
/

---

Step 2: Create the Package Body

This contains the "Brain." It hides the complexity of checking for scheduler jobs, verifying background processes, and performing the kill.

SQL

CREATE OR REPLACE PACKAGE BODY admin_kill_tools AS

    -- PRIVATE HELPER: The actual logic that performs the kill
    -- (Not accessible directly by users)
    PROCEDURE do_kill_logic(p_sid NUMBER, p_serial NUMBER, p_program VARCHAR2, p_status VARCHAR2) IS
        l_job_name VARCHAR2(128);
        l_check    NUMBER;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('   ..Targeting SID: ' || p_sid || ' | Serial: ' || p_serial);

        -- 1. SCHEDULER JOB CHECK
        IF p_program LIKE '%(J%' THEN
            BEGIN
                SELECT job_name INTO l_job_name FROM dba_scheduler_running_jobs WHERE session_id = p_sid;
                DBMS_OUTPUT.PUT_LINE('Detected Scheduler Job: ' || l_job_name);
                DBMS_SCHEDULER.STOP_JOB(l_job_name, force => TRUE);
                DBMS_OUTPUT.PUT_LINE('STOP_JOB executed.');
                RETURN; 
            EXCEPTION WHEN OTHERS THEN NULL; 
            END;
        END IF;

        -- 2. ALREADY DEAD CHECK
        IF p_status = 'KILLED' THEN
            DBMS_OUTPUT.PUT_LINE('Already KILLED status. Escaling to NUCLEAR PROCESS kill.');
            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
            DBMS_OUTPUT.PUT_LINE('OS Process terminated.');
            RETURN;
        END IF;

        -- 3. STANDARD KILL
        BEGIN
            rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'IMMEDIATE');
            DBMS_OUTPUT.PUT_LINE('KILL IMMEDIATE sent.');
            
            -- Wait 2 seconds and check
            DBMS_LOCK.SLEEP(2);
            SELECT COUNT(*) INTO l_check FROM v$session WHERE sid = p_sid AND serial# = p_serial;
            
            IF l_check > 0 THEN
                 rdsadmin.rdsadmin_util.kill(p_sid, p_serial, 'PROCESS');
                 DBMS_OUTPUT.PUT_LINE('Session stubborn -> OS Process terminated.');
            END IF;
        EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error during kill: ' || SQLERRM);
        END;
    END do_kill_logic;

    -------------------------------------------------------------------------
    -- PUBLIC PROCEDURE 1: KILL_SESSION (Sniper)
    -------------------------------------------------------------------------
    PROCEDURE kill_session(p_sid NUMBER, p_serial NUMBER) IS
        CURSOR c_target IS
            SELECT sid, serial#, username, type, program, status
            FROM v$session WHERE sid = p_sid AND serial# = p_serial;
        r c_target%ROWTYPE;
    BEGIN
        OPEN c_target; FETCH c_target INTO r;
        
        IF c_target%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE('Session ' || p_sid || ',' || p_serial || ' not found.');
            CLOSE c_target; RETURN;
        END IF;
        CLOSE c_target;

        -- Safety: Background Process
        IF r.type = 'BACKGROUND' THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: Cannot kill background process.');
            RETURN;
        END IF;

        -- Safety: Self-Kill
        IF r.sid = SYS_CONTEXT('USERENV', 'SID') THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot kill your own current session.');
            RETURN;
        END IF;

        DBMS_OUTPUT.PUT_LINE('=== SNIPER KILL START ===');
        do_kill_logic(r.sid, r.serial#, r.program, r.status);
        DBMS_OUTPUT.PUT_LINE('=== FINISHED ===');
    END kill_session;

    -------------------------------------------------------------------------
    -- PUBLIC PROCEDURE 2: PURGE_USER (Shotgun)
    -------------------------------------------------------------------------
    PROCEDURE purge_user(p_username IN VARCHAR2) IS
        v_user_clean VARCHAR2(128) := UPPER(TRIM(p_username));
        v_count      NUMBER := 0;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('=== PURGE USER START: ' || v_user_clean || ' ===');

        -- Safety: Privileged Accounts
        IF v_user_clean IN ('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB','AUDSYS') THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: Cannot mass-kill privileged account: ' || v_user_clean);
            RETURN;
        END IF;

        -- Safety: Self-Purge
        IF v_user_clean = USER THEN
            DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot purge yourself ('||USER||') while connected.');
            RETURN;
        END IF;

        -- Loop and Kill
        FOR r IN (
            SELECT sid, serial#, username, type, program, status
            FROM v$session 
            WHERE username = v_user_clean
            AND type != 'BACKGROUND'
            AND sid != SYS_CONTEXT('USERENV', 'SID')
        ) LOOP
            do_kill_logic(r.sid, r.serial#, r.program, r.status);
            v_count := v_count + 1;
        END LOOP;

        IF v_count = 0 THEN
            DBMS_OUTPUT.PUT_LINE('No active sessions found for user: ' || v_user_clean);
        ELSE
            DBMS_OUTPUT.PUT_LINE('=== PURGE COMPLETE. Killed ' || v_count || ' sessions. ===');
        END IF;
    END purge_user;

END admin_kill_tools;
/

---

Step 3: Deployment & Usage

Once you compile the package above, here is how you use it.

1. Enable Output (Important!) Since this runs inside a procedure, you must turn on output to see the logs.

SQL

SET SERVEROUTPUT ON;

2. Scenario A: A user "SCOTT" is blocking a deployment.

SQL

EXEC admin_kill_tools.purge_user('SCOTT');

3. Scenario B: A specific rogue query (SID 123, Serial 99).

SQL

EXEC admin_kill_tools.kill_session(123, 99);

Step 4: Permissions (Optional but Recommended)

If you want other team members to use this without giving them the powerful DBA role or ALTER SYSTEM privilege, grant them execute access on this package only.

SQL

GRANT EXECUTE ON admin_kill_tools TO junior_dba_user;

Would you like a companion "View" that shows exactly who is currently blocking others, so you know who to kill?

=================================

SET SERVEROUTPUT ON;

BEGIN

   FOR session_rec IN (

      SELECT sid, serial#

      FROM v$session

      WHERE username = UPPER('HR')

      AND sql_id = '7x9k2m3n4p5q'

      AND status = 'ACTIVE'

   )

   LOOP

      BEGIN

         rdsadmin.rdsadmin_util.kill(

            sid    => session_rec.sid,

            serial => session_rec.serial#

         );

         DBMS_OUTPUT.PUT_LINE('Session killed successfully: SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial#);

      EXCEPTION

         WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('Error killing session SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial# || ': ' || SQLERRM);

            CONTINUE;

      END;

   END LOOP;

   IF SQL%ROWCOUNT = 0 THEN

      DBMS_OUTPUT.PUT_LINE('No active sessions found for username=HR and sql_id=7x9k2m3n4p5q');

   END IF;

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);

END;

/

SET SERVEROUTPUT ON;
DECLARE
   v_username VARCHAR2(128) := 'SCOTT'; -- Manually set username
   v_sql_id   VARCHAR2(13) := 'abc123xyz4567'; -- Manually set sql_id
BEGIN
   FOR session_rec IN (
      SELECT sid, serial#
      FROM v$session
      WHERE username = UPPER(v_username)
      AND sql_id = v_sql_id
      AND status = 'ACTIVE'
   )
   LOOP
      BEGIN
         rdsadmin.rdsadmin_util.kill(
            sid    => session_rec.sid,
            serial => session_rec.serial#
         );
         DBMS_OUTPUT.PUT_LINE('Session killed successfully: SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial#);
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error killing session SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial# || ': ' || SQLERRM);
            CONTINUE;
      END;
   END LOOP;
   IF SQL%ROWCOUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No active sessions found for username=' || UPPER(v_username) || ' and sql_id=' || v_sql_id);
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED;

SET LONG 1000000;

SET LINESIZE 32767;

SET PAGESIZE 0;

SET TRIMSPOOL ON;

SET LONGCHUNKSIZE 1000000;

BEGIN

  FOR sql_rec IN (

    SELECT DISTINCT sql_id

    FROM v$sql

    WHERE sql_id IN ('your_sql_id1', 'your_sql_id2')  -- Replace with your SQL_IDs

    UNION

    SELECT sql_id

    FROM dba_hist_sqltext

    WHERE sql_id IN ('your_sql_id1', 'your_sql_id2')

  )

  LOOP

    DBMS_OUTPUT.PUT_LINE('SQL_ID: ' || sql_rec.sql_id);

    DBMS_OUTPUT.PUT_LINE('SQL Text:');

    FOR rec IN (

      SELECT sql_full_text

      FROM v$sql

      WHERE sql_id = sql_rec.sql_id

      AND rownum = 1

    )

    LOOP

      DBMS_OUTPUT.PUT_LINE(rec.sql_full_text);

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Execution Plan:');

    FOR plan_rec IN (

      SELECT plan_table_output

      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_rec.sql_id, NULL, 'ALL +NOTE +MEMSTATS'))

    )

    LOOP

      DBMS_OUTPUT.PUT_LINE(plan_rec.plan_table_output);

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

  END LOOP;

END;

/

INDEX STATUS CHECK

 WITH index_list AS (

    SELECT 'SCHEMA1' AS index_owner, 'INDEX1' AS index_name FROM dual

    UNION ALL

    SELECT 'SCHEMA2', 'INDEX2' FROM dual

    UNION ALL

    SELECT 'SCHEMA3', 'INDEX3' FROM dual

)

SELECT il.index_owner,

       il.index_name,

       NVL(aip.status, 'UNUSABLE') AS status,

       COUNT(aip.partition_name) AS unusable_partition_count

FROM index_list il

LEFT JOIN all_ind_partitions aip

    ON il.index_owner = aip.index_owner

    AND il.index_name = aip.index_name

    AND aip.status = 'UNUSABLE'

GROUP BY il.index_owner, il.index_name, NVL(aip.status, 'UNUSABLE') ORDER BY il.index_owner, il.index_name;

SELECT index_owner,

       index_name,

       partition_name,

       status

FROM all_ind_partitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

ORDER BY index_owner, index_name, partition_name;

SELECT 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name || ';' AS rebuild_statement

FROM all_ind_partitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

ORDER BY index_owner, index_name, partition_name;

-- Query for unusable partitions

SELECT index_owner, index_name, 'PARTITION' AS object_type, partition_name, status

FROM all_ind_partitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

UNION ALL

-- Query for unusable subpartitions

SELECT index_owner, index_name, 'SUBPARTITION' AS object_type, subpartition_name, status

FROM all_ind_subpartitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

ORDER BY index_owner, index_name, object_type;

4. Broader Scope: Check All Unusable Partitions

If this is a health check, you might want to remove the IN clause to see all unusable index partitions in your schema, not just a specific list.

Modified Query:

SQL

SELECT index_owner,
       index_name,
       COUNT(*) as partition_count
FROM all_ind_partitions
WHERE status = 'UNUSABLE'
AND index_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3') -- Optional: scope to specific schemas
GROUP BY index_owner, index_name;

Why this is useful: This gives you a more comprehensive view of the entire environment, which is often a better approach for monitoring and alerting.