index_check

CREATE TABLE user_lock_log (

  username          VARCHAR2(128),

  original_status   VARCHAR2(32),

  action_taken      VARCHAR2(32),  -- e.g., 'LOCKED_EXPIRED'

  log_time          TIMESTAMP DEFAULT SYSTIMESTAMP,

  restored          CHAR(1) DEFAULT 'N'

);

SET SERVEROUTPUT ON;

DECLARE

  v_sql             VARCHAR2(4000);

BEGIN

  -- Clear previous logs (optional: comment out if appending)

  DELETE FROM user_lock_log;

  COMMIT;

  -- Process regular users that are OPEN

  FOR rec IN (

    SELECT username

    FROM all_users

    WHERE status = 'OPEN'

      AND username NOT IN ('SYS', 'SYSTEM', 'RDSADMIN', 'OUTLN', 'ORDSYS', 'MDSYS', 'WMSYS', 

                           'CTXSYS', 'DVSYS', 'GSMADMIN_INTERNAL', 'XDB')  -- Exclude main accounts

  ) LOOP

    BEGIN

      -- Log original status

      INSERT INTO user_lock_log (username, original_status, action_taken)

      VALUES (rec.username, 'OPEN', 'LOCKED_EXPIRED');

      -- Lock the account

      v_sql := 'ALTER USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(rec.username) || ' ACCOUNT LOCK';

      EXECUTE IMMEDIATE v_sql;

      -- Expire the password

      v_sql := 'ALTER USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(rec.username) || ' PASSWORD EXPIRE';

      EXECUTE IMMEDIATE v_sql;

      COMMIT;  -- Commit per user

      DBMS_OUTPUT.PUT_LINE('Locked and expired: ' || rec.username);

    

    EXCEPTION

      WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error for ' || rec.username || ': ' || SQLERRM);

        -- Log error but continue

        UPDATE user_lock_log 

        SET action_taken = 'ERROR: ' || SQLERRM 

        WHERE username = rec.username;

        COMMIT;

    END;

  END LOOP;

  -- Summary

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

  DBMS_OUTPUT.PUT_LINE('Total users locked/expired: ' || SQL%ROWCOUNT);

  FOR sum_rec IN (SELECT COUNT(*) AS cnt FROM user_lock_log WHERE action_taken = 'LOCKED_EXPIRED') LOOP

    DBMS_OUTPUT.PUT_LINE('Successful: ' || sum_rec.cnt);

  END LOOP;

END;

/

SELECT username, status

FROM all_users

WHERE username NOT IN ('SYS', 'SYSTEM', 'RDSADMIN', 'OUTLN', 'ORDSYS', 'MDSYS', 'WMSYS', 

                       'CTXSYS', 'DVSYS', 'GSMADMIN_INTERNAL', 'XDB')

ORDER BY username;

post:

SET SERVEROUTPUT ON;

DECLARE

  v_sql             VARCHAR2(4000);

BEGIN

  FOR rec IN (

    SELECT username, original_status

    FROM user_lock_log

    WHERE action_taken = 'LOCKED_EXPIRED'

      AND restored = 'N'

  ) LOOP

    BEGIN

      -- Always unlock first (since we locked during maintenance)

      v_sql := 'ALTER USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(rec.username) || ' ACCOUNT UNLOCK';

      EXECUTE IMMEDIATE v_sql;

      -- Restore original status

      IF rec.original_status = 'OPEN' THEN

        -- Just unlock (already done); no further action

        NULL;

      ELSIF rec.original_status = 'EXPIRED' THEN

        -- Expire password but keep unlocked

        v_sql := 'ALTER USER ' || DBMS_ASSERT.SQL_OBJECT_NAME(rec.username) || ' PASSWORD EXPIRE';

        EXECUTE IMMEDIATE v_sql;

      END IF;

      -- Mark as restored

      UPDATE user_lock_log 

      SET restored = 'Y', 

          log_time = SYSTIMESTAMP 

      WHERE username = rec.username;

      COMMIT;

      DBMS_OUTPUT.PUT_LINE('Restored: ' || rec.username || ' to ' || rec.original_status);

    

    EXCEPTION

      WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error restoring ' || rec.username || ': ' || SQLERRM);

        -- Log error

        UPDATE user_lock_log 

        SET action_taken = action_taken || '; Restore Error: ' || SQLERRM 

        WHERE username = rec.username;

        COMMIT;

    END;

  END LOOP;

  -- Summary

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

  FOR sum_rec IN (SELECT COUNT(*) AS cnt FROM user_lock_log WHERE restored = 'Y') LOOP

    DBMS_OUTPUT.PUT_LINE('Successfully restored: ' || sum_rec.cnt);

  END LOOP;

END;

/

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

-- Check indexes in index_build but not in DBA_INDEXES

SELECT 'Extra in index_build' AS status, owner, index_name

FROM (

    SELECT owner, index_name

    FROM index_build

    WHERE build_status = 'NEED_BUILD'

    AND index_type = 'NORMAL'

    AND owner IN ('DS1_01', 'DS1_04', 'DS1_05', 'DS1_06', 'DS1_02', 'DS1_07', 'DS1_03')

    MINUS

    SELECT owner, index_name

    FROM dba_indexes

    WHERE index_type = 'NORMAL'

    AND owner IN ('DS1_01', 'DS1_04', 'DS1_05', 'DS1_06', 'DS1_02', 'DS1_07', 'DS1_03')

)

UNION ALL

-- Check indexes in DBA_INDEXES but not in index_build

SELECT 'Missing in index_build' AS status, owner, index_name

FROM (

    SELECT owner, index_name

    FROM dba_indexes

    WHERE index_type = 'NORMAL'

    AND owner IN ('DS1_01', 'DS1_04', 'DS1_05', 'DS1_06', 'DS1_02', 'DS1_07', 'DS1_03')

    MINUS

    SELECT owner, index_name

    FROM index_build

    WHERE build_status = 'NEED_BUILD'

    AND index_type = 'NORMAL'

    AND owner IN ('DS1_01', 'DS1_04', 'DS1_05', 'DS1_06', 'DS1_02', 'DS1_07', 'DS1_03')

)

ORDER BY status, owner, index_name;

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

 -- Validation Query: Compare index_build with DBA_INDEXES for mismatches and missing items
WITH index_build_data AS (
    SELECT DISTINCT owner, index_name, table_name, column_names, index_type, uniqueness
    FROM index_build
    WHERE build_status = 'NEED_BUILD'  -- Adjust if you want all rows
    AND index_type = 'NORMAL'  -- Per your procedure
),
dba_index_data AS (
    SELECT di.owner, di.index_name, di.table_name, di.index_type, di.uniqueness,
           LISTAGG(dic.column_name, ',') WITHIN GROUP (ORDER BY dic.column_position) AS dba_column_names
    FROM dba_indexes di
    LEFT JOIN dba_ind_columns dic
        ON di.owner = dic.index_owner AND di.index_name = dic.index_name
    WHERE di.index_type = 'NORMAL'  -- Match your procedure
    GROUP BY di.owner, di.index_name, di.table_name, di.index_type, di.uniqueness
),
comparison AS (
    SELECT 
        COALESCE(ib.owner, di.owner) AS owner,
        COALESCE(ib.index_name, di.index_name) AS index_name,
        ib.table_name AS ib_table_name,
        di.table_name AS dba_table_name,
        ib.column_names AS ib_column_names,
        di.dba_column_names AS dba_column_names,
        ib.index_type AS ib_index_type,
        di.index_type AS dba_index_type,
        ib.uniqueness AS ib_uniqueness,
        di.uniqueness AS dba_uniqueness,
        CASE 
            WHEN ib.index_name IS NULL AND di.index_name IS NOT NULL THEN 'Missing in index_build (present in source DBA_INDEXES)'
            WHEN ib.index_name IS NOT NULL AND di.index_name IS NULL THEN 'Extra in index_build (missing in source DBA_INDEXES)'
            WHEN ib.table_name != di.table_name THEN 'Table name mismatch'
            WHEN ib.index_type != di.index_type THEN 'Index type mismatch'
            WHEN ib.uniqueness != di.uniqueness THEN 'Uniqueness mismatch'
            WHEN NVL(ib.column_names, 'NULL') != NVL(di.dba_column_names, 'NULL') THEN 'Column names mismatch'
            ELSE 'Match'
        END AS mismatch_reason
    FROM index_build_data ib
    FULL OUTER JOIN dba_index_data di
        ON ib.owner = di.owner AND ib.index_name = di.index_name
)
SELECT 
    owner,
    index_name,
    ib_table_name,
    dba_table_name,
    ib_column_names,
    dba_column_names,
    ib_index_type,
    dba_index_type,
    ib_uniqueness,
    dba_uniqueness,
    mismatch_reason
FROM comparison
WHERE mismatch_reason != 'Match'
ORDER BY owner, index_name;