Tuesday, November 18, 2025

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;
/
Posted by at No comments:

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;
/
Posted by at No comments:

Saturday, November 15, 2025

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;
/
Posted by at No comments:

Wednesday, November 5, 2025

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;
Posted by at No comments:

Tuesday, November 4, 2025

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;
/
Posted by at No comments:

Monday, October 20, 2025

data guard

-- Generates DDL to enable constraints with NOVALIDATE for specified schemas
SELECT
    -- Order foreign keys ('R') last to minimize dependency issues
    CASE WHEN constraint_type = 'R' THEN 2 ELSE 1 END AS sort_order,
    -- Construct DDL statement with quoted identifiers for safety
    'ALTER TABLE "' || owner || '"."' || table_name || 
    '" ENABLE NOVALIDATE CONSTRAINT "' || constraint_name || '";' AS ddl_statement
FROM
    dba_constraints
WHERE
    -- Target specified schemas
    owner IN ('SCHEMA_A', 'SCHEMA_B', 'SCHEMA_C', 'SCHEMA_D', 'SCHEMA_E', 'SCHEMA_F', 'SCHEMA_G')
    -- Exclude constraints already in ENABLED NOVALIDATE state
    AND NOT (status = 'ENABLED' AND validated = 'NOT VALIDATED')
    -- Include only primary key, unique, foreign key, and check constraints
    AND constraint_type IN ('P', 'U', 'R', 'C')
    -- Exclude system-generated constraints to avoid ORA-31603
    AND constraint_name NOT LIKE 'SYS_C%'
ORDER BY
    sort_order, table_name, constraint_name;


-


- File: precheck_sequences.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL precheck_sequences.txt
SELECT 
  sequence_owner AS schema_name,
  COUNT(*) AS sequence_count,
  LISTAGG(sequence_name, ', ') WITHIN GROUP (ORDER BY sequence_name) AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')  -- Replace with your 7 schemas
GROUP BY sequence_owner
UNION ALL
SELECT 
  'TOTAL' AS schema_name,
  COUNT(*) AS sequence_count,
  NULL AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
ORDER BY schema_name;
SPOOL OFF

-- File: generate_sequences_increment_by_1.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL create_sequences_increment_by_1.sql
SELECT 
  'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name || 
  ' START WITH ' || last_number || 
  ' INCREMENT BY 1' || 
  CASE WHEN max_value IS NULL THEN ' NOMAXVALUE' ELSE ' MAXVALUE ' || max_value END || 
  CASE WHEN min_value IS NULL THEN ' NOMINVALUE' ELSE ' MINVALUE ' || min_value END || 
  CASE WHEN cycle_flag = 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END || 
  CASE WHEN cache_size = 0 THEN ' NOCACHE' ELSE ' CACHE ' || cache_size END || 
  CASE WHEN order_flag = 'Y' THEN ' ORDER' ELSE ' NOORDER' END || 
  CASE WHEN keep_value = 'N' THEN ' NOKEEP' ELSE ' KEEP' END || 
  CASE WHEN scale_flag = 'N' THEN ' NOSCALE' WHEN scale_flag = 'Y' THEN ' SCALE' ELSE '' END || 
  CASE WHEN session_flag = 'N' THEN ' GLOBAL' WHEN session_flag = 'Y' THEN ' SESSION' ELSE '' END || ';' AS create_statement
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')  -- Replace with your 7 schemas
ORDER BY sequence_owner, sequence_name;
SPOOL OFF


-- File: generate_sequences_preserve_all.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL create_sequences_preserve_all.sql
SELECT 
  'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name || 
  ' START WITH ' || last_number || 
  ' INCREMENT BY ' || increment_by || 
  CASE WHEN max_value IS NULL THEN ' NOMAXVALUE' ELSE ' MAXVALUE ' || max_value END || 
  CASE WHEN min_value IS NULL THEN ' NOMINVALUE' ELSE ' MINVALUE ' || min_value END || 
  CASE WHEN cycle_flag = 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END || 
  CASE WHEN cache_size = 0 THEN ' NOCACHE' ELSE ' CACHE ' || cache_size END || 
  CASE WHEN order_flag = 'Y' THEN ' ORDER' ELSE ' NOORDER' END || 
  CASE WHEN keep_value = 'N' THEN ' NOKEEP' ELSE ' KEEP' END || 
  CASE WHEN scale_flag = 'N' THEN ' NOSCALE' WHEN scale_flag = 'Y' THEN ' SCALE' ELSE '' END || 
  CASE WHEN session_flag = 'N' THEN ' GLOBAL' WHEN session_flag = 'Y' THEN ' SESSION' ELSE '' END || ';' AS create_statement
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')  -- Replace with your 7 schemas
ORDER BY sequence_owner, sequence_name;
SPOOL OFF

-- File: postcheck_sequences.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL postcheck_sequences.txt
SELECT 
  sequence_owner AS schema_name,
  COUNT(*) AS sequence_count,
  LISTAGG(sequence_name, ', ') WITHIN GROUP (ORDER BY sequence_name) AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')  -- Replace with your 7 schemas
GROUP BY sequence_owner
UNION ALL
SELECT 
  'TOTAL' AS schema_name,
  COUNT(*) AS sequence_count,
  NULL AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
ORDER BY schema_name;
SPOOL OFF



===

SET LINESIZE 4000
SET PAGESIZE 0
SPOOL precheck_sequences_and_constraints.txt
-- Sequences Pre-Check
SELECT 
  'SEQUENCES,' || sequence_owner AS schema_name,
  COUNT(*) AS object_count,
  LISTAGG(sequence_name, ', ') WITHIN GROUP (ORDER BY sequence_name) AS object_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')  -- Replace with your 7 schemas
GROUP BY sequence_owner
UNION ALL
SELECT 
  'SEQUENCES,TOTAL' AS schema_name,
  COUNT(*) AS object_count,
  NULL AS object_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
UNION ALL
-- Constraints Pre-Check
SELECT 
  'CONSTRAINTS,' || owner AS schema_name,
  COUNT(*) AS object_count,
  LISTAGG(constraint_name || ' (' || constraint_type || ')', ', ') WITHIN GROUP (ORDER BY constraint_name) AS object_names
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
  AND constraint_type IN ('P', 'U', 'R', 'C')  -- Primary, Unique, Foreign, Check
GROUP BY owner
UNION ALL
SELECT 
  'CONSTRAINTS,TOTAL' AS schema_name,
  COUNT(*) AS object_count,
  NULL AS object_names
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
  AND constraint_type IN ('P', 'U', 'R', 'C')
ORDER BY schema_name;
SPOOL OFF



SET LINESIZE 4000
SET PAGESIZE 0
SPOOL postcheck_sequences_and_constraints.txt
-- Sequence Counts and Names
SELECT 
  'SEQUENCES,' || sequence_owner AS schema_name,
  COUNT(*) AS object_count
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
GROUP BY sequence_owner
UNION ALL
SELECT 
  'SEQUENCES,TOTAL' AS schema_name,
  COUNT(*) AS object_count
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
UNION ALL
SELECT 
  'SEQUENCES,' || sequence_owner AS schema_name,
  sequence_name AS object_count
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
UNION ALL
-- Constraint Counts and Names
SELECT 
  'CONSTRAINTS,' || owner AS schema_name,
  COUNT(*) AS object_count
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
  AND constraint_type IN ('P', 'U', 'R', 'C')
GROUP BY owner
UNION ALL
SELECT 
  'CONSTRAINTS,TOTAL' AS schema_name,
  COUNT(*) AS object_count
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
  AND constraint_type IN ('P', 'U', 'R', 'C')
UNION ALL
SELECT 
  'CONSTRAINTS,' || owner AS schema_name,
  constraint_name || ' (' || constraint_type || ')' AS object_count
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
  AND constraint_type IN ('P', 'U', 'R', 'C')
ORDER BY schema_name, object_count;
SPOOL OFF

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

DECLARE
  CURSOR csr IS
    SELECT sequence_name,
           sequence_owner AS schema_name,
           last_number,
           min_value,
           max_value,
           increment_by,
           cycle_flag,
           order_flag,
           cache_size,
           keep_value,
           scale_flag,
           session_flag
    FROM all_sequences
    WHERE sequence_owner = 'YOUR_SCHEMA';  -- Replace with your source schema
  v_total_sequences NUMBER;
BEGIN
  -- Count total sequences for validation
  SELECT COUNT(*) INTO v_total_sequences
  FROM all_sequences
  WHERE sequence_owner = 'YOUR_SCHEMA';
  
  -- Output header for validation report
  DBMS_OUTPUT.PUT_LINE('Total sequences found in schema ' || 'YOUR_SCHEMA' || ': ' || v_total_sequences);
  DBMS_OUTPUT.PUT_LINE('Schema | Sequence Name | Start With | Increment By | Min Value | Max Value | Cycle | Order | Cache | Keep | Scale | Session | Create Statement');
  DBMS_OUTPUT.PUT_LINE('-------|---------------|------------|--------------|-----------|-----------|-------|-------|-------|------|-------|--------|-----------------');
  
  FOR rec IN csr LOOP
    -- Output validation data
    DBMS_OUTPUT.PUT_LINE(
      rec.schema_name || ' | ' || 
      rec.sequence_name || ' | ' || 
      rec.last_number || ' | ' || 
      '1' || ' | ' || 
      NVL(TO_CHAR(rec.min_value), 'NULL') || ' | ' || 
      NVL(TO_CHAR(rec.max_value), 'NULL') || ' | ' || 
      rec.cycle_flag || ' | ' || 
      rec.order_flag || ' | ' || 
      rec.cache_size || ' | ' || 
      rec.keep_value || ' | ' || 
      NVL(rec.scale_flag, 'N/A') || ' | ' || 
      rec.session_flag || ' | ' ||
      'CREATE SEQUENCE ' || rec.schema_name || '.' || rec.sequence_name || 
      ' START WITH ' || rec.last_number || 
      ' INCREMENT BY 1' || 
      CASE WHEN rec.max_value IS NULL THEN ' NOMAXVALUE' ELSE ' MAXVALUE ' || rec.max_value END || 
      CASE WHEN rec.min_value IS NULL THEN ' NOMINVALUE' ELSE ' MINVALUE ' || rec.min_value END || 
      CASE WHEN rec.cycle_flag = 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END || 
      CASE WHEN rec.cache_size = 0 THEN ' NOCACHE' ELSE ' CACHE ' || rec.cache_size END || 
      CASE WHEN rec.order_flag = 'Y' THEN ' ORDER' ELSE ' NOORDER' END || 
      CASE WHEN rec.keep_value = 'N' THEN ' NOKEEP' ELSE ' KEEP' END || 
      CASE WHEN rec.scale_flag = 'N' THEN ' NOSCALE' WHEN rec.scale_flag = 'Y' THEN ' SCALE' ELSE '' END || 
      CASE WHEN rec.session_flag = 'N' THEN ' GLOBAL' WHEN rec.session_flag = 'Y' THEN ' SESSION' ELSE '' END || ';'
    );
  END LOOP;
  
  -- Final instruction
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE('Copy the CREATE SEQUENCE statements above and execute them in the target database.');
  DBMS_OUTPUT.PUT_LINE('Ensure the schema ' || '''YOUR_SCHEMA''' || ' exists in the target database.');
  DBMS_OUTPUT.PUT_LINE('Verify that ' || v_total_sequences || ' sequences are created in the target database.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/
=======

SELECT 
    d.name AS db_name,
    d.db_unique_name,
    d.open_mode,
    CASE 
        WHEN d.database_role = 'PRIMARY' THEN 'PRIMARY'
        WHEN d.database_role = 'PHYSICAL STANDBY' THEN 'PHYSICAL STANDBY'
        WHEN d.database_role = 'LOGICAL STANDBY' THEN 'LOGICAL STANDBY'
        WHEN d.database_role = 'SNAPSHOT STANDBY' THEN 'SNAPSHOT STANDBY'
        ELSE d.database_role
    END AS database_role,
    CASE 
        WHEN d.protection_mode = 'MAXIMUM PROTECTION' THEN 'MAX PROTECTION'
        WHEN d.protection_mode = 'MAXIMUM AVAILABILITY' THEN 'MAX AVAILABILITY'
        WHEN d.protection_mode = 'MAXIMUM PERFORMANCE' THEN 'MAX PERFORMANCE'
        ELSE d.protection_mode
    END AS protection_mode,
    d.protection_level,
    d.remote_archive,
    d.flashback_on,
    d.log_mode,
    CASE WHEN dr.status = 'VALID' THEN 'APPLYING' ELSE dr.status END AS apply_status,
    dr.process AS apply_process,
    dr.error AS last_error
FROM v$database d
LEFT JOIN v$dataguard_status dr ON 1=1
WHERE ROWNUM = 1;

SELECT 
    T.tablespace_name,
    -- Total Size
    TO_CHAR(T.total_blocks * B.block_size / 1024 / 1024, '999,999.00') AS "Total Size (MB)",
    -- Used Size
    TO_CHAR(SUM(S.used_blocks * B.block_size) / 1024 / 1024, '999,999.00') AS "Used Size (MB)",
    -- Free Size (Total - Used)
    TO_CHAR((T.total_blocks * B.block_size - SUM(S.used_blocks * B.block_size)) / 1024 / 1024, '999,999.00') AS "Free Size (MB)",
    -- Used Percentage
    TO_CHAR(ROUND(SUM(S.used_blocks) / T.total_blocks * 100, 2), '999.00') AS "Used (%)"
FROM 
    V$SORT_SEGMENT S,
    DBA_TABLESPACES B,
    (
        SELECT 
            tablespace_name, 
            SUM(blocks) AS total_blocks 
        FROM DBA_TEMP_FILES 
        GROUP BY tablespace_name
    ) T
WHERE 
    S.tablespace_name(+) = T.tablespace_name 
    AND T.tablespace_name = B.tablespace_name
GROUP BY 
    T.tablespace_name, T.total_blocks, B.block_size
ORDER BY "Used (%)" DESC;


SELECT 
    S.sid, 
    S.serial#, 
    S.username, 
    S.osuser,
    S.program, 
    T.blocks * B.block_size / 1024 / 1024 AS "MB Used",
    T.tablespace
FROM 
    V$TEMPSEG_USAGE T, 
    V$SESSION S, 
    DBA_TABLESPACES B
WHERE 
    T.session_addr = S.saddr
    AND T.tablespace = B.tablespace_name
ORDER BY 
    "MB Used" DESC;


SELECT 
    ARCH.THREAD# AS "Thread",
    ARCH.SEQUENCE# AS "Last Received", 
    APPL.SEQUENCE# AS "Last Applied", 
    (ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "Seq Diff",
    ROUND((SYSDATE - MAX(ARCH.FIRST_TIME)) * 1440, 1) AS "Time Lag (min)",
    CASE 
        WHEN (ARCH.SEQUENCE# - APPL.SEQUENCE#) > 10 THEN ' CRITICAL'
        WHEN (ARCH.SEQUENCE# - APPL.SEQUENCE#) > 5 THEN ' HIGH'
        ELSE 'OK'
    END AS "Status"
FROM 
    (SELECT THREAD#, SEQUENCE#, FIRST_TIME
     FROM V$ARCHIVED_LOG 
     WHERE (THREAD#, FIRST_TIME) IN (
         SELECT THREAD#, MAX(FIRST_TIME) 
         FROM V$ARCHIVED_LOG 
         GROUP BY THREAD#
     )
    ) ARCH,
    (SELECT THREAD#, SEQUENCE#
     FROM V$LOG_HISTORY 
     WHERE (THREAD#, FIRST_TIME) IN (
         SELECT THREAD#, MAX(FIRST_TIME) 
         FROM V$LOG_HISTORY 
         GROUP BY THREAD#
     )
    ) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
GROUP BY ARCH.THREAD#, ARCH.SEQUENCE#, APPL.SEQUENCE#, ARCH.FIRST_TIME
ORDER BY 1;
Posted by at No comments:

Sunday, October 19, 2025

Validation

 


SELECT
    owner,
    table_name,
    TO_CHAR(num_rows, '999,999,999,999') AS dictionary_row_estimate,
    TO_CHAR(data_mb, '999,999.00') AS size_mb
FROM
    (
        SELECT
            t.owner,
            t.table_name,
            t.num_rows,
            (s.bytes / 1024 / 1024) AS data_mb
        FROM
            all_tables t
        JOIN
            dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
        WHERE
            t.owner IN ('HR', 'SALES', 'FINANCE') -- 👈 ADJUST SCHEMAS IF NECESSARY
            AND t.table_name NOT LIKE 'BIN$%'
            AND s.segment_type = 'TABLE'
        ORDER BY
            t.num_rows DESC
    )
WHERE
    ROWNUM <= 10;


SELECT
    'Verified Top 10 Report' AS report_title,
    t.owner AS schema_name,
    t.table_name,
    -- 1. Dictionary Estimate (Often Stale)
    TO_CHAR(t.num_rows, '999,999,999,999') AS dictionary_estimate,
    
    -- 2. Your Verified Live Count (From the parallel system)
    TO_CHAR(l.row_count, '999,999,999,999') AS verified_live_count,
    
    -- 3. Status and Timestamp
    c.status AS final_status,
    TO_CHAR(l.count_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS verification_time
    
FROM
    all_tables t
JOIN
    dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
LEFT JOIN
    row_count_control c ON t.owner = c.schema_name AND t.table_name = c.table_name
LEFT JOIN
    row_counts_scheduler_log l ON t.owner = l.schema_name AND t.table_name = l.table_name
WHERE
    t.owner IN ('HR', 'SALES', 'FINANCE')
    AND s.segment_type = 'TABLE'
ORDER BY
    t.num_rows DESC -- Keep the largest tables at the top
FETCH NEXT 10 ROWS ONLY;

SELECT
    '3. Overall Project Completion' AS report_title,
    successful_count AS tables_processed_successfully,
    failed_count AS tables_requiring_dba_fix,
    pct_complete || '%' AS completion_rate
FROM
    row_count_summary;


=====

SET SERVEROUTPUT ON SIZE UNLIMITED
WHENEVER SQLERROR EXIT FAILURE

DECLARE
    -- Variables to hold data from cursors for output formatting
    v_output_line VARCHAR2(500);
    v_total_tasks NUMBER;
    v_pct_complete NUMBER;
    
    -- Cursor for Query 2 (Per-Schema Breakdown)
    CURSOR c_schema_status IS
        SELECT
            schema_name,
            SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) AS done_count,
            SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failed_count,
            ROUND(SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) AS pct_done
        FROM
            row_count_control
        GROUP BY
            schema_name
        ORDER BY
            pct_done DESC;
            
    -- Cursor for Query 4 (Specific Table Lookup/Validation Example)
    CURSOR c_validation_check IS
        SELECT
            t.owner AS schema_name,
            t.table_name,
            TO_CHAR(t.num_rows, '999,999,999,999') AS estimate,
            TO_CHAR(l.row_count, '999,999,999,999') AS live_count,
            c.status AS final_status
        FROM
            all_tables t
        LEFT JOIN
            row_count_control c ON t.owner = c.schema_name AND t.table_name = c.table_name
        LEFT JOIN
            row_counts_scheduler_log l ON t.owner = l.schema_name AND t.table_name = l.table_name
        WHERE
            t.owner IN ('HR', 'SALES', 'FINANCE')
            -- Limit to 10 rows for a quick validation snapshot
            AND ROWNUM <= 10
        ORDER BY
            t.table_name; 

BEGIN
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '========================================================================');
    DBMS_OUTPUT.PUT_LINE('  I. OVERALL SYSTEM HEALTH DASHBOARD');
    DBMS_OUTPUT.PUT_LINE('========================================================================');
    
    -- Query 1: Overall Dashboard (Using the View)
    SELECT total_tasks, pct_complete 
    INTO v_total_tasks, v_pct_complete
    FROM row_count_summary;

    DBMS_OUTPUT.PUT_LINE(RPAD('Total Workload Tasks:', 30) || TO_CHAR(v_total_tasks, '999,999'));
    DBMS_OUTPUT.PUT_LINE(RPAD('Overall Completion Rate:', 30) || TO_CHAR(v_pct_complete, '990.0') || '%');
    
    -- Query the view for full status line (simpler than fetching 8 columns into variables)
    FOR r IN (SELECT * FROM row_count_summary) LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD('  Success Count:', 30) || TO_CHAR(r.success_count, '999,999'));
        DBMS_OUTPUT.PUT_LINE(RPAD('  Pending Count (Remaining):', 30) || TO_CHAR(r.pending_count, '999,999'));
        DBMS_OUTPUT.PUT_LINE(RPAD('  Failed Count (Requires DBA):', 30) || TO_CHAR(r.failed_count, '999'));
        DBMS_OUTPUT.PUT_LINE(RPAD('  Last Completion Time:', 30) || TO_CHAR(r.last_completion_time, 'HH24:MI:SS'));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(10) || '========================================================================');
    DBMS_OUTPUT.PUT_LINE('  II. STATUS BREAKDOWN PER SCHEMA');
    DBMS_OUTPUT.PUT_LINE('========================================================================');

    -- Query 2: Status Breakdown Per Schema
    DBMS_OUTPUT.PUT_LINE(RPAD('SCHEMA', 15) || RPAD('TOTAL_TABLES', 15) || RPAD('COMPLETE', 10) || RPAD('FAILED', 10) || RPAD('%_DONE', 8));
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 58, '-'));

    FOR r_schema IN c_schema_status LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(r_schema.schema_name, 15) || 
            RPAD(TO_CHAR(r_schema.done_count + r_schema.failed_count + r_schema.pending_count, '999,999'), 15) || 
            RPAD(TO_CHAR(r_schema.done_count, '999,999'), 10) ||
            RPAD(TO_CHAR(r_schema.failed_count, '999'), 10) ||
            RPAD(TO_CHAR(r_schema.pct_done, '990.0'), 8)
        );
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(10) || '========================================================================');
    DBMS_OUTPUT.PUT_LINE('  III. THREE-WAY TABLE COUNT VALIDATION (SAMPLE)');
    DBMS_OUTPUT.PUT_LINE('========================================================================');
    DBMS_OUTPUT.PUT_LINE(RPAD('SCHEMA.TABLE', 30) || RPAD('ALL_TABLES (EST)', 20) || RPAD('LIVE_COUNT', 20) || RPAD('STATUS', 10));
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));

    -- Query 4: Detailed Table Validation
    FOR r_val IN c_validation_check LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(r_val.schema_name || '.' || r_val.table_name, 30) ||
            RPAD(r_val.estimate, 20) ||
            RPAD(r_val.live_count, 20) ||
            RPAD(r_val.final_status, 10)
        );
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- END OF MONITORING REPORT ---');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('!!! FATAL ERROR DURING MONITORING !!! SQLERRM: ' || SQLERRM);
        -- Note: We don't rollback here as this is a read-only process
END;
/

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

1. Overall System Health Dashboard (The Quick Status)

This query gives you the overall progress, success rate, and active job counts from the summary view.

SQL
SELECT
    '1. OVERALL SYSTEM STATUS' AS metric_group,
    t.total_tasks,
    t.successful_count AS tasks_complete,
    t.pending_count AS tasks_waiting,
    t.running_count AS tasks_active,
    t.failed_count AS tasks_failed,
    t.pct_complete || '%' AS completion_rate,
    TO_CHAR(t.last_completion_time, 'YYYY-MM-DD HH24:MI:SS') AS last_success_time
FROM
    row_count_summary t;

2. Status Breakdown Per Schema (Workload Health)

This query uses the row_count_control table to show which schemas are complete, failed, or still pending work.

SQL
SELECT
    schema_name,
    COUNT(*) AS total_tables,
    SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) AS done_count,
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failed_count,
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS pending_count,
    ROUND(SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) AS pct_done
FROM
    row_count_control
GROUP BY
    schema_name
ORDER BY
    pct_done DESC, schema_name;

3. Detailed Failed Task Report (Troubleshooting)

This query pinpoints every task currently marked as FAILED and shows the exact error message that caused the stall.

SQL
SELECT
    '3. FAILED TASK REPORT' AS metric_group,
    job_partition_id AS batch_id,
    schema_name,
    table_name,
    error_message,
    TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS failure_time
FROM
    row_count_control
WHERE
    status = 'FAILED'
ORDER BY
    end_time DESC;

4. Three-Way Table Count Validation (Accuracy Check)

This query proves the integrity of your system by comparing Oracle's stale estimate (ALL_TABLES) against your verified, live count (row_counts_scheduler_log) for a sample of tables.

SQL
SELECT
    '4. LIVE COUNT VALIDATION (SAMPLE)' AS metric_group,
    c.schema_name,
    c.table_name,
    -- 1. Source Estimate (Unverified)
    TO_CHAR(t.num_rows, '999,999,999,999') AS all_tables_estimate,
    -- 2. Your Verified Live Count (Final Result)
    TO_CHAR(l.row_count, '999,999,999,999') AS log_table_live_count,
    -- 3. Control Status
    c.status AS final_control_status
FROM
    all_tables t
LEFT JOIN
    row_count_control c ON t.owner = c.schema_name AND t.table_name = c.table_name
LEFT JOIN
    row_counts_scheduler_log l ON t.owner = l.schema_name AND t.table_name = l.table_name
WHERE
    c.status IN ('COMPLETE', 'PENDING', 'RUNNING') -- Focus on tasks in the control queue
ORDER BY
    c.status DESC, c.schema_name, c.table_name
FETCH NEXT 10 ROWS ONLY; -- Adjust ROWNUM limit as needed
Posted by at No comments:
Subscribe to: Posts (Atom)