POPULATE TABLE DATA

 

-- CLEAN FINAL REPORT: One clean row per table, no duplicates, perfect for managers

-- Top 50 most active tables across the entire database

SELECT

    "Rank",

    "Schema",

    "Table Name",

    "Rows Inserted",

    "Rows Updated",

    "Rows Deleted",

    "Total DML",

    "Avg Inserts/Day",

    "DML Activity Since",

    "Stats Last Collected"

FROM (

    SELECT

        RANK() OVER (ORDER BY 

            dtm.inserts DESC, 

            dtm.updates DESC, 

            dtm.deletes DESC

        ) AS "Rank",

        dtm.table_owner                                              AS "Schema",

        dtm.table_name                                               AS "Table Name",

        TO_CHAR(dtm.inserts, 'FM999,999,999,990')                    AS "Rows Inserted",

        TO_CHAR(dtm.updates, 'FM999,999,999,990')                     AS "Rows Updated",

        TO_CHAR(dtm.deletes, 'FM999,999,999,990')                    AS "Rows Deleted",

        TO_CHAR(dtm.inserts + dtm.updates + dtm.deletes, 'FM999,999,999,990') AS "Total DML",

        -- Safe average: avoids crazy numbers when stats are fresh

        TO_CHAR(

            ROUND(dtm.inserts / GREATEST(SYSDATE - dtm.timestamp + 1, 1)),

            'FM999,999,990'

        )                                                             AS "Avg Inserts/Day",

        TO_CHAR(dtm.timestamp, 'DD-MON-YYYY HH24:MI')                AS "DML Activity Since",

        TO_CHAR(MAX(st.last_analyzed), 'DD-MON-YYYY HH24:MI')        AS "Stats Last Collected"

    FROM dba_tab_modifications dtm

    LEFT JOIN dba_tab_statistics st

           ON st.owner       = dtm.table_owner

          AND st.table_name  = dtm.table_name

          AND st.object_type = 'TABLE'

    WHERE (dtm.inserts + dtm.updates + dtm.deletes) > 0

      AND dtm.table_owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','GSMADMIN_INTERNAL','XDB','APPQOSSYS','ORACLE_OCM')

    GROUP BY 

        dtm.table_owner, dtm.table_name, dtm.inserts, dtm.updates, dtm.deletes, dtm.timestamp

    ORDER BY "Rank"

)

WHERE ROWNUM <= 50;   -- Top 50 only — clean and readable

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

SELECT

    owner AS "Schema",

    object_name AS "Table Name",

    TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS "Date Created"

FROM

    dba_objects

WHERE

    owner = 'YOUR_SCHEMA_NAME'  -- <<< REPLACE with the schema name

    AND object_type = 'TABLE'

ORDER BY

    created DESC;

==========

SELECT

    -- 1. RANK & SCHEMA INFO

    RANK() OVER (ORDER BY dtm.inserts DESC, dtm.updates DESC, dtm.deletes DESC) AS "Rank",

    dtm.table_owner AS "Schema",

    dtm.table_name AS "Table Name",

    

    -- 2. DML COUNTS (Formatted)

    TO_CHAR(dtm.inserts, 'FM999,999,999,990') AS "Rows Inserted",

    TO_CHAR(dtm.updates, 'FM999,999,999,990') AS "Rows Updated",

    TO_CHAR(dtm.deletes, 'FM999,999,999,990') AS "Rows Deleted",

    TO_CHAR(dtm.inserts + dtm.updates + dtm.deletes, 'FM999,999,999,990') AS "Total DML",

    -- 3. RATE & TIMESTAMP INFO

    TO_CHAR(ROUND(dtm.inserts / NULLIF((SYSDATE - dtm.timestamp), 0)), 'FM999,999,999,990') AS "Avg Inserts/Day",

    TO_CHAR(dtm.timestamp, 'DD-MON-YYYY HH24:MI') AS "Modifications Since",

    dt.last_analyzed AS "Stats Last Collected", -- Pulled from DBA_TABLES

    ROUND(SYSDATE - dtm.timestamp, 1) AS "Days Since Mods"

FROM

    dba_tab_modifications dtm

JOIN

    dba_tables dt ON dtm.table_owner = dt.owner AND dtm.table_name = dt.table_name

WHERE

    (dtm.inserts > 0 OR dtm.updates > 0 OR dtm.deletes > 0) -- Filter for any DML activity

    AND dtm.table_owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','GSMADMIN_INTERNAL','XDB','APPQOSSYS')

ORDER BY

    "Rank" ASC;

partitions

 

CREATE OR REPLACE PROCEDURE schema_name.p_cleanup_drop_columns

AS

    -- Define a collection to hold all your DDL statements

    TYPE t_ddl_list IS TABLE OF VARCHAR2(512);

    

    -- *** 1. ADD YOUR STATEMENTS HERE ***

    v_ddl_statements t_ddl_list := t_ddl_list(

        -- Placeholder 1: REPLACE this line

        'ALTER TABLE OWNER1.TABLE_INVENTORY DROP COLUMN OLD_FLAG_ID', 

        

        -- Placeholder 2: REPLACE this line

        'ALTER TABLE OWNER2.AUDIT_LOGS DROP COLUMN LEGACY_COL_DATE',

        

        -- Placeholder 3: REPLACE this line

        'ALTER TABLE OWNER3.CONFIG_DATA DROP COLUMN TEMP_VALUE',

        

        -- Add as many 'ALTER TABLE ... DROP COLUMN ...' statements as needed

        -- 'ALTER TABLE schema_name.table_name DROP COLUMN column_to_drop'

        

        -- Placeholder N: REPLACE this line

        'ALTER TABLE OWNER4.MASTER_TABLE DROP COLUMN REDUNDANT_KEY'

    );

    

    v_current_ddl VARCHAR2(512);

    

BEGIN

    -- Loop through the defined list of DDL statements

    FOR i IN 1..v_ddl_statements.COUNT LOOP

        v_current_ddl := v_ddl_statements(i);

        

        -- Begin an inner block to handle errors for THIS specific statement

        BEGIN

            -- Output the DDL being run (will be logged by DBMS_SCHEDULER)

            DBMS_OUTPUT.PUT_LINE('Executing: ' || v_current_ddl);

            

            -- Execute the DDL statement

            EXECUTE IMMEDIATE v_current_ddl;

            

            -- Commit implicitly happens due to DDL, but good to ensure transaction boundary

            COMMIT; 

            

            DBMS_OUTPUT.PUT_LINE('SUCCESS: ' || v_current_ddl);

            

        EXCEPTION

            WHEN OTHERS THEN

                -- Log the error, but do not raise the exception (continue the loop)

                DBMS_OUTPUT.PUT_LINE('*** ERROR *** Failed to execute DDL: ' || v_current_ddl);

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

                

                -- The NULL statement allows the procedure to proceed to the next item

                NULL; 

        END;

    END LOOP;

    

    DBMS_OUTPUT.PUT_LINE('Procedure p_cleanup_drop_columns finished processing ' || v_ddl_statements.COUNT || ' statements.');

    

END;

/

-- How to schedule the enhanced procedure

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'BACKGROUND_COLUMN_DROP_JOB',

    job_type        => 'STORED_PROCEDURE',

    job_action      => 'SCHEMA_NAME.P_CLEANUP_DROP_COLUMNS', -- The short, correct call

    enabled         => TRUE,

    auto_drop       => TRUE

  );

END;

/

==========

DECLARE
  -- *** Configuration Parameters (Same as yours) ***
  TYPE t_tab IS TABLE OF VARCHAR2(128);
  v_tables t_tab := t_tab(
    'OWNER.TABLE_N1',
    'OWNER.TABLE_N2',
    'OWNER.TABLE_N3',
    'OWNER.TABLE_N4',
    'OWNER.TABLE_N5'
  );
  v_max_boundary  NUMBER;
  v_start         NUMBER;
  v_sql           VARCHAR2(4000);
  
  -- *** New Iteration Variables ***
  v_new_boundary  NUMBER;
  v_new_pname     VARCHAR2(128);
  v_current_pname CONSTANT VARCHAR2(128) := 'REPORT_ID_7000'; -- The MAXVALUE partition
  
BEGIN
  -- 1. Find the current maximum boundary (Your logic is perfect here)
  SELECT MAX(TO_NUMBER(REGEXP_SUBSTR(high_value, '\d+')))
  INTO   v_max_boundary
  FROM   user_tab_partitions
  WHERE  table_name IN (
           SELECT UPPER(REGEXP_SUBSTR(column_value))
           FROM   TABLE(v_tables)
         )
    AND  partition_name LIKE 'REPORT_ID_%'
    AND  high_value NOT IN ('MAXVALUE', 'maxvalue') -- Exclude the MAXVALUE partition
    AND  high_value IS NOT NULL;
  IF v_max_boundary IS NULL THEN
    v_max_boundary := 270;
  END IF;
  v_start := v_max_boundary + 1;
  DBMS_OUTPUT.PUT_LINE('Highest existing boundary: ' || v_max_boundary);
  DBMS_OUTPUT.PUT_LINE('Will create partitions from ' || v_start || ' to ' || (v_start + 11));
  -- 2. Loop over each table
  FOR i IN 1..v_tables.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Processing table: ' || v_tables(i));
    -- 3. Loop 12 times to create 12 new partitions
    FOR j IN 0..11 LOOP
      v_new_boundary := v_start + j;
      v_new_pname := 'REPORT_ID_' || v_new_boundary;
      
      -- SPLIT PARTITION AT (new_boundary)
      -- This creates the new partition (P_XXX) up to the boundary,
      -- and leaves the remaining data (P_7000) to the right.
      v_sql := 'ALTER TABLE ' || v_tables(i) || ' SPLIT PARTITION ' || v_current_pname ||
               ' AT (' || (v_new_boundary + 1) || ')' || -- Boundary is always the next value (less than)
               ' INTO (PARTITION ' || v_new_pname || ' VALUES LESS THAN (' || (v_new_boundary + 1) || '), ' ||
               'PARTITION ' || v_current_pname || ')';
      -- Execute the DDL statement
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE(' -> Created partition ' || v_new_pname);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('12 new partitions created successfully on ' || v_tables(i));
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE('All done! 12 partitions added to ' || v_tables.COUNT || ' tables.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Failing SQL: ' || v_sql);
    RAISE;
END;
/

partitions drop

 

--  THIS ONE WORKS – tested on Oracle 19c / 21c RDS

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name   => 'CLEANUP_OLD_PARTITIONS_01',

    job_type   => 'PLSQL_BLOCK',

    job_action => q'[

      DECLARE

        v_owner       CONSTANT VARCHAR2(128) := 'OWNER1';           -- CHANGE THIS

        v_table       CONSTANT VARCHAR2(128) := 'YOUR_TABLE_NAME'; -- CHANGE THIS

        v_truncated            PLS_INTEGER := 0;

      BEGIN

        FOR rec IN (

          SELECT partition_name

          FROM   dba_tab_partitions

          WHERE  owner       = v_owner

            AND  table_name  = v_table

            AND  partition_name NOT IN ('P_202511', 'P_202510')   -- KEEP these two

        )

        LOOP

          EXECUTE IMMEDIATE

            'ALTER TABLE ' || v_owner || '.' || v_table ||

            ' TRUNCATE PARTITION ' || rec.partition_name ||

            ' DROP STORAGE';

          v_truncated := v_truncated + 1;

          -- Optional: commit every 20 partitions so redo log doesn’t explode

          IF MOD(v_truncated, 20) = 0 THEN

            COMMIT;

          END IF;

        END LOOP;

        COMMIT;

        DBMS_OUTPUT.PUT_LINE('SUCCESS: Truncated ' || v_truncated || ' partitions from ' ||

                             v_owner || '.' || v_table);

      EXCEPTION

        WHEN OTHERS THEN

          ROLLBACK;

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

          RAISE;

      END;

    ]',

    start_date => SYSTIMESTAMP,

    enabled    => TRUE,

    auto_drop  => TRUE,

    comments   => 'Fast background TRUNCATE of all old partitions except last 2'

  );

END;

/

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

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'CLEANUP_OLD_PARTITIONS_01',

    job_type        => 'PLSQL_BLOCK',

    job_action      => q'[

      DECLARE

        v_cnt_dropped  PLS_INTEGER := 0;

        v_cnt_rebuilt  PLS_INTEGER := 0;

      BEGIN

        -- 1. Drop old partitions with PARALLEL 12 (fastest possible segment drop)

        FOR rec IN (

          SELECT partition_name

          FROM   user_tab_partitions

          WHERE  table_name = 'YOUR_TABLE_NAME'            -- CHANGE THIS

            AND  partition_name NOT IN ('P_202511', 'P_202510')  -- CHANGE THESE

        ) LOOP

          EXECUTE IMMEDIATE

            'ALTER TABLE YOUR_TABLE_NAME DROP PARTITION ' || rec.partition_name ||

            ' PARALLEL 12';

          v_cnt_dropped := v_cnt_dropped + 1;

        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Dropped ' || v_cnt_dropped || ' partitions with PARALLEL 12');

        -- 2. Rebuild global indexes with PARALLEL 12

        FOR idx IN (

          SELECT index_name

          FROM   user_indexes

          WHERE  table_name = 'YOUR_TABLE_NAME'

            AND (status = 'UNUSABLE' OR partitioned = 'NO')

        ) LOOP

          EXECUTE IMMEDIATE

            'ALTER INDEX ' || idx.index_name || 

            ' REBUILD ONLINE PARALLEL 12';

          v_cnt_rebuilt := v_cnt_rebuilt + 1;

        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Rebuilt ' || v_cnt_rebuilt || ' global indexes with PARALLEL 12');

      EXCEPTION

        WHEN OTHERS THEN

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

          RAISE;

      END;]',

    start_date      => SYSTIMESTAMP,

    enabled         => TRUE,

    auto_drop       => TRUE,

    comments        => 'Ultra-fast partition purge - DROP + REBUILD both with PARALLEL 12'

  );

END;

/

RDS Kill script

SET SERVEROUTPUT ON

DECLARE

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

    -- CONFIGURATION (Tri-Mode)

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

    -- 1. Sniper Mode (Specific Session)

    v_sid_raw     VARCHAR2(50)  := TRIM('&sid'); 

    v_serial_raw  VARCHAR2(50)  := TRIM('&serial');

    

    -- 2. Purge Mode (All sessions for User)

    v_target_user VARCHAR2(128) := TRIM('&username');

    -- 3. SQL Killer Mode (All sessions running specific SQL)

    v_target_sql  VARCHAR2(13)  := TRIM('&sql_id');

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

    v_sid         NUMBER;

    v_serial      NUMBER;

    

    -- Protected Users List

    TYPE t_protected IS TABLE OF VARCHAR2(30);

    v_protected t_protected := t_protected('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB','AUDSYS');

    -- The "Wide Net" Cursor

    CURSOR c_sessions IS

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

        FROM v$session

        WHERE (v_target_user IS NOT NULL AND username = v_target_user)

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

           OR (v_target_sql IS NOT NULL AND sql_id = v_target_sql);

    v_killed_cnt  NUMBER := 0;

    v_is_safe     BOOLEAN := TRUE;

    v_mode_msg    VARCHAR2(100);

    v_inputs_set  NUMBER := 0;

    -- 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);

        

        -- Scheduler 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;

        -- Already Dead Check

        IF p_status = 'KILLED' THEN

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

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

            RETURN;

        END IF;

        -- Standard Kill

        BEGIN

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

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

            

            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(' OS Process terminated.');

            END IF;

        EXCEPTION WHEN OTHERS THEN

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

        END;

    END;

BEGIN

    -- [STEP 1] Safe Number Conversion

    BEGIN

        v_sid    := TO_NUMBER(v_sid_raw);

        v_serial := TO_NUMBER(v_serial_raw);

    EXCEPTION WHEN VALUE_ERROR THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: SID and Serial must be numbers.');

        RETURN;

    END;

    -- Handle literal "NULL" strings

    IF UPPER(v_target_user) = 'NULL' THEN v_target_user := NULL; END IF;

    IF UPPER(v_target_sql) = 'NULL'  THEN v_target_sql  := NULL; END IF;

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

    -- [SAFETY CHECK 0] Ambiguity Check (The "Pick One" Rule)

    IF v_sid IS NOT NULL THEN v_inputs_set := v_inputs_set + 1; END IF;

    IF v_target_user IS NOT NULL THEN v_inputs_set := v_inputs_set + 1; END IF;

    IF v_target_sql IS NOT NULL THEN v_inputs_set := v_inputs_set + 1; END IF;

    IF v_inputs_set > 1 THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: Ambiguous Input detected.');

        DBMS_OUTPUT.PUT_LINE('   You provided multiple inputs. Please choose EXACTLY ONE mode:');

        DBMS_OUTPUT.PUT_LINE('   1. SID/Serial only (Sniper Mode)');

        DBMS_OUTPUT.PUT_LINE('   2. Username only (Purge Mode)');

        DBMS_OUTPUT.PUT_LINE('   3. SQL_ID only (SQL Killer Mode)');

        RETURN;

    END IF;

    IF v_inputs_set = 0 THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: No input detected. Provide SID, Username, OR SQL_ID.');

        RETURN;

    END IF;

    -- Determine Mode Message

    IF v_target_user IS NOT NULL THEN

        v_mode_msg := 'PURGE USER [' || v_target_user || ']';

    ELSIF v_target_sql IS NOT NULL THEN

        v_mode_msg := 'SQL KILLER [SQL_ID=' || v_target_sql || ']';

    ELSE

        v_mode_msg := 'SNIPER [SID=' || v_sid || ']';

    END IF;

    DBMS_OUTPUT.PUT_LINE('Mode: ' || v_mode_msg);

    -- [SAFETY CHECK 2] Prevent Self-Kill

    IF v_target_user = USER THEN

        DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot purge yourself.');

        RETURN;

    END IF;

    -- EXECUTION LOOP

    FOR r IN c_sessions LOOP

        v_is_safe := TRUE;

        -- [SAFETY CHECK 3] Background

        IF r.type = 'BACKGROUND' THEN

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

            CONTINUE;

        END IF;

        

        -- [SAFETY CHECK 4] Self

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

             CONTINUE;

        END IF;

        -- [SAFETY CHECK 5] Protected Users (Crucial for SQL_ID mode!)

        -- Even if we kill by SQL_ID, we check WHO is running it.

        -- If RDSADMIN is running the bad SQL, we MUST NOT kill it.

        IF r.username IS NOT NULL THEN

            FOR i IN 1..v_protected.COUNT LOOP

                IF r.username = v_protected(i) THEN

                    DBMS_OUTPUT.PUT_LINE('BLOCKED SID ' || r.sid || ': User ' || r.username || ' is PROTECTED.');

                    v_is_safe := FALSE;

                    EXIT; 

                END IF;

            END LOOP;

        END IF;

        IF v_is_safe THEN

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

            v_killed_cnt := v_killed_cnt + 1;

        END IF;

    END LOOP;

    IF v_killed_cnt = 0 THEN

        DBMS_OUTPUT.PUT_LINE('No actionable sessions found.');

    ELSE

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

    END IF;

END;

/

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

SET SERVEROUTPUT ON

DECLARE

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

    -- CONFIGURATION (Fixed for Empty Inputs)

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

    -- We read these as STRINGS first to handle empty inputs safely.

    v_sid_raw     VARCHAR2(50)  := TRIM('&sid'); 

    v_serial_raw  VARCHAR2(50)  := TRIM('&serial');

    v_target_user VARCHAR2(128) := TRIM('&username');

    

    -- Now we convert them to numbers for logic

    v_sid         NUMBER;

    v_serial      NUMBER;

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

    -- Protected Users List

    TYPE t_protected IS TABLE OF VARCHAR2(30);

    v_protected t_protected := t_protected('SYS','SYSTEM','RDSADMIN','DBSNMP','XDB','AUDSYS');

    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;

    v_is_safe     BOOLEAN := TRUE;

    v_mode_msg    VARCHAR2(100);

    -- Internal Procedure

    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);

        

        -- Scheduler 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;

        -- Already Dead Check

        IF p_status = 'KILLED' THEN

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

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

            RETURN;

        END IF;

        -- Standard Kill

        BEGIN

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

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

            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('   OS Process terminated.');

            END IF;

        EXCEPTION WHEN OTHERS THEN

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

        END;

    END;

BEGIN

    -- [STEP 1] Safe Conversion: Convert string inputs to Numbers

    -- If empty, TO_NUMBER returns NULL (which is what we want)

    BEGIN

        v_sid    := TO_NUMBER(v_sid_raw);

        v_serial := TO_NUMBER(v_serial_raw);

    EXCEPTION WHEN VALUE_ERROR THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: SID and Serial must be numbers.');

        RETURN;

    END;

    -- Handle literal "NULL" string input

    IF UPPER(v_target_user) = 'NULL' THEN v_target_user := NULL; END IF;

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

    -- [SAFETY CHECK 0] Ambiguity Check

    IF v_target_user IS NOT NULL AND (v_sid IS NOT NULL OR v_serial IS NOT NULL) THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: Ambiguous Input detected.');

        DBMS_OUTPUT.PUT_LINE('   Please choose ONE mode (Sniper OR Purge).');

        RETURN;

    END IF;

    -- Display Mode

    IF v_target_user IS NOT NULL THEN

        v_mode_msg := 'PURGE USER [' || v_target_user || ']';

    ELSE

        v_mode_msg := 'SNIPER [SID=' || NVL(TO_CHAR(v_sid),'NULL') || ']';

    END IF;

    DBMS_OUTPUT.PUT_LINE('Mode: ' || v_mode_msg);

    -- [SAFETY CHECK 1] No Input

    IF v_target_user IS NULL AND v_sid IS NULL THEN

        DBMS_OUTPUT.PUT_LINE('ERROR: No input detected.');

        RETURN;

    END IF;

    -- [SAFETY CHECK 2] Self-Kill

    IF v_target_user = USER THEN

        DBMS_OUTPUT.PUT_LINE('BLOCKED: You cannot purge yourself.');

        RETURN;

    END IF;

    -- EXECUTION LOOP

    FOR r IN c_sessions LOOP

        v_is_safe := TRUE;

        -- [SAFETY CHECK 3] Background

        IF r.type = 'BACKGROUND' THEN

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

            CONTINUE;

        END IF;

        

        -- [SAFETY CHECK 4] Self (Current Session)

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

             CONTINUE;

        END IF;

        -- [SAFETY CHECK 5] Protected Users

        IF r.username IS NOT NULL THEN

            FOR i IN 1..v_protected.COUNT LOOP

                IF r.username = v_protected(i) THEN

                    DBMS_OUTPUT.PUT_LINE('BLOCKED SID ' || r.sid || ': User ' || r.username || ' is PROTECTED.');

                    v_is_safe := FALSE;

                    EXIT; 

                END IF;

            END LOOP;

        END IF;

        IF v_is_safe THEN

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

            v_killed_cnt := v_killed_cnt + 1;

        END IF;

    END LOOP;

    IF v_killed_cnt = 0 THEN

        DBMS_OUTPUT.PUT_LINE('No actionable sessions found.');

    ELSE

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

    END IF;

END;

/

=============
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 6000
SET PAGESIZE 0
SET VERIFY OFF
DECLARE
  ------------------------------------------------------------------
  -- INPUT: CHANGE THESE ONLY
  ------------------------------------------------------------------
  v_sid_serial_list VARCHAR2(4000) := '140,32001;255,1089';
  v_username_list   VARCHAR2(4000) := 'BATCH_JOB,REPORT_USER';
  ------------------------------------------------------------------
  -- Internal
  ------------------------------------------------------------------
  TYPE t_sid_rec IS RECORD (sid NUMBER, serial NUMBER, username VARCHAR2(30), status VARCHAR2(10), program VARCHAR2(100));
  TYPE t_sid_tab IS TABLE OF t_sid_rec;
  v_sessions t_sid_tab := t_sid_tab();
  v_db_name     VARCHAR2(30) := SYS_CONTEXT('USERENV','DB_NAME');
  v_current_sid NUMBER       := SYS_CONTEXT('USERENV','SID');
  v_confirm     VARCHAR2(10);
  PROCEDURE log(p_msg VARCHAR2, p_emphasis BOOLEAN := FALSE) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(
      TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3') ||
      ' | DB=' || v_db_name ||
      CASE WHEN p_emphasis THEN ' | *** '||p_msg||' ***' ELSE ' | '||p_msg END
    );
  END;
  PROCEDURE kill_one(p_sid NUMBER, p_serial NUMBER, p_method VARCHAR2) IS
    v_action VARCHAR2(60) := CASE p_method
                               WHEN 'IMMEDIATE' THEN 'IMMEDIATE KILL'
                               WHEN 'PROCESS'   THEN '*** AGGRESSIVE PROCESS KILL (kill -9) ***'
                             END;
  BEGIN
    log('KILL '||v_action||': SID='||p_sid||' SERIAL#='||p_serial, p_method = 'PROCESS');
    BEGIN
      rdsadmin.rdsadmin_util.kill(
        sid    => p_sid,
        serial => p_serial,
        method => p_method
      );
      log('SUCCESS: '||v_action||' issued.', p_method = 'PROCESS');
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -3135 THEN
          log('INFO: Session already terminated (ORA-3135).');
        ELSIF SQLCODE = -1013 THEN
          log('WARN: Insufficient privileges.');
        ELSE
          log('ERROR: '||SQLERRM);
        END IF;
    END;
  END;
BEGIN
  log('=== SESSION KILL SAFETY CHECK ===', TRUE);
  log('Current Session SID: '||v_current_sid);
  ------------------------------------------------------------------
  -- STEP 1: COLLECT AND PREVIEW SESSIONS
  ------------------------------------------------------------------
  IF v_sid_serial_list IS NOT NULL THEN
    FOR rec IN (
      WITH pairs AS (
        SELECT TRIM(REGEXP_SUBSTR(v_sid_serial_list, '[^;]+', 1, LEVEL)) AS pair
        FROM dual
        CONNECT BY LEVEL <= REGEXP_COUNT(v_sid_serial_list, ';') + 1
      )
      SELECT
        TO_NUMBER(TRIM(REGEXP_SUBSTR(pair, '[^,]+', 1, 1))) AS sid,
        TO_NUMBER(TRIM(REGEXP_SUBSTR(pair, '[^,]+', 1, 2))) AS serial
      FROM pairs
      WHERE REGEXP_LIKE(pair, '^[0-9]+,[0-9]+$')
    ) LOOP
      IF rec.sid != v_current_sid THEN
        FOR s IN (
          SELECT sid, serial#, username, status, program
          FROM v$session
          WHERE sid = rec.sid AND serial# = rec.serial
        ) LOOP
          v_sessions.EXTEND;
          v_sessions(v_sessions.LAST) := s;
          log('QUEUED (SID): SID='||s.sid||' SERIAL#='||s.serial#||' USER='||NVL(s.username,'<null>')||' STATUS='||s.status||' PROGRAM='||SUBSTR(s.program,1,50));
        END LOOP;
      END IF;
    END LOOP;
  END IF;
  IF v_username_list IS NOT NULL THEN
    FOR u IN (
      SELECT TRIM(REGEXP_SUBSTR(v_username_list, '[^,]+', 1, LEVEL)) AS username
      FROM dual
      CONNECT BY LEVEL <= REGEXP_COUNT(v_username_list, ',') + 1
    ) LOOP
      FOR s IN (
        SELECT sid, serial#, username, status, program
        FROM v$session
        WHERE UPPER(username) = UPPER(u.username)
          AND type = 'USER'
          AND status IN ('ACTIVE', 'INACTIVE', 'KILLED')
          AND sid != v_current_sid
          AND username NOT LIKE 'RDS%'
          AND program NOT LIKE '%(P%)'
      ) LOOP
        IF NOT EXISTS (
          SELECT 1 FROM TABLE(v_sessions) v
          WHERE v.sid = s.sid AND v.serial = s.serial#
        ) THEN
          v_sessions.EXTEND;
          v_sessions(v_sessions.LAST) := s;
          log('QUEUED (user '||u.username||'): SID='||s.sid||' SERIAL#='||s.serial#||' STATUS='||s.status||' PROGRAM='||SUBSTR(s.program,1,50));
        END IF;
      END LOOP;
    END LOOP;
  END IF;
  ------------------------------------------------------------------
  -- STEP 2: SHOW PREVIEW
  ------------------------------------------------------------------
  IF v_sessions.COUNT = 0 THEN
    log('No sessions found to kill. Exiting safely.');
    RETURN;
  END IF;
  log('=== PREVIEW: '||v_sessions.COUNT||' SESSIONS TO BE KILLED ===', TRUE);
  FOR i IN 1..v_sessions.COUNT LOOP
    log('  ['||i||'] SID='||v_sessions(i).sid||
        ' SERIAL#='||v_sessions(i).serial||
        ' USER='||NVL(v_sessions(i).username,'<null>')||
        ' STATUS='||v_sessions(i).status||
        ' PROGRAM='||SUBSTR(v_sessions(i).program,1,60));
  END LOOP;
  ------------------------------------------------------------------
  -- STEP 3: CONFIRM BEFORE KILL
  ------------------------------------------------------------------
  log('DO YOU WANT TO PROCEED WITH KILL? (Type YES to continue)', TRUE);
  v_confirm := UPPER(TRIM('&CONFIRM_KILL'));
  IF v_confirm != 'YES' THEN
    log('KILL ABORTED BY USER. No action taken.');
    RETURN;
  END IF;
  log('KILL CONFIRMED. Proceeding...', TRUE);
  ------------------------------------------------------------------
  -- STEP 4: EXECUTE KILLS
  ------------------------------------------------------------------
  FOR i IN 1..v_sessions.COUNT LOOP
    kill_one(v_sessions(i).sid, v_sessions(i).serial, 'IMMEDIATE');
    DBMS_LOCK.SLEEP(1);
    kill_one(v_sessions(i).sid, v_sessions(i).serial, 'PROCESS');
  END LOOP;
  log('=== ALL '||v_sessions.COUNT||' SESSIONS KILLED SUCCESSFULLY ===', TRUE);
EXCEPTION
  WHEN OTHERS THEN
    log('FATAL ERROR: '||SQLERRM, TRUE);
    RAISE;
END;
/

GATHER STATS

UPDATED SCRIPT:

DECLARE

    v_job_name     VARCHAR2(128) := 'STATS_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS');

    v_program_name VARCHAR2(128) := 'GATHER_STATS_PROG';

BEGIN

    -- PREVENT DUPLICATES: KILL ANY RUNNING STATS JOB FIRST

    FOR rec IN (

        SELECT job_name FROM user_scheduler_jobs

        WHERE job_name LIKE 'STATS_%' AND state = 'RUNNING'

    ) LOOP

        DBMS_SCHEDULER.STOP_JOB(rec.job_name, force => TRUE);

        DBMS_SCHEDULER.DROP_JOB(rec.job_name, force => TRUE);

    END LOOP;

    -- CREATE PROGRAM (idempotent)

    BEGIN

        DBMS_SCHEDULER.CREATE_PROGRAM(

            program_name   => v_program_name,

            program_type   => 'PLSQL_BLOCK',

            program_action => '

                BEGIN

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE1_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE2_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE3_NAME'', degree=>32, cascade=>TRUE);

                END;',

            enabled        => TRUE

        );

    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;

    -- CREATE & RUN ONE JOB

    DBMS_SCHEDULER.CREATE_JOB(

        job_name     => v_job_name,

        program_name => v_program_name,

        enabled      => TRUE,

        auto_drop    => TRUE

    );

    DBMS_SCHEDULER.RUN_JOB(v_job_name, use_current_session => FALSE);

    DBMS_OUTPUT.PUT_LINE('ONE JOB STARTED: ' || v_job_name);

END;

/

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

======

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

-- 1. UNIQUE JOB NAME (NEVER REUSED)

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

DECLARE

    v_job_name     VARCHAR2(128);

    v_program_name VARCHAR2(128) := 'GATHER_STATS_PROG';  -- reusable

BEGIN

    -- Generate unique job name: STATS_YYYYMMDD_HH24MISS

    v_job_name := 'STATS_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS');

    ----------------------------------------------------------------

    -- 2. CLEANUP: Drop only FAILED/BROKEN jobs with same prefix

    ----------------------------------------------------------------

    FOR rec IN (

        SELECT job_name

        FROM user_scheduler_jobs

        WHERE job_name LIKE 'STATS_%'

          AND state IN ('BROKEN', 'FAILED', 'STOPPED')

          AND job_name != v_job_name  -- never touch current

    ) LOOP

        BEGIN

            DBMS_SCHEDULER.DROP_JOB(rec.job_name, force => TRUE);

        EXCEPTION WHEN OTHERS THEN NULL;

        END;

    END LOOP;

    ----------------------------------------------------------------

    -- 3. CREATE REUSABLE PROGRAM (once)

    ----------------------------------------------------------------

    BEGIN

        DBMS_SCHEDULER.CREATE_PROGRAM(

            program_name   => v_program_name,

            program_type   => 'PLSQL_BLOCK',

            program_action => '

                BEGIN

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE1_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE2_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE3_NAME'', degree=>32, cascade=>TRUE);

                EXCEPTION WHEN OTHERS THEN RAISE;

                END;',

            enabled        => TRUE,

            comments       => 'Stats gather DEGREE 32'

        );

    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;

    ----------------------------------------------------------------

    -- 4. CREATE AND RUN UNIQUE JOB

    ---------------------------------------

-------------------------

    DBMS_SCHEDULER.CREATE_JOB(

        job_name     => v_job_name,

        program_name => v_program_name,

        enabled      => FALSE,

        auto_drop    => TRUE,

        comments     => 'One-time stats run'

    );

    DBMS_SCHEDULER.SET_ATTRIBUTE(v_job_name, 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);

    DBMS_SCHEDULER.ENABLE(v_job_name);

    DBMS_SCHEDULER.RUN_JOB(v_job_name, use_current_session => FALSE);

    DBMS_OUTPUT.PUT_LINE('Started: ' || v_job_name || ' (DEGREE 32)');

END;

/

-- All recent jobs

SELECT job_name, state, last_start_date, run_duration

FROM user_scheduler_jobs

WHERE job_name LIKE 'STATS_%'

ORDER BY last_start_date DESC;

-- Running

SELECT job_name, elapsed_time FROM all_scheduler_running_jobs

WHERE job_name LIKE 'STATS_%';

-- Last run

SELECT job_name, status, error# FROM all_scheduler_job_run_details

WHERE job_name LIKE 'STATS_%'

ORDER BY log_date DESC FETCH FIRST 5 ROWS ONLY;

index rebuild in the background

-- Shorten ALL your index names to 24 chars + generate RENAME scripts

-- Paste your index names below (one per line, schema.index_name)

SET LINESIZE 6000

SET PAGESIZE 0

SET FEEDBACK OFF

SPOOL rename_long_indexes.sql

WITH your_indexes (owner, index_name) AS (

  SELECT 'SCHEMA1', 'VERY_LONG_INDEX_NAME_PART1_2025' FROM DUAL UNION ALL

  SELECT 'SCHEMA1', 'ANOTHER_SUPER_LONG_INDEX_NAME_2024' FROM DUAL UNION ALL

  SELECT 'SCHEMA2', 'INDEX_WITH_TOO_MANY_CHARS_FOR_ORACLE' FROM DUAL UNION ALL

  -- PASTE YOUR FULL LIST BELOW (schema.index_name)

  -- Example:

  -- SELECT 'SCOTT', 'MY_INDEX_THAT_IS_WAY_TOO_LONG_FOR_24_CHARS' FROM DUAL UNION ALL

  -- SELECT 'HR',    'EMPLOYEE_PERFORMANCE_INDEX_2025_Q4' FROM DUAL

)

SELECT

  'Found: ' || owner || '.' || index_name || 

  ' (len=' || LENGTH(index_name) || ') -> ' || SUBSTR(index_name,1,24) AS info,

  'ALTER INDEX ' || owner || '.' || index_name || 

  ' RENAME TO ' || SUBSTR(index_name,1,24) || ';' AS rename_sql

FROM your_indexes

WHERE LENGTH(index_name) > 24

ORDER BY LENGTH(index_name) DESC;

SPOOL OFF

-- Count

SELECT COUNT(*) AS indexes_to_shorten

FROM your_indexes

WHERE LENGTH(index_name) > 24;

Find long indexes:

-- Script: Shorten index names > 24 chars to 24 and generate RENAME statements

-- Works in Toad, SQL*Plus, SQL Developer

-- Safe for Oracle RDS

SET LINESIZE 6000

SET PAGESIZE 0

SET FEEDBACK OFF

SET VERIFY OFF

SPOOL shorten_index_names.sql

SELECT

  'Found long index: ' || owner || '.' || index_name || ' (length=' || LENGTH(index_name) || ')' AS info,

  'ALTER INDEX ' || owner || '.' || index_name || 

  ' RENAME TO ' || 

  SUBSTR(index_name, 1, 24) || 

  ';' AS rename_sql

FROM all_indexes

WHERE LENGTH(index_name) > 24

  AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'GSMADMIN_INTERNAL')

ORDER BY LENGTH(index_name) DESC, owner, index_name;

SPOOL OFF

-- Optional: Show count

SELECT COUNT(*) AS long_indexes_found

FROM all_indexes

WHERE LENGTH(index_name) > 24

  AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'GSMADMIN_INTERNAL');

SET FEEDBACK ON

=======

SET FEEDBACK ON

======

SET LINESIZE 6000

SET PAGESIZE 0

SET FEEDBACK OFF

SET VERIFY OFF

DECLARE

  ------------------------------------------------------------------

  --  YOUR INDEX SCRIPT – copy-paste it between the q'[...]' delimiters

  ------------------------------------------------------------------

  v_script CLOB := q'[

    ALTER INDEX SCOTT.MY_IDX      REBUILD ONLINE PARALLEL 4;

    ALTER INDEX HR.EMP_IDX        REBUILD ONLINE;

    ALTER INDEX SALES.ORD_IDX     REBUILD ONLINE NOLOGGING;

    -- add as many lines as you need – 6000 chars per line is fine

    EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','MY_TABLE');

  ]';

BEGIN

  ------------------------------------------------------------------

  -- Create the scheduler job

  ------------------------------------------------------------------

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'REBUILD_INDEXES_BG',

    job_type        => 'PLSQL_BLOCK',

    job_action      => q'[

      DECLARE

        l_sql   CLOB := :1;                     -- bind the script

        l_stmt  VARCHAR2(32767);

      BEGIN

        FOR rec IN (

          SELECT REGEXP_SUBSTR(l_sql,

                               '[^;]+;',               -- everything up to the next ;

                               1, LEVEL) AS stmt

          FROM   dual

          CONNECT BY LEVEL <= REGEXP_COUNT(l_sql, ';')

        ) LOOP

          l_stmt := TRIM(REPLACE(rec.stmt, CHR(10), ' '));

          BEGIN

            EXECUTE IMMEDIATE l_stmt;

            DBMS_OUTPUT.PUT_LINE('OK: '||SUBSTR(l_stmt,1,200));

          EXCEPTION

            WHEN OTHERS THEN

              DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM||' --> '||SUBSTR(l_stmt,1,200));

          END;

        END LOOP;

      END;

    ]',

    number_of_arguments => 1,

    enabled             => FALSE,

    auto_drop           => TRUE,      -- delete after it finishes

    comments            => 'Background index rebuild – one shot'

  );

  ------------------------------------------------------------------

  -- Bind the script (CLOB) to argument 1

  ------------------------------------------------------------------

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

    job_name          => 'REBUILD_INDEXES_BG',

    argument_position => 1,

    argument_value    => v_script

  );

  ------------------------------------------------------------------

  -- Fire it

  ------------------------------------------------------------------

  DBMS_SCHEDULER.ENABLE('REBUILD_INDEXES_BG');

END;

/