clean up

DECLARE

  v_handle NUMBER;

  v_job_state VARCHAR2(30);

  v_status ku$_Status;

  v_le ku$_LogEntry;

  v_ind NUMBER;

BEGIN

  v_handle := DBMS_DATAPUMP.ATTACH(job_name => 'FULL_EXPORT_HEMANT');

  DBMS_DATAPUMP.GET_STATUS(

    handle => v_handle,

    mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR + DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS,

    timeout => 0,

    job_state => v_job_state,

    status => v_status

  );

  DBMS_OUTPUT.PUT_LINE('Job state: ' || v_job_state);

  IF BITAND(v_status.mask, DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0 THEN

    v_le := v_status.error;

    IF v_le IS NOT NULL THEN

      v_ind := v_le.FIRST;

      WHILE v_ind IS NOT NULL LOOP

        DBMS_OUTPUT.PUT_LINE(v_le(v_ind).LogText);

        v_ind := v_le.NEXT(v_ind);

      END LOOP;

    END IF;

  END IF;

  DBMS_DATAPUMP.DETACH(v_handle);

END;

/

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

DECLARE

  v_sql VARCHAR2(4000);

  v_count NUMBER := 0;

  v_error_count NUMBER := 0;

  v_pre_count NUMBER;

  v_post_count NUMBER;

  v_schema_names VARCHAR2(4000) := UPPER(:schema_names); -- Bind variable, no default

  -- Cursor for all objects (tables, views, synonyms, sequences, procedures, functions, packages, triggers, types)

  CURSOR c_objects (p_schema IN VARCHAR2) IS

    SELECT owner, object_name, object_type,

           CASE object_type

             WHEN 'TABLE' THEN 'TABLE CASCADE CONSTRAINTS'

             WHEN 'VIEW' THEN 'VIEW'

             WHEN 'SYNONYM' THEN 'SYNONYM'

             WHEN 'SEQUENCE' THEN 'SEQUENCE'

             WHEN 'PROCEDURE' THEN 'PROCEDURE'

             WHEN 'FUNCTION' THEN 'FUNCTION'

             WHEN 'PACKAGE' THEN 'PACKAGE'

             WHEN 'TRIGGER' THEN 'TRIGGER'

             WHEN 'TYPE' THEN 'TYPE FORCE'

           END AS drop_type

    FROM all_objects

    WHERE owner = p_schema

      AND object_type IN ('TABLE', 'VIEW', 'SYNONYM', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'TYPE')

      AND generated = 'N'

      AND (object_type != 'TABLE' OR temporary = 'N')

    ORDER BY CASE WHEN object_type = 'TABLE' THEN 1 ELSE 2 END; -- Tables first

  -- Cursor for counting objects

  CURSOR c_count (p_schema IN VARCHAR2) IS

    SELECT COUNT(*) AS obj_count

    FROM all_objects

    WHERE owner = p_schema

      AND object_type IN ('TABLE', 'VIEW', 'SYNONYM', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'TYPE')

      AND generated = 'N'

      AND (object_type != 'TABLE' OR temporary = 'N');

BEGIN

  -- Check for valid schema input

  IF v_schema_names IS NULL OR TRIM(v_schema_names) IS NULL THEN

    RAISE_APPLICATION_ERROR(-20001, 'Schema names required (e.g., SCHEMA1,SCHEMA2).');

  END IF;

  -- Create simple log table

  BEGIN

    EXECUTE IMMEDIATE 'CREATE TABLE drop_log (log_message VARCHAR2(4000), log_time TIMESTAMP DEFAULT SYSTIMESTAMP)';

  EXCEPTION

    WHEN OTHERS THEN

      IF SQLCODE = -955 THEN NULL; -- Table exists

      ELSE RAISE;

      END IF;

  END;

  -- Enable parallel DDL

  BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';

  EXCEPTION

    WHEN OTHERS THEN NULL;

  END;

  -- Process each schema

  FOR i IN 1 .. (REGEXP_COUNT(v_schema_names, ',') + 1) LOOP

    DECLARE

      v_schema VARCHAR2(128) := TRIM(REGEXP_SUBSTR(v_schema_names, '[^,]+', 1, i));

    BEGIN

      IF v_schema IS NULL THEN CONTINUE; END IF;

      -- Verify schema exists

      EXECUTE IMMEDIATE 'SELECT 1 FROM all_users WHERE username = :1' INTO v_count USING v_schema;

      -- Count objects before drop

      OPEN c_count(v_schema);

      FETCH c_count INTO v_pre_count;

      CLOSE c_count;

      EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

        USING 'Pre-drop count for ' || v_schema || ': ' || v_pre_count || ' objects';

      -- Drop all objects

      FOR rec IN c_objects(v_schema) LOOP

        BEGIN

          v_sql := 'DROP ' || rec.drop_type || ' ' || DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '.' || DBMS_ASSERT.SQL_OBJECT_NAME(rec.object_name);

          EXECUTE IMMEDIATE v_sql;

          v_count := v_count + 1;

        EXCEPTION

          WHEN OTHERS THEN

            v_error_count := v_error_count + 1;

            EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

              USING 'Error dropping ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ': ' || SQLERRM;

        END;

      END LOOP;

      -- Count objects after drop

      OPEN c_count(v_schema);

      FETCH c_count INTO v_post_count;

      CLOSE c_count;

      EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

        USING 'Post-drop count for ' || v_schema || ': ' || v_post_count || ' objects';

    EXCEPTION

      WHEN OTHERS THEN

        v_error_count := v_error_count + 1;

        EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

          USING 'Error processing schema ' || v_schema || ': ' || SQLERRM;

    END;

  END LOOP;

  -- Log summary

  EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

    USING 'Dropped ' || v_count || ' objects. Errors: ' || v_error_count;

  DBMS_OUTPUT.PUT_LINE('Dropped ' || v_count || ' objects. Errors: ' || v_error_count);

EXCEPTION

  WHEN OTHERS THEN

    EXECUTE IMMEDIATE 'INSERT INTO drop_log (log_message) VALUES (:1)'

      USING 'Script error: ' || SQLERRM;

    RAISE;

END;

/

tablespace drop

SELECT 

    t.tablespace_name,

    t.owner AS schema_name,

    ROUND(SUM(t.bytes) / 1024 / 1024 / 1024, 2) AS size_gb

FROM 

    dba_segments t

WHERE 

    t.owner = 'YOUR_SCHEMA_NAME'  -- Replace with your schema name

GROUP BY 

    t.tablespace_name, t.owner

ORDER BY 

    size_gb DESC;

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

DECLARE

    v_schema_name VARCHAR2(30) := 'YOUR_SCHEMA_NAME';  -- Replace with your schema name

    v_sql VARCHAR2(4000);

    v_count NUMBER;

BEGIN

    DBMS_OUTPUT.PUT_LINE('TABLE_NAME                          | ROW_COUNT');

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

    FOR rec IN (SELECT table_name 

                FROM dba_tables 

                WHERE owner = v_schema_name

                ORDER BY table_name)

    LOOP

        v_sql := 'SELECT COUNT(*) FROM ' || v_schema_name || '.' || rec.table_name;

        EXECUTE IMMEDIATE v_sql INTO v_count;

        DBMS_OUTPUT.PUT_LINE(RPAD(rec.table_name, 35) || '| ' || v_count);

    END LOOP;

EXCEPTION

    WHEN OTHERS THEN

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

END;

/

SELECT p.table_owner AS schema_name, p.table_name, p.partition_name, p.high_value AS partition_range, -- For range partitions; shows the upper bound ROUND(SUM(s.bytes) / 1024 / 1024 / 1024, 2) AS approx_size_gb, -- Approximate size as a proxy if counts are expensive 'SELECT COUNT(*) FROM ' || p.table_owner || '.' || p.table_name || ' PARTITION (' || p.partition_name || ');' AS count_query -- Generate query for exact row count FROM dba_tab_partitions p JOIN dba_segments s ON p.table_owner = s.owner AND p.table_name = s.segment_name AND p.partition_name = s.partition_name WHERE p.table_owner = 'YOUR_SCHEMA_NAME' -- Replace with schema AND p.table_name = 'YOUR_TABLE_NAME' -- Replace with table GROUP BY p.table_owner, p.table_name, p.partition_name, p.high_value ORDER BY p.partition_position;

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

CREATE TABLE datapump_job_status (

    owner_name VARCHAR2(128),

    job_name VARCHAR2(128),

    state VARCHAR2(30),

    degree NUMBER,

    last_updated TIMESTAMP DEFAULT SYSTIMESTAMP,

    CONSTRAINT pk_datapump_job_status PRIMARY KEY (owner_name, job_name)

);

COMMENT ON TABLE datapump_job_status IS 'Stores status of Data Pump jobs';

COMMENT ON COLUMN datapump_job_status.owner_name IS 'Owner of the Data Pump job';

COMMENT ON COLUMN datapump_job_status.job_name IS 'Name of the Data Pump job';

COMMENT ON COLUMN datapump_job_status.state IS 'Current state of the job (e.g., EXECUTING, IDLING, COMPLETED)';

COMMENT ON COLUMN datapump_job_status.degree IS 'Parallelism degree of the job';

COMMENT ON COLUMN datapump_job_status.last_updated IS 'Timestamp of last status update';

INSERT INTO datapump_job_status (owner_name, job_name, state, degree)

VALUES ('RDSADM', 'SYS_IMPORT_05', 'EXECUTING', 16);

COMMIT;

===

DECLARE

  v_handle NUMBER;

  v_job_state VARCHAR2(30);

BEGIN

  -- Attach to the job

  v_handle := DBMS_DATAPUMP.ATTACH(job_name => 'FULL_EXPORT_HEMANT');

  DBMS_OUTPUT.PUT_LINE('Attached to job: FULL_EXPORT_HEMANT');

  -- Get current job state

  DBMS_DATAPUMP.GET_STATUS(

    handle => v_handle,

    mask => DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS,

    timeout => 0,

    job_state => v_job_state

  );

  DBMS_OUTPUT.PUT_LINE('Current job state: ' || v_job_state);

  -- Stop the job (graceful stop)

  DBMS_DATAPUMP.STOP_JOB(

    handle => v_handle,

    immediate => 0, -- 0 for graceful, 1 for immediate

    keep_master => 0 -- 0 to drop master table, 1 to keep it

  );

  DBMS_OUTPUT.PUT_LINE('Job stop requested');

  -- Detach from the job

  DBMS_DATAPUMP.DETACH(v_handle);

EXCEPTION

  WHEN OTHERS THEN

    IF v_handle != 0 THEN

      DBMS_DATAPUMP.DETACH(v_handle);

    END IF;

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

    RAISE;

END;

/

=====

SELECT 'DROP TABLE "' || owner || '"."' || segment_name || '" CASCADE CONSTRAINTS /*+ PARALLEL(32) */;' AS drop_statement

FROM dba_segments

WHERE segment_type = 'TABLE'

AND owner IN ('SCOTT', 'HR', 'TEST') -- Replace with your schema list

ORDER BY owner, segment_name;

```

=======

BEGIN

  FOR ts IN (

    SELECT tablespace_name

    FROM dba_tablespaces

    WHERE tablespace_name NOT IN ('RDSADMIN', 'RDS_SQL_TS', 'RDS-TEMP_SQLT_TS', 

                                  'SYSAUX', 'SYSTEM', 'TEMP', 'UNDO_T1', 'USERS')

  ) LOOP

    EXECUTE IMMEDIATE 'DROP TABLESPACE ' || ts.tablespace_name || 

                      ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';

  END LOOP;

END;

/

=======

DECLARE
  v_ts_count NUMBER;
  v_job_name VARCHAR2(100) := 'DROP_TABLESPACES_JOB_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');
BEGIN
  -- Step 1: Verify the count of tablespaces to drop
  SELECT COUNT(*)
  INTO v_ts_count
  FROM dba_tablespaces
  WHERE tablespace_name NOT IN ('RDSADMIN', 'RDS_SQL_TS', 'RDS-TEMP_SQLT_TS', 
                                'SYSAUX', 'SYSTEM', 'TEMP', 'UNDO_T1', 'USERS');
  IF v_ts_count != 12 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Expected 12 tablespaces to drop, but found ' || v_ts_count);
  END IF;
  -- Step 2: Create a logging table if it doesn't exist
  BEGIN
    EXECUTE IMMEDIATE '
      CREATE TABLE drop_ts_log (
        log_id NUMBER GENERATED ALWAYS AS IDENTITY,
        tablespace_name VARCHAR2(128),
        status VARCHAR2(20),
        message VARCHAR2(4000),
        log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT pk_drop_ts_log PRIMARY KEY (log_id)
      )';
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN NULL; -- Table already exists
      ELSE RAISE;
      END IF;
  END;
  -- Step 3: Create a scheduler job to drop tablespaces
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => v_job_name,
    job_type   => 'PLSQL_BLOCK',
    job_action => q'[
      DECLARE
        v_sql VARCHAR2(4000);
      BEGIN
        -- Log start of job
        INSERT INTO drop_ts_log (tablespace_name, status, message)
        VALUES ('NONE', 'START', 'Starting tablespace drop job');
        -- Loop through tablespaces to drop
        FOR ts IN (
          SELECT tablespace_name
          FROM dba_tablespaces
          WHERE tablespace_name NOT IN ('RDSADMIN', 'RDS_SQL_TS', 'RDS-TEMP_SQLT_TS', 
                                        'SYSAUX', 'SYSTEM', 'TEMP', 'UNDO_T1', 'USERS')
        ) LOOP
          BEGIN
            v_sql := 'DROP TABLESPACE ' || DBMS_ASSERT.SQL_OBJECT_NAME(ts.tablespace_name) || 
                     ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
            EXECUTE IMMEDIATE v_sql;
            INSERT INTO drop_ts_log (tablespace_name, status, message)
            VALUES (ts.tablespace_name, 'SUCCESS', 'Tablespace dropped successfully');
          EXCEPTION
            WHEN OTHERS THEN
              INSERT INTO drop_ts_log (tablespace_name, status, message)
              VALUES (ts.tablespace_name, 'ERROR', 'Failed to drop: ' || SQLERRM);
          END;
        END LOOP;
        -- Log completion
        INSERT INTO drop_ts_log (tablespace_name, status, message)
        VALUES ('NONE', 'COMPLETED', 'Tablespace drop job completed');
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          INSERT INTO drop_ts_log (tablespace_name, status, message)
          VALUES ('NONE', 'ERROR', 'Job failed: ' || SQLERRM);
          COMMIT;
          RAISE;
      END;
    ]',
    start_date => SYSTIMESTAMP,
    enabled    => TRUE,
    auto_drop  => TRUE,
    comments   => 'Job to drop all non-system tablespaces except specified ones'
  );
  -- Log job submission
  INSERT INTO drop_ts_log (tablespace_name, status, message)
  VALUES ('NONE', 'SUBMITTED', 'Job ' || v_job_name || ' submitted successfully');
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Job ' || v_job_name || ' submitted. Check drop_ts_log table for progress.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/

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;

tbl

 

SELECT 'TABLE' AS object_type, table_name AS object_name, tablespace_name

FROM user_tables

WHERE tablespace_name = 'USERS'

UNION ALL

SELECT 'INDEX' AS object_type, index_name AS object_name, tablespace_name

FROM user_indexes

WHERE tablespace_name = 'USERS'

ORDER BY object_type, object_name, tablespace_name;

SELECT 'TABLE' AS object_type, table_name AS object_name, tablespace_name

FROM user_tables

UNION ALL

SELECT 'INDEX' AS object_type, index_name AS object_name, tablespace_name

FROM user_indexes

ORDER BY object_type, object_name, tablespace_name;

DECLARE

  v_app_tablespace VARCHAR2(30) := 'APP_TBS'; -- Replace with your target tablespace name

  v_sql VARCHAR2(4000);

BEGIN

  -- Generate statements for tables in USERS tablespace

  DBMS_OUTPUT.PUT_LINE('-- Move Tables:');

  FOR rec IN (

    SELECT owner, table_name 

    FROM all_tables 

    WHERE tablespace_name = 'USERS'

      AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'ORDSYS', 'MDSYS')

  ) 

  LOOP

    v_sql := 'ALTER TABLE ' || DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '.' || 

             DBMS_ASSERT.SQL_OBJECT_NAME(rec.table_name) || 

             ' MOVE TABLESPACE ' || DBMS_ASSERT.SQL_OBJECT_NAME(v_app_tablespace);

    DBMS_OUTPUT.PUT_LINE(v_sql || ';');

    -- Commented out execution

    -- EXECUTE IMMEDIATE v_sql;

    -- DBMS_OUTPUT.PUT_LINE('Moved table: ' || rec.owner || '.' || rec.table_name || ' to ' || v_app_tablespace);

  END LOOP;

  -- Generate statements for indexes in USERS tablespace

  DBMS_OUTPUT.PUT_LINE('-- Move Indexes:');

  FOR rec IN (

    SELECT owner, index_name, table_name 

    FROM all_indexes 

    WHERE tablespace_name = 'USERS'

      AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'ORDSYS', 'MDSYS')

  ) 

  LOOP

    v_sql := 'ALTER INDEX ' || DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '.' || 

             DBMS_ASSERT.SQL_OBJECT_NAME(rec.index_name) || 

             ' REBUILD TABLESPACE ' || DBMS_ASSERT.SQL_OBJECT_NAME(v_app_tablespace);

    DBMS_OUTPUT.PUT_LINE(v_sql || ';');

    -- Commented out execution

    -- EXECUTE IMMEDIATE v_sql;

    -- DBMS_OUTPUT.PUT_LINE('Rebuilt index: ' || rec.owner || '.' || rec.index_name || 

    --                      ' on table ' || rec.table_name || ' to ' || v_app_tablespace);

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

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

    RAISE;

END;

/

table count

DECLARE

  h1 NUMBER;

BEGIN

  h1 := DBMS_DATAPUMP.ATTACH('JOB_NAME_HERE','OWNER_NAME_HERE');

  DBMS_DATAPUMP.STOP_JOB (

    handle => h1, 

    immediate => 1, 

    abort => 1

  );

  DBMS_DATAPUMP.DETACH(h1);

END;

/

-- Create staging table for counts

CREATE TABLE staging_table_counts (

  owner        VARCHAR2(128),

  table_name   VARCHAR2(128),

  row_count    NUMBER,

  count_date   TIMESTAMP DEFAULT SYSTIMESTAMP

);

-- Create error log table

CREATE TABLE count_error_log (

  owner        VARCHAR2(128),

  table_name   VARCHAR2(128),

  error_msg    VARCHAR2(4000),

  error_time   TIMESTAMP DEFAULT SYSTIMESTAMP

);

-- Create or replace the batch processing procedure

CREATE OR REPLACE PROCEDURE calculate_table_counts_batch (

  p_batch_id        IN NUMBER,

  p_total_batches   IN NUMBER DEFAULT 20  -- ~189 tables/batch for 3775 tables

) AS

  CURSOR c_tables IS

    SELECT owner, table_name

    FROM (

      SELECT owner, table_name, 

             ROW_NUMBER() OVER (ORDER BY owner, table_name) AS rn

      FROM all_tables

      WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'ORDSYS', 'MDSYS')  -- Exclude system schemas

    )

    WHERE MOD(rn - 1, p_total_batches) = p_batch_id - 1;  -- Batch filter

  v_count   NUMBER;

  v_sql     VARCHAR2(4000);

BEGIN

  FOR rec IN c_tables LOOP

    BEGIN

      -- Dynamic SQL with 5-minute timeout and NO_PARALLEL hint

      v_sql := 'BEGIN DBMS_SESSION.SET_NLS(''query_timeout'', ''300''); ' ||

               'SELECT /*+ NO_PARALLEL */ COUNT(*) FROM "' || 

               DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '"."' || 

               DBMS_ASSERT.SQL_OBJECT_NAME(rec.table_name) || '"; END;';

      EXECUTE IMMEDIATE v_sql INTO v_count;

      -- Insert into staging table

      INSERT INTO staging_table_counts (owner, table_name, row_count)

      VALUES (rec.owner, rec.table_name, v_count);

      COMMIT;  -- Commit per table to avoid long transactions

      -- Log success

      DBMS_OUTPUT.PUT_LINE('Processed: ' || rec.owner || '.' || rec.table_name || ' | Count: ' || v_count);

    

    EXCEPTION

      WHEN OTHERS THEN

        -- Log error to table and continue

        INSERT INTO count_error_log (owner, table_name, error_msg)

        VALUES (rec.owner, rec.table_name, SQLERRM);

        COMMIT;

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

    END;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

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

    RAISE;

END;

/

recheck

-- Log row count before reset

DECLARE

  v_count NUMBER;

BEGIN

  SELECT COUNT(*)

  INTO v_count

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

  DBMS_OUTPUT.PUT_LINE('Rows to reset for DMS: ' || v_count);

END;

/

-- Reset start_time, end_time, and error_message for DMS run

UPDATE index_build

SET start_time = NULL,

    end_time = NULL,

    error_message = NULL

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

-- Verify reset

DECLARE

  v_count NUMBER;

BEGIN

  SELECT COUNT(*)

  INTO v_count

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

  AND (start_time IS NOT NULL OR end_time IS NOT NULL OR error_message IS NOT NULL);

  IF v_count != 0 THEN

    RAISE_APPLICATION_ERROR(-20003, 'Expected 0 rows with non-NULL start_time, end_time, or error_message, but found ' || v_count);

  END IF;

END;

/

-- Drop error_message column

ALTER TABLE index_build DROP COLUMN error_message;

-- Verify column dropped

DECLARE

  v_count NUMBER;

BEGIN

  SELECT COUNT(*)

  INTO v_count

  FROM dba_tab_columns

  WHERE table_name = 'INDEX_BUILD'

  AND column_name = 'ERROR_MESSAGE';

  IF v_count != 0 THEN

    RAISE_APPLICATION_ERROR(-20004, 'Expected error_message column to be dropped, but it still exists');

  END IF;

END;

/

-- Commit changes

COMMIT;

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

WITH index_build_data AS (

    SELECT DISTINCT owner, index_name

    FROM index_build

    WHERE build_status = 'need_build'  -- Adjust if you want all rows

),

dba_index_data AS (

    SELECT DISTINCT owner, index_name

    FROM dba_indexes

    WHERE owner NOT IN ('SYS', 'SYSTEM')  -- Exclude system schemas; adjust as needed

),

comparison AS (

    SELECT 

        COALESCE(ib.owner, di.owner) AS owner,

        COALESCE(ib.index_name, di.index_name) AS index_name,

        CASE 

            WHEN ib.index_name IS NOT NULL AND di.index_name IS NOT NULL THEN 'Match'

            WHEN ib.index_name IS NOT NULL AND di.index_name IS NULL THEN 'In index_build only'

            WHEN ib.index_name IS NULL AND di.index_name IS NOT NULL THEN 'In DBA_INDEXES only'

        END AS status

    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,

    status,

    COUNT(*) OVER (PARTITION BY owner, status) AS count_per_status

FROM comparison

ORDER BY owner, status, index_name;

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

WITH index_build_data AS (

    SELECT DISTINCT owner, index_name

    FROM index_build

    WHERE build_status = 'need_build'  -- Adjust if you want all rows

),

dba_index_data AS (

    SELECT DISTINCT owner, index_name

    FROM dba_indexes

    WHERE owner NOT IN ('SYS', 'SYSTEM')  -- Exclude system schemas; adjust as needed

),

comparison AS (

    SELECT 

        COALESCE(ib.owner, di.owner) AS owner,

        COALESCE(ib.index_name, di.index_name) AS index_name,

        CASE 

            WHEN ib.index_name IS NOT NULL AND di.index_name IS NOT NULL THEN 'Match'

            WHEN ib.index_name IS NOT NULL AND di.index_name IS NULL THEN 'In index_build only (potential invalid index)'

            WHEN ib.index_name IS NULL AND di.index_name IS NOT NULL THEN 'In DBA_INDEXES only (potential missed index)'

        END AS status

    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,

    status,

    COUNT(*) OVER (PARTITION BY owner, status) AS count_per_status

FROM comparison

ORDER BY owner, status, index_name;

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

-- 1. List distinct tablespaces in index_build (for need_build)

SELECT DISTINCT tablespace_name

FROM index_build

WHERE build_status = 'need_build'

ORDER BY tablespace_name;

-- 2. Identify old tablespaces in index_build with no mapping in ts_mappings

SELECT DISTINCT ib.tablespace_name

FROM index_build ib

WHERE ib.build_status = 'need_build'

AND ib.tablespace_name NOT IN (SELECT old_tablespace FROM ts_mappings)

ORDER BY ib.tablespace_name;

-- 3. Preview the mapping (show old -> new for matching tablespaces)

SELECT ib.tablespace_name AS old_tablespace, tsm.new_tablespace

FROM index_build ib

JOIN ts_mappings tsm ON ib.tablespace_name = tsm.old_tablespace

WHERE ib.build_status = 'need_build'

GROUP BY ib.tablespace_name, tsm.new_tablespace

ORDER BY ib.tablespace_name;

-- 4. Count rows in index_build that will be updated vs. flagged as errors

SELECT 

    SUM(CASE WHEN tsm.old_tablespace IS NOT NULL THEN 1 ELSE 0 END) AS will_be_updated,

    SUM(CASE WHEN tsm.old_tablespace IS NULL THEN 1 ELSE 0 END) AS will_be_flagged_error

FROM index_build ib

LEFT JOIN ts_mappings tsm ON ib.tablespace_name = tsm.old_tablespace

WHERE ib.build_status = 'need_build';

SELECT new_tablespace

FROM ts_mappings

WHERE new_tablespace NOT IN (SELECT tablespace_name FROM DBA_TABLESPACES)

ORDER BY new_tablespace;

-- Add error_message column if it doesn't exist

ALTER TABLE index_build ADD (error_message VARCHAR2(4000));

-- Clear prior errors for need_build rows

UPDATE index_build SET error_message = NULL WHERE build_status = 'need_build';

-- Merge to update tablespace_name

MERGE INTO index_build ib

USING ts_mappings tsm

ON (ib.tablespace_name = tsm.old_tablespace AND ib.build_status = 'need_build')

WHEN MATCHED THEN

    UPDATE SET

        ib.tablespace_name = tsm.new_tablespace,

        ib.error_message = NULL  -- Clear error if matched

WHEN NOT MATCHED THEN

    UPDATE SET

        ib.build_status = 'error_no_mapping',

        ib.error_message = 'No mapping found for old tablespace: ' || ib.tablespace_name;

-- Commit

COMMIT;

-- Check for errors post-MERGE

SELECT COUNT(*) AS error_count, error_message

FROM index_build

WHERE build_status = 'error_no_mapping'

GROUP BY error_message;

==========

Handling Issues Found in Checks

• If Query 2 shows missing mappings:
  • Add the missing old_tablespace → new_tablespace pairs to ts_mappings:
    sql

    INSERT INTO ts_mappings (old_tablespace, new_tablespace)
    VALUES ('OLD_TBS_NAME', 'NEW_TBS_NAME');
  • Alternatively, exclude those indexes from rebuilding by updating their status:
    sql

    UPDATE index_build
    SET build_status = 'skipped_no_mapping',
        error_message = 'No tablespace mapping available'
    WHERE tablespace_name NOT IN (SELECT old_tablespace FROM ts_mappings)
    AND build_status = 'need_build';
• If Query 3 shows incorrect new tablespaces:
  • Update ts_mappings to correct the new_tablespace values:
    sql

    UPDATE ts_mappings
    SET new_tablespace = 'CORRECT_TBS_NAME'
    WHERE old_tablespace = 'OLD_TBS_NAME';
• If Query 4 shows many errors:
  • Investigate why so many index_build rows lack mappings. It could indicate incomplete ts_mappings data or that some indexes don’t need tablespace changes (in which case, you might adjust the MERGE to skip them).

Step 5: Next Steps After MERGE

After the MERGE, you can proceed with the validation against DBA_INDEXES and DBA_IND_COLUMNS (as in the original response) to ensure index definitions match the database before rebuilding. Here’s a quick recap of that step:

sql

-- Example validation (simplified; see original response for full PL/SQL)
SELECT ib.index_name, ib.table_name, ib.column_names, di.index_type, di.uniqueness
FROM index_build ib
JOIN DBA_INDEXES di ON ib.owner = di.owner AND ib.index_name = di.index_name
WHERE ib.build_status = 'need_build';

Additional Notes

• Why need_build?: The condition ensures you’re only updating tablespaces for indexes flagged for DMS-related rebuilds. If you want to check/update all rows in index_build (not just need_build), remove the AND ib.build_status = 'need_build' condition from the MERGE’s ON clause and the UPDATE for clearing errors.
• Test First: Run the mapping checks and MERGE on a small subset (e.g., add WHERE ROWNUM <= 10 to queries) in a test environment to confirm behavior.
• DMS Context: If need_build isn’t the right filter or you have a different flag (e.g., dms_job IS NOT NULL), let me know, and I can adjust the queries.

If you want to see sample output from the mapping checks or need help fixing specific issues (e.g., missing mappings), share the results of the above queries or more details about your DMS process, and I’ll tailor the solution further!

-- Update matched rows UPDATE index_build ib SET ib.tablespace_name = (SELECT tsm.new_tablespace FROM ts_mappings tsm WHERE tsm.old_tablespace = ib.tablespace_name), ib.error_message = NULL WHERE ib.build_status = 'need_build' AND EXISTS (SELECT 1 FROM ts_mappings tsm WHERE tsm.old_tablespace = ib.tablespace_name); -- Update non-matched rows UPDATE index_build ib SET ib.build_status = 'error_no_mapping', ib.error_message = 'No mapping found for old tablespace: ' || ib.tablespace_name WHERE ib.build_status = 'need_build' AND NOT EXISTS (SELECT 1 FROM ts_mappings tsm WHERE tsm.old_tablespace = ib.tablespace_name); COMMIT;

-- Verify index_build against DBA_INDEXES and DBA_IND_COLUMNS WITH index_build_data AS ( SELECT owner, index_name, table_name, column_names, index_type, uniqueness, partitioned, degree, ini_trans, pct_free, build_status, error_message FROM index_build WHERE build_status = 'need_build' -- Adjust if you want all rows ), dba_index_data AS ( SELECT di.owner, di.index_name, di.table_name, di.index_type, di.uniqueness, di.partitioned, di.degree, di.ini_trans, di.pct_free, 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 GROUP BY di.owner, di.index_name, di.table_name, di.index_type, di.uniqueness, di.partitioned, di.degree, di.ini_trans, di.pct_free ), comparison AS ( SELECT ib.owner, ib.index_name, ib.table_name, ib.build_status, -- Compare attributes and build error messages CASE WHEN ib.index_name IS NULL OR di.index_name IS NULL THEN 'Index not found in DBA_INDEXES' WHEN ib.table_name != di.table_name THEN 'Table name mismatch: index_build=' || ib.table_name || ', DBA=' || di.table_name WHEN ib.index_type != di.index_type THEN 'Index type mismatch: index_build=' || ib.index_type || ', DBA=' || di.index_type WHEN ib.uniqueness != di.uniqueness THEN 'Uniqueness mismatch: index_build=' || ib.uniqueness || ', DBA=' || di.uniqueness WHEN ib.partitioned != di.partitioned THEN 'Partitioned mismatch: index_build=' || ib.partitioned || ', DBA=' || di.partitioned WHEN ib.degree != di.degree THEN 'Degree mismatch: index_build=' || ib.degree || ', DBA=' || di.degree WHEN ib.ini_trans != di.ini_trans THEN 'Ini_trans mismatch: index_build=' || ib.ini_trans || ', DBA=' || di.ini_trans WHEN ib.pct_free != di.pct_free THEN 'Pct_free mismatch: index_build=' || ib.pct_free || ', DBA=' || di.pct_free WHEN ib.column_names != di.dba_column_names THEN 'Column names mismatch: index_build=' || ib.column_names || ', DBA=' || di.dba_column_names ELSE NULL END AS mismatch_reason, 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, ib.partitioned AS ib_partitioned, di.partitioned AS dba_partitioned, ib.degree AS ib_degree, di.degree AS dba_degree, ib.ini_trans AS ib_ini_trans, di.ini_trans AS dba_ini_trans, ib.pct_free AS ib_pct_free, di.pct_free AS dba_pct_free 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, table_name, build_status, mismatch_reason, ib_column_names, dba_column_names, ib_index_type, dba_index_type, ib_uniqueness, dba_uniqueness, ib_partitioned, dba_partitioned, ib_degree, dba_degree, ib_ini_trans, dba_ini_trans, ib_pct_free, dba_pct_free FROM comparison WHERE mismatch_reason IS NOT NULL ORDER BY owner, index_name;

compare

```sql

SET LINESIZE 32767

SET PAGESIZE 0

SET FEEDBACK OFF

SET ECHO OFF

SET SERVEROUTPUT ON SIZE UNLIMITED

SET TERMOUT ON

SET TRIMSPOOL ON

SET TRIMOUT ON

-- Hardcode schema list and output directory

DEFINE schema_list = 'SCHEMA1,SCHEMA2,SCHEMA3'

DEFINE output_dir = 'C:\Reports\'

-- Spool to local directory (may not work in Toad; use Script Output export instead)

SPOOL "&output_dir.schema_compare_report_&SYSDATE..csv"

-- Report Header (as comments in CSV)

PROMPT # Database Schema Comparison Report

PROMPT # Generated on: &SYSDATE

PROMPT # Database: &DATABASE_NAME

PROMPT # Schemas: &schema_list

PROMPT # Overall object counts per schema for tables, indexes, constraints, sequences, etc.

-- CSV Header

PROMPT "Schema","Object Type","Count"

-- Anonymous PL/SQL Block

BEGIN

    -- Declare variables

    FOR schema_rec IN (

        SELECT UPPER(TRIM(REGEXP_SUBSTR('&schema_list', '[^,]+', 1, LEVEL))) AS schema_name

        FROM dual

        CONNECT BY LEVEL <= REGEXP_COUNT('&schema_list', ',') + 1

    ) LOOP

        IF schema_rec.schema_name IS NOT NULL THEN

            BEGIN

                -- Object counts from dba_objects (TABLE, INDEX, SEQUENCE, PROCEDURE, PACKAGE, SYNONYM, etc.)

                FOR obj IN (

                    SELECT object_type, COUNT(*) AS object_count

                    FROM dba_objects

                    WHERE owner = schema_rec.schema_name

                    GROUP BY object_type

                    ORDER BY object_type

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","' || obj.object_type || '","' || obj.object_count || '"');

                END LOOP;

                -- Constraint counts from dba_constraints

                DECLARE

                    v_constraint_count NUMBER;

                BEGIN

                    SELECT COUNT(*) INTO v_constraint_count

                    FROM dba_constraints

                    WHERE owner = schema_rec.schema_name;

                    DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","' || v_constraint_count || '"');

                EXCEPTION

                    WHEN NO_DATA_FOUND THEN

                        DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","0"');

                END;

            EXCEPTION

                WHEN OTHERS THEN

                    DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","ERROR","' || REPLACE(SQLERRM, '"', '""') || '"');

            END;

        END IF;

    END LOOP;

END;

/

SPOOL OFF

PROMPT Report generated: &output_dir.schema_compare_report_&SYSDATE..csv

PROMPT To save as CSV in Toad, right-click the Script Output, select 'Export Dataset', choose CSV format, and save to your local directory (e.g., &output_dir).

```

### Key Changes

1. **Anonymous PL/SQL Block**:

   - Replaced the `UNION ALL` plain SQL queries with an anonymous PL/SQL block using `DBMS_OUTPUT` to generate CSV rows.

   - Iterates over schemas using a cursor and outputs counts for each object type.

2. **Simplified Output**:

   - Reports only the **overall count** per schema for:

     - Object types from `dba_objects` (e.g., TABLE, INDEX, SEQUENCE, PROCEDURE, PACKAGE, SYNONYM).

     - CONSTRAINT (total count from `dba_constraints`, not broken down by type).

   - Excludes detailed breakdowns (e.g., tablespace usage, constraint types, invalid objects) for simplicity.

   - CSV format: `Schema,Object Type,Count` (e.g., `"SCHEMA1","TABLE","50"`).

3. **Hardcoded Values**:

   - Schema list: `DEFINE schema_list = 'SCHEMA1,SCHEMA2,SCHEMA3'` (edit to your schemas).

   - Output directory: `DEFINE output_dir = 'C:\Reports\'` (edit to your directory).

4. **Constraints**:

   - Included a single count of all constraints per schema from `dba_constraints`, as you questioned their inclusion but they were part of your earlier requirements.

   - If you don’t want constraints, comment out or delete the constraint block:

     ```sql

     -- DECLARE

     --     v_constraint_count NUMBER;

     -- BEGIN

     --     SELECT COUNT(*) INTO v_constraint_count

     --     FROM dba_constraints

     --     WHERE owner = schema_rec.schema_name;

     --     DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","' || v_constraint_count || '"');

     -- EXCEPTION

     --     WHEN NO_DATA_FOUND THEN

     --         DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","0"');

     -- END;

     ```

5. **Toad Compatibility**:

   - Outputs via `DBMS_OUTPUT` to Toad’s Script Output tab.

   - Includes instructions to export from Script Output to CSV, as Toad may not write `SPOOL` files reliably.

6. **Excel-Friendly**:

   - CSV headers: `Schema,Object Type,Count`.

   - Fields are quoted to handle special characters, ensuring Excel parses columns correctly.

   - No Percentage column, as you requested a plain count for simplicity.

### Updated PowerShell Comparison Script

The PowerShell script needs to be updated to handle the simplified CSV output (Schema, Object Type, Count). Below is the modified script to compare the source and target CSV files and produce a comparison CSV for Excel.

<xaiArtifact artifact_id="345c5809-7506-4a03-8f77-8d92775ad9a0" artifact_version_id="07826d33-73a3-4507-84af-bfa3e4acd1cb" title="Compare-SchemaReports.ps1" contentType="text/powershell">

```powershell

<#

.SYNOPSIS

    Compares two Oracle schema comparison report CSV files and generates a difference report in CSV format.

.DESCRIPTION

    This script parses two CSV files generated by schema_compare_report_toad.sql, compares object counts

    for each schema and object type, and outputs differences in a CSV file for Excel.

.PARAMETER SourceFile

    Path to the source database report (.csv).

.PARAMETER TargetFile

    Path to the target database report (.csv).

.PARAMETER OutputFile

    Path to save the comparison report (optional, defaults to schema_comparison_diff

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

```sql

SET LINESIZE 32767

SET PAGESIZE 0

SET FEEDBACK OFF

SET ECHO OFF

SET SERVEROUTPUT ON SIZE UNLIMITED

SET TERMOUT ON

SET TRIMSPOOL ON

SET TRIMOUT ON

-- Prompt for schema names once as a comma-separated list

ACCEPT schema_list PROMPT 'Enter schema names (comma-separated, up to 7, e.g., SCHEMA1,SCHEMA2,SCHEMA3): '

-- Prompt for output directory (Windows path, e.g., C:\Reports\)

ACCEPT output_dir PROMPT 'Enter local output directory (e.g., C:\Reports\): '

-- Attempt to spool to local directory (may not work in Toad)

SPOOL "&output_dir.schema_compare_report_&SYSDATE..csv"

-- Report Header (as a comment in CSV)

PROMPT # Database Schema Comparison Report

PROMPT # Generated on: &SYSDATE

PROMPT # Database: &DATABASE_NAME

PROMPT # Schemas: &schema_list

PROMPT # Run this report on both Source and Target databases for comparison

-- Summary of Object Counts by Type across all schemas

PROMPT "Section","Schema","Object Type","Status","Object Count","Percentage"

SELECT 

    'Summary' AS "Section",

    owner AS "Schema",

    object_type AS "Object Type",

    status AS "Status",

    COUNT(*) AS "Object Count",

    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY owner), 2) AS "Percentage"

FROM dba_objects

WHERE owner IN (

    SELECT UPPER(TRIM(REGEXP_SUBSTR('&schema_list', '[^,]+', 1, LEVEL)))

    FROM dual

    CONNECT BY LEVEL <= REGEXP_COUNT('&schema_list', ',') + 1

)

GROUP BY owner, object_type, status

ORDER BY owner, object_type, status;

-- Detailed Report per Schema

DECLARE

    v_schema VARCHAR2(30);

    v_error_count NUMBER := 0;

    v_error_message VARCHAR2(4000);

    CURSOR schema_cursor IS

        SELECT UPPER(TRIM(REGEXP_SUBSTR('&schema_list', '[^,]+', 1, LEVEL))) AS schema_name

        FROM dual

        CONNECT BY LEVEL <= REGEXP_COUNT('&schema_list', ',') + 1;

BEGIN

    -- Enable DBMS_OUTPUT

    DBMS_OUTPUT.ENABLE(NULL);

    

    -- Object Type Counts

    FOR rec IN schema_cursor LOOP

        v_schema := rec.schema_name;

        IF v_schema IS NOT NULL THEN

            BEGIN

                DBMS_OUTPUT.PUT_LINE('Objects,' || v_schema || ',Object Type,Count');

                FOR obj IN (

                    SELECT object_type, COUNT(*) AS object_count

                    FROM dba_objects

                    WHERE owner = v_schema

                    GROUP BY object_type

                    ORDER BY object_type

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE('Objects,' || v_schema || ',' || obj.object_type || ',' || obj.object_count);

                END LOOP;

                

                -- Tablespace Usage for Tables

                DBMS_OUTPUT.PUT_LINE('Tablespace_Tables,' || v_schema || ',Tablespace,Count');

                FOR tbl IN (

                    SELECT tablespace_name, COUNT(*) AS table_count

                    FROM dba_tables

                    WHERE owner = v_schema

                    GROUP BY tablespace_name

                    ORDER BY tablespace_name

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE('Tablespace_Tables,' || v_schema || ',' || NVL(tbl.tablespace_name, 'N/A') || ',' || tbl.table_count);

                END LOOP;

                

                -- Tablespace Usage for Indexes

                DBMS_OUTPUT.PUT_LINE('Tablespace_Indexes,' || v_schema || ',Tablespace,Count');

                FOR idx IN (

                    SELECT tablespace_name, COUNT(*) AS index_count

                    FROM dba_indexes

                    WHERE owner = v_schema

                    GROUP BY tablespace_name

                    ORDER BY tablespace_name

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE('Tablespace_Indexes,' || v_schema || ',' || NVL(idx.tablespace_name, 'N/A') || ',' || idx.index_count);

                END LOOP;

                

                -- Constraints by Type

                DBMS_OUTPUT.PUT_LINE('Constraints,' || v_schema || ',Constraint Type,Count');

                FOR cons IN (

                    SELECT 

                        CASE constraint_type

                            WHEN 'C' THEN 'Check'

                            WHEN 'P' THEN 'Primary Key'

                            WHEN 'U' THEN 'Unique'

                            WHEN 'R' THEN 'Foreign Key'

                            WHEN 'V' THEN 'View Check'

                            WHEN 'O' THEN 'Read Only View'

                            ELSE constraint_type

                        END AS constraint_type, 

                        COUNT(*) AS constraint_count

                    FROM dba_constraints

                    WHERE owner = v_schema

                    GROUP BY constraint_type

                    ORDER BY constraint_type

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE('Constraints,' || v_schema || ',' || cons.constraint_type || ',' || cons.constraint_count);

                END LOOP;

                

                -- Invalid Objects

                DBMS_OUTPUT.PUT_LINE('Invalid_Objects,' || v_schema || ',Object Type,Object Name');

                FOR inv IN (

                    SELECT object_name, object_type

                    FROM dba_objects

                    WHERE owner = v_schema AND status != 'VALID'

                    ORDER BY object_type, object_name

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE('Invalid_Objects,' || v_schema || ',' || inv.object_type || ',' || inv.object_name);

                END LOOP;

                

            EXCEPTION

                WHEN OTHERS THEN

                    v_error_count := v_error_count + 1;

                    v_error_message := SQLERRM;

                    DBMS_OUTPUT.PUT_LINE('Errors,' || v_schema || ',Error Message,' || REPLACE(v_error_message, ',', '||'));

            END;

        END IF;

    END LOOP;

    

    -- Summary of Errors

    IF v_error_count > 0 THEN

        DBMS_OUTPUT.PUT_LINE('Errors,All Schemas,Total Errors,' || v_error_count);

    END IF;

END;

/

SPOOL OFF

PROMPT Report generated: &output_dir.schema_compare_report_&SYSDATE..csv

PROMPT To save as CSV in Toad, right-click the grid, select 'Export Dataset', choose CSV format, and save to your local directory (e.g., &output_dir).

```

### Changes in the SQL Script

1. **Toad Compatibility**:

   - Removed quoting around fields in the output to simplify CSV export from Toad’s grid, as Toad handles quoting automatically when exporting.

   - Added `SET TRIMSPOOL ON` and `SET TRIMOUT ON` to remove trailing spaces, ensuring clean CSV output.

   - Kept `SPOOL` as a fallback, but added a `PROMPT` instructing users to export from Toad’s grid if `SPOOL` fails.

2. **Output Directory Prompt**:

   - Retained `ACCEPT output_dir` to specify the local directory (e.g., `C:\Reports\`).

   - The `SPOOL` command uses this path, but Toad may ignore it, so we rely on manual export if needed.

3. **CSV Format**:

   - Output is formatted as comma-separated values without quotes (Toad’s export adds quotes as needed).

   - Sections are clearly labeled (e.g., `Summary`, `Objects`) for Excel compatibility.

4. **Error Handling**:

   - Errors are logged in the `Errors` section, with commas in error messages replaced by `||` to avoid CSV parsing issues.

5. **Instructions for Toad**:

   - Added a `PROMPT` at the end guiding users to export the results from Toad’s grid to a CSV file.

### Steps to Run in Toad

1. **Set Up Toad**:

   - Ensure Toad for Oracle is installed and configured to connect to your Oracle RDS instances (source and target).

   - Add the RDS connection:

     - In Toad, go to `Database > New Connection`.

     - Enter the RDS endpoint (e.g., `mydb.123456789012.us-east-1.rds.amazonaws.com:1521/ORCL`), username, and password.

     - Test the connection to confirm it works.

2. **Create Local Directory**:

   - Create a directory on your Windows machine (e.g., `C:\Reports`):

     ```cmd

     mkdir C:\Reports

     ```

3. **Run the Script in Toad**:

   - Save the script as `C:\Scripts\schema_compare_report_toad.sql`.

   - Open Toad’s **SQL Editor**:

     - Go to `Database > SQL Editor`.

     - Load the script: `File > Open > C:\Scripts\schema_compare_report_toad.sql`.

   - Execute the script:

     - Press `F5` (Run as Script) or click the “Run as Script” button.

     - Respond to prompts:

       ```

       Enter schema names (comma-separated, up to 7, e.g., SCHEMA1,SCHEMA2,SCHEMA3): SCHEMA1,SCHEMA2

       Enter local output directory (e.g., C:\Reports\): C:\Reports\

       ```

   - **Check Output**:

     - If `SPOOL` works in your Toad version, check `C:\Reports\schema_compare_report_YYYYMMDD_HHMMSS.csv`.

     - If no file is written (common in Toad), the results will appear in the **Script Output** tab or **Data Grid**.

4. **Export to CSV from Toad**:

   - If `SPOOL` doesn’t write the file:

     - In the **Script Output** tab or **Data Grid**, right-click the results.

     - Select `Export Dataset`.

     - Choose:

       - **Format**: CSV

       - **File**: `C:\Reports\schema_compare_report_SOURCE.csv` (or `TARGET.csv` for the target database)

       - **Delimiter**: Comma

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

SET LINESIZE 200

SET PAGESIZE 50000

SET HEADING ON

SET FEEDBACK OFF

SET ECHO OFF

SET SERVEROUTPUT ON SIZE UNLIMITED

-- Define schema names

ACCEPT schema1 PROMPT 'Enter the name for Schema 1: '

ACCEPT schema2 PROMPT 'Enter the name for Schema 2: '

ACCEPT schema3 PROMPT 'Enter the name for Schema 3: '

ACCEPT schema4 PROMPT 'Enter the name for Schema 4: '

ACCEPT schema5 PROMPT 'Enter the name for Schema 5: '

ACCEPT schema6 PROMPT 'Enter the name for Schema 6: '

ACCEPT schema7 PROMPT 'Enter the name for Schema 7: '

-- Spool the output to a file

SPOOL schema_compare_report_&schema1._&SYSDATE..lst

-- Report Header

PROMPT ==============================================================================

PROMPT           Database Schema Comparison Report

PROMPT ==============================================================================

PROMPT Generated on: &SYSDATE

PROMPT Database: &DATABASE_NAME

PROMPT Run this report on both Source and Target databases for comparison

PROMPT ==============================================================================

-- Summary of Object Counts by Type across all schemas

PROMPT 

PROMPT --- 1. SUMMARY OF OBJECT COUNTS BY SCHEMA AND TYPE ---

PROMPT 

SELECT 

    owner AS "Schema",

    object_type AS "Object Type",

    status AS "Status",

    COUNT(*) AS "Object Count",

    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY owner), 2) AS "Percentage"

FROM dba_objects

WHERE owner IN (

    UPPER('&schema1.'), UPPER('&schema2.'), UPPER('&schema3.'),

    UPPER('&schema4.'), UPPER('&schema5.'), UPPER('&schema6.'), UPPER('&schema7.')

)

GROUP BY owner, object_type, status

ORDER BY owner, object_type, status;

-- Detailed Report per Schema

PROMPT 

PROMPT --- 2. DETAILED OBJECT REPORT PER SCHEMA ---

PROMPT 

DECLARE

    v_schema VARCHAR2(30);

    TYPE schema_array_t IS TABLE OF VARCHAR2(30);

    v_schemas schema_array_t := schema_array_t(

        UPPER('&schema1.'), UPPER('&schema2.'), UPPER('&schema3.'),

        UPPER('&schema4.'), UPPER('&schema5.'), UPPER('&schema6.'), UPPER('&schema7.')

    );

    v_error_count NUMBER := 0;

BEGIN

    -- Enable DBMS_OUTPUT

    DBMS_OUTPUT.ENABLE(NULL);

    

    FOR i IN 1..v_schemas.COUNT LOOP

        v_schema := v_schemas(i);

        IF v_schema IS NOT NULL THEN

            BEGIN

                DBMS_OUTPUT.PUT_LINE(' ');

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

                DBMS_OUTPUT.PUT_LINE('--- SCHEMA: ' || v_schema || ' ---');

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

                

                -- Object Type Counts

                DBMS_OUTPUT.PUT_LINE(RPAD('Object Type', 25) || RPAD('Count', 10));

                DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || RPAD('-', 10, '-'));

                FOR rec IN (

                    SELECT object_type, COUNT(*) AS object_count

                    FROM dba_objects

                    WHERE owner = v_schema

                    GROUP BY object_type

                    ORDER BY object_type

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE(RPAD(rec.object_type, 25) || RPAD(rec.object_count, 10));

                END LOOP;

                

                -- Tablespace Usage for Tables

                DBMS_OUTPUT.PUT_LINE(' ');

                DBMS_OUTPUT.PUT_LINE('Tablespace Usage (Tables)');

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

                FOR rec IN (

                    SELECT tablespace_name, COUNT(*) AS table_count

                    FROM dba_tables

                    WHERE owner = v_schema

                    GROUP BY tablespace_name

                    ORDER BY tablespace_name

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE(RPAD(NVL(rec.tablespace_name, 'N/A'), 25) || RPAD(rec.table_count, 10));

                END LOOP;

                

                -- Tablespace Usage for Indexes

                DBMS_OUTPUT.PUT_LINE(' ');

                DBMS_OUTPUT.PUT_LINE('Tablespace Usage (Indexes)');

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

                FOR rec IN (

                    SELECT tablespace_name, COUNT(*) AS index_count

                    FROM dba_indexes

                    WHERE owner = v_schema

                    GROUP BY tablespace_name

                    ORDER BY tablespace_name

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE(RPAD(NVL(rec.tablespace_name, 'N/A'), 25) || RPAD(rec.index_count, 10));

                END LOOP;

                

                -- Constraints by Type

                DBMS_OUTPUT.PUT_LINE(' ');

                DBMS_OUTPUT.PUT_LINE('Constraints by Type');

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

                FOR rec IN (

                    SELECT 

                        CASE constraint_type

                            WHEN 'C' THEN 'Check'

                            WHEN 'P' THEN 'Primary Key'

                            WHEN 'U' THEN 'Unique'

                            WHEN 'R' THEN 'Foreign Key'

                            WHEN 'V' THEN 'View Check'

                            WHEN 'O' THEN 'Read Only View'

                            ELSE constraint_type

                        END AS constraint_type, 

                        COUNT(*) AS constraint_count

                    FROM dba_constraints

                    WHERE owner = v_schema

                    GROUP BY constraint_type

                    ORDER BY constraint_type

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE(RPAD(rec.constraint_type, 25) || RPAD(rec.constraint_count, 10));

                END LOOP;

                

                -- Invalid Objects

                DBMS_OUTPUT.PUT_LINE(' ');

                DBMS_OUTPUT.PUT_LINE('Invalid Objects');

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

                FOR rec IN (

                    SELECT object_name, object_type

                    FROM dba_objects

                    WHERE owner = v_schema AND status != 'VALID'

                    ORDER BY object_type, object_name

                ) LOOP

                    DBMS_OUTPUT.PUT_LINE(RPAD(rec.object_type, 25) || rec.object_name);

                END LOOP;

                

            EXCEPTION

                WHEN OTHERS THEN

                    v_error_count := v_error_count + 1;

                    DBMS_OUTPUT.PUT_LINE('Error processing schema ' || v_schema || ': ' || SQLERRM);

            END;

        END IF;

    END LOOP;

    

    -- Summary of Errors

    IF v_error_count > 0 THEN

        DBMS_OUTPUT.PUT_LINE(' ');

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

        DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_error_count || ' error(s) encountered during report generation');

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

    END IF;

END;

/

-- Report Footer

PROMPT 

PROMPT ==============================================================================

PROMPT End of Schema Comparison Report

PROMPT ==============================================================================

SPOOL OFF