all_objects

 

Here are the 3 individual, standardized SQL statements.

These use the "Best Practice" logic we established:

1. Composite Exclusion: Uses DATA_OBJECT_ID IS NOT NULL (Much faster/cleaner than joining ALL_TAB_PARTITIONS).

2. Subpartition Support: Automatically includes TABLE SUBPARTITION.

3. Safety: The Update statement includes the Old Values and Object Type to ensure you never update the wrong row.

1. The "Live Data" Query (Source)

Run this to get the New values from the database.

• Note: This replaces your complex UNION ALL query. It automatically filters out logical "Parent" partitions (Composite=YES) by ensuring DATA_OBJECT_ID exists.

SELECT 
    OBJECT_ID, 
    DATA_OBJECT_ID, 
    OBJECT_NAME       AS TABLE_NAME,
    SUBOBJECT_NAME    AS PARTITION_NAME, -- Holds Partition or Subpartition Name
    OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OWNER = 'YOUR_SCHEMA_NAME'       -- <1-- Change Owner
  AND OBJECT_NAME = 'YOUR_TABLE_NAME'  -- <2-- Change Table Name
  AND OBJECT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
  AND DATA_OBJECT_ID IS NOT NULL       -- <3-- This ensures COMPOSITE = 'NO'
ORDER BY 
    OBJECT_NAME, 
    OBJECT_TYPE, 
    SUBOBJECT_NAME;

2. The "Captured Data" Query (Target)

Run this to see what you currently have stored (the Old values).

SELECT * FROM DB_OBJECT_ID_CAPTURED 

WHERE PUR_ID = 279 

  AND TABLE_NAME = 'YOUR_TABLE_NAME'

ORDER BY 

    TABLE_NAME, 

    OBJECT_TYPE, 

    PARTITION_NAME;

3. The "Fix" Statement (Update)

Use the values from Statement 1 (Live) to update Statement 2 (Target).

• Replace the placeholders <...> with the actual numbers/names.

UPDATE DB_OBJECT_ID_CAPTURED
SET OBJECT_ID      = <NEW_OBJECT_ID_FROM_STEP_1>,
    DATA_OBJECT_ID = <NEW_DATA_OBJECT_ID_FROM_STEP_1>
WHERE PUR_ID = 279
  AND TABLE_NAME = 'YOUR_TABLE_NAME'
  
  -- 1. IDENTIFY THE PARTITION (Handle NULLs for non-partitioned tables)
  AND NVL(PARTITION_NAME, '###') = NVL('<PARTITION_NAME_FROM_STEP_1>', '###')
  
  -- 2. LOCK THE TYPE (Ensures you don't mix up Partition vs Subpartition)
  AND OBJECT_TYPE = '<OBJECT_TYPE_FROM_STEP_1>' 
  -- 3. SAFETY CHECK (Only update if it matches the OLD values from Step 2)
  AND OBJECT_ID = <OLD_OBJECT_ID_FROM_STEP_2>
  AND DATA_OBJECT_ID = <OLD_DATA_OBJECT_ID_FROM_STEP_2>;

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

Master Script:

/*

This is the most seamless approach. We will wrap the logic in a standard PL/SQL block.

How this works:

1. Run it as is: It acts exactly like your "Health Check" query (Preview Mode). It prints the changes to the DBMS_OUTPUT window.

2. Change one word: Change v_apply_changes to TRUE and run it again. It executes the Merge.

This guarantees that what you see in the report is exactly what gets applied.

The "All-in-One" Script

*/

SET SERVEROUTPUT ON;

DECLARE

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

    -- 1. CONFIGURATION SECTION

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

    v_pur_id         NUMBER       := 279;             -- Your Group ID

    v_schema_owner   VARCHAR2(50) := 'YOUR_SCHEMA';   -- Your Schema

    

    -- OPTIONAL FILTERS (Leave NULL to ignore)

    v_table_filter   VARCHAR2(50) := NULL;            -- e.g. 'MY_BIG_TABLE'

    v_part_filter    VARCHAR2(50) := NULL;            -- e.g. 'P_2025_JAN'

    

    -- ACTION: FALSE = Preview, TRUE = Execute

    v_apply_changes  BOOLEAN      := FALSE;           

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

    v_rows_merged    NUMBER := 0;

BEGIN

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

    DBMS_OUTPUT.PUT_LINE('MODE:      ' || CASE WHEN v_apply_changes THEN 'EXECUTE' ELSE 'PREVIEW' END);

    DBMS_OUTPUT.PUT_LINE('TABLE:     ' || NVL(v_table_filter, 'ALL'));

    DBMS_OUTPUT.PUT_LINE('PARTITION: ' || NVL(v_part_filter, 'ALL'));

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

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

    -- PART A: PREVIEW MODE

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

    IF NOT v_apply_changes THEN

        FOR r IN (

            WITH captured_db AS (

                SELECT table_name, partition_name, object_id, data_object_id

                FROM db_object_id_captured

                WHERE pur_id = v_pur_id

                  AND (v_table_filter IS NULL OR table_name = v_table_filter)

                  AND (v_part_filter IS NULL OR partition_name = v_part_filter)

            ),

            live_db AS (

                SELECT o.object_name, o.subobject_name, o.object_type, o.object_id, o.data_object_id

                FROM all_objects o

                WHERE o.owner = v_schema_owner

                  AND o.object_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

                  AND o.data_object_id IS NOT NULL -- COMPOSITE=NO Check

                  

                  -- Filters

                  AND (v_table_filter IS NULL OR o.object_name = v_table_filter)

                  AND (v_part_filter IS NULL OR o.subobject_name = v_part_filter)

                  -- Safety Join

                  AND o.object_name IN (SELECT DISTINCT table_name FROM captured_db)

            )

            SELECT 

                NVL(live.object_name, cap.table_name) AS t_name,

                NVL(live.subobject_name, cap.partition_name) AS p_name,

                CASE 

                    WHEN live.object_id IS NULL THEN 'ORPHAN (Safe to Delete)'

                    WHEN cap.object_id IS NULL THEN 'NEW (Will Insert)'

                    WHEN live.object_id != cap.object_id OR 

                         NVL(live.data_object_id,-1) != NVL(cap.data_object_id,-1) THEN 'DRIFT (Will Update)'

                    ELSE 'SYNCED'

                END AS status,

                cap.object_id as old_id, live.object_id as new_id

            FROM live_db live

            FULL OUTER JOIN captured_db cap

              ON live.object_name = cap.table_name

              AND NVL(live.subobject_name, '###') = NVL(cap.partition_name, '###')

            WHERE (live.object_id IS NULL) OR (cap.object_id IS NULL) 

               OR (live.object_id != cap.object_id) 

               OR (NVL(live.data_object_id,-1) != NVL(cap.data_object_id,-1))

            ORDER BY 1, 2

        ) LOOP

            DBMS_OUTPUT.PUT_LINE('[' || r.status || '] ' || r.t_name || ' : ' || r.p_name || 

                                 ' (Old: ' || r.old_id || ' -> New: ' || r.new_id || ')');

        END LOOP;

        

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

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

    -- PART B: EXECUTE MODE

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

    ELSE

        MERGE INTO db_object_id_captured target

        USING (

            SELECT 

                v_pur_id AS pur_id,

                o.object_id, 

                o.data_object_id, 

                o.object_name, 

                o.subobject_name, 

                o.object_type

            FROM all_objects o

            WHERE o.owner = v_schema_owner

              AND o.object_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

              AND o.data_object_id IS NOT NULL 

              

              -- Apply Filters

              AND (v_table_filter IS NULL OR o.object_name = v_table_filter)

              AND (v_part_filter IS NULL OR o.subobject_name = v_part_filter)

              AND o.object_name IN (

                  SELECT DISTINCT table_name FROM db_object_id_captured WHERE pur_id = v_pur_id

              )

        ) source

        ON (

            target.pur_id          = source.pur_id

            AND target.table_name  = source.object_name

            AND NVL(target.partition_name, '###') = NVL(source.subobject_name, '###')

            AND target.object_type = source.object_type

        )

        WHEN MATCHED THEN

            UPDATE SET 

                target.object_id      = source.object_id,

                target.data_object_id = source.data_object_id,

                target.last_updated   = SYSDATE

            WHERE DECODE(target.object_id, source.object_id, 0, 1) = 1 

               OR DECODE(target.data_object_id, source.data_object_id, 0, 1) = 1

        WHEN NOT MATCHED THEN

            INSERT (pur_id, table_name, partition_name, object_type, object_id, data_object_id, last_updated)

            VALUES (source.pur_id, source.object_name, source.subobject_name, source.object_type, source.object_id, source.data_object_id, SYSDATE);

            

        v_rows_merged := SQL%ROWCOUNT;

        COMMIT;

        DBMS_OUTPUT.PUT_LINE('SUCCESS. Rows Synced: ' || v_rows_merged);

    END IF;

END;

/

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

Script 2

/* Here is the updated Robust Health Check script.

I have decoupled the filter logic. Now, the captured_db block is completely independent, allowing you to manually type whatever WHERE clause you need (single ID, multiple IDs, date ranges, etc.).

The script is smart enough to use your manual list to filter the live_db query automatically, keeping performance high.

*/

WITH 

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

-- 1. YOUR MANUAL FILTER (Edit this section)

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

captured_db AS (

    SELECT 

        table_name, 

        partition_name, 

        object_type, 

        object_id, 

        data_object_id

    FROM db_object_id_captured

    WHERE pur_id = 279  -- <--- MANUALLY EDIT YOUR CONDITION HERE

    -- Examples: 

    -- WHERE pur_id IN (279, 280)

    -- WHERE table_name LIKE 'STG_%'

),

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

-- 2. LIVE DB FETCH (Automatically looks at tables from step 1)

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

live_db AS (

    SELECT 

        o.object_name, 

        o.subobject_name, 

        o.object_type, 

        o.object_id, 

        o.data_object_id

    FROM all_objects o

    WHERE o.owner = 'YOUR_SCHEMA_NAME' -- <--- UPDATE SCHEMA OWNER

      AND o.object_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

      AND o.data_object_id IS NOT NULL -- Matches "COMPOSITE='NO'" (Physical segments only)

      

      -- OPTIMIZATION: Only looks for tables found in your manual list above

      AND o.object_name IN (SELECT DISTINCT table_name FROM captured_db)

)

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

-- 3. THE COMPARISON REPORT (Full Health Check)

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

SELECT 

    NVL(live.object_name, cap.table_name) AS TABLE_NAME,

    NVL(live.subobject_name, cap.partition_name) AS PARTITION_NAME,

    

    CASE 

        WHEN live.object_id IS NULL THEN 'ORPHAN (In Table, Deleted from DB)'

        WHEN cap.object_id IS NULL THEN 'NEW (In DB, Missing in Table)'

        WHEN live.object_id != cap.object_id OR 

             NVL(live.data_object_id, -1) != NVL(cap.data_object_id, -1) 

             THEN 'DRIFT (IDs Changed)'

        ELSE 'SYNCED'

    END AS STATUS,

    cap.object_id AS OLD_ID,

    live.object_id AS NEW_ID

    

FROM live_db live

FULL OUTER JOIN captured_db cap

  ON live.object_name = cap.table_name

  AND NVL(live.subobject_name, '###') = NVL(cap.partition_name, '###')

  AND live.object_type = cap.object_type

-- Optional: Filter to show only issues

WHERE (live.object_id IS NULL) 

   OR (cap.object_id IS NULL) 

   OR (live.object_id != cap.object_id)

   OR (NVL(live.data_object_id, -1) != NVL(cap.data_object_id, -1))

ORDER BY 1, 2;

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

Script 3:

======

Part 1: The Preview Report (ReadOnly)

Run this query first. It joins your table with the live database using the COMPOSITE='NO' logic (Physical Segments only) and tells you the status of every object.

SQL

WITH config AS (

    SELECT 279 AS filter_pur_id, 'YOUR_SCHEMA_NAME' AS filter_owner FROM dual

),

live_data AS (

    -- Equivalent to your "Composite = NO" query, but faster

    SELECT object_name, subobject_name, object_type, object_id, data_object_id

    FROM all_objects, config

    WHERE owner = config.filter_owner

      AND object_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

      AND data_object_id IS NOT NULL -- This filters out COMPOSITE partitions

),

captured_data AS (

    SELECT table_name, partition_name, object_type, object_id, data_object_id

    FROM db_object_id_captured, config

    WHERE pur_id = config.filter_pur_id

)

SELECT 

    NVL(live.object_name, cap.table_name) AS TABLE_NAME,

    NVL(live.subobject_name, cap.partition_name) AS PARTITION_NAME,

    

    -- LIVE VALUES

    live.object_id AS LIVE_OBJ_ID,

    live.data_object_id AS LIVE_DATA_ID,

    

    -- CAPTURED VALUES

    cap.object_id AS DB_CAPTURED_OBJ_ID,

    cap.data_object_id AS DB_CAPTURED_DATA_ID,

    

    -- STATUS CALCULATION

    CASE 

        WHEN cap.object_id IS NULL THEN 'NEW (Insert Needed)'

        WHEN live.object_id IS NULL THEN 'ORPHAN (Delete Needed)'

        WHEN live.object_id != cap.object_id OR 

             NVL(live.data_object_id, -1) != NVL(cap.data_object_id, -1) 

             THEN 'NEEDS TO BE UPDATED'

        ELSE 'THEY ARE IN SYNC'

    END AS STATUS

FROM live_data live

FULL OUTER JOIN captured_data cap

  ON live.object_name = cap.table_name

  AND NVL(live.subobject_name, '###') = NVL(cap.partition_name, '###')

  AND live.object_type = cap.object_type

WHERE 

    -- Filter to show only specific table if needed, or leave commented for all

    -- live.object_name = 'YOUR_TABLE_NAME' AND 

    (live.object_id != cap.object_id OR 

     NVL(live.data_object_id, -1) != NVL(cap.data_object_id, -1) OR 

     cap.object_id IS NULL OR live.object_id IS NULL)

ORDER BY 1, 2;

====

This is the "Best of the Best" approach. It strictly follows your logic (validating COMPOSITE='NO') and gives you exactly what you asked for:

1. Part 1: A clean Report (Preview) showing the comparison side-by-side.

2. Part 2: A generator that writes the SQL statements for you. It produces the exact UPDATE scripts with the specific WHERE clauses you requested.

---

Part 1: The Preview Report (ReadOnly)

Run this query first. It joins your table with the live database using the COMPOSITE='NO' logic (Physical Segments only) and tells you the status of every object.

SQL

WITH config AS (
    SELECT 279 AS filter_pur_id, 'YOUR_SCHEMA_NAME' AS filter_owner FROM dual
),
live_data AS (
    -- Equivalent to your "Composite = NO" query, but faster
    SELECT object_name, subobject_name, object_type, object_id, data_object_id
    FROM all_objects, config
    WHERE owner = config.filter_owner
      AND object_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
      AND data_object_id IS NOT NULL -- This filters out COMPOSITE partitions
),
captured_data AS (
    SELECT table_name, partition_name, object_type, object_id, data_object_id
    FROM db_object_id_captured, config
    WHERE pur_id = config.filter_pur_id
)
SELECT 
    NVL(live.object_name, cap.table_name) AS TABLE_NAME,
    NVL(live.subobject_name, cap.partition_name) AS PARTITION_NAME,
    
    -- LIVE VALUES
    live.object_id AS LIVE_OBJ_ID,
    live.data_object_id AS LIVE_DATA_ID,
    
    -- CAPTURED VALUES
    cap.object_id AS DB_CAPTURED_OBJ_ID,
    cap.data_object_id AS DB_CAPTURED_DATA_ID,
    
    -- STATUS CALCULATION
    CASE 
        WHEN cap.object_id IS NULL THEN 'NEW (Insert Needed)'
        WHEN live.object_id IS NULL THEN 'ORPHAN (Delete Needed)'
        WHEN live.object_id != cap.object_id OR 
             NVL(live.data_object_id, -1) != NVL(cap.data_object_id, -1) 
             THEN 'NEEDS TO BE UPDATED'
        ELSE 'THEY ARE IN SYNC'
    END AS STATUS
FROM live_data live
FULL OUTER JOIN captured_data cap
  ON live.object_name = cap.table_name
  AND NVL(live.subobject_name, '###') = NVL(cap.partition_name, '###')
  AND live.object_type = cap.object_type
WHERE 
    -- Filter to show only specific table if needed, or leave commented for all
    -- live.object_name = 'YOUR_TABLE_NAME' AND 
    (live.object_id != cap.object_id OR 
     NVL(live.data_object_id, -1) != NVL(cap.data_object_id, -1) OR 
     cap.object_id IS NULL OR live.object_id IS NULL)
ORDER BY 1, 2;

---

Part 2: The SQL Generator (The "Fix" Script)

This PL/SQL block does not execute changes. Instead, it prints the specific UPDATE statements you asked for to the "Dbms Output" window. You can then copy-paste them and run them manually.

• It checks COMPOSITE='NO' logic.

• It includes the Old Values in the WHERE clause as requested.

  
  

  SET SERVEROUTPUT ON SIZE 100000;
  DECLARE
      v_pur_id       NUMBER       := 279;
      v_schema_owner VARCHAR2(50) := 'YOUR_SCHEMA_NAME';
  BEGIN
      DBMS_OUTPUT.PUT_LINE('-- GENERATED UPDATE STATEMENTS (INCLUDES SUBPARTITIONS) --');
      
      FOR r IN (
          SELECT 
              live.object_name,
              live.subobject_name,
              live.object_type,
              -- New Values (From Live DB)
              live.object_id AS new_obj_id,
              live.data_object_id AS new_data_id,
              -- Old Values (From Your Table)
              cap.object_id AS old_obj_id,
              cap.data_object_id AS old_data_id
          FROM all_objects live
          JOIN db_object_id_captured cap
            ON live.object_name = cap.table_name
            -- Matches Partition OR Subpartition Name automatically
            AND NVL(live.subobject_name, '###') = NVL(cap.partition_name, '###')
            AND live.object_type = cap.object_type
          WHERE live.owner = v_schema_owner
            AND cap.pur_id = v_pur_id
            AND live.data_object_id IS NOT NULL -- Composite=NO Logic
            AND live.object_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
            
            -- CRITICAL: Only generate updates if IDs drifted
            AND (live.object_id != cap.object_id OR 
                 NVL(live.data_object_id, -1) != NVL(cap.data_object_id, -1))
          ORDER BY live.object_name, live.subobject_name
      ) LOOP
          -- 1. HEADER
          DBMS_OUTPUT.PUT_LINE('UPDATE DB_OBJECT_ID_CAPTURED');
          DBMS_OUTPUT.PUT_LINE('SET OBJECT_ID = ' || r.new_obj_id || 
                               ', DATA_OBJECT_ID = ' || r.new_data_id);
          
          -- 2. IDENTIFIERS (The "Where" Clause)
          DBMS_OUTPUT.PUT_LINE('WHERE PUR_ID = ' || v_pur_id);
          DBMS_OUTPUT.PUT_LINE('  AND TABLE_NAME = ''' || r.object_name || '''');
          
          -- Extended Logic: Explicitly handle Partitions vs Subpartitions in the WHERE clause
          IF r.subobject_name IS NOT NULL THEN
               DBMS_OUTPUT.PUT_LINE('  AND PARTITION_NAME = ''' || r.subobject_name || '''');
          ELSE
               DBMS_OUTPUT.PUT_LINE('  AND PARTITION_NAME IS NULL');
          END IF;
          -- [NEW] Explicitly lock the Object Type (Table vs Partition vs Subpartition)
          DBMS_OUTPUT.PUT_LINE('  AND OBJECT_TYPE = ''' || r.object_type || '''');
          -- 3. SAFETY CHECK (Old Values)
          DBMS_OUTPUT.PUT_LINE('  AND OBJECT_ID = ' || r.old_obj_id);
          
          IF r.old_data_id IS NOT NULL THEN
              DBMS_OUTPUT.PUT_LINE('  AND DATA_OBJECT_ID = ' || r.old_data_id || ';');
          ELSE
              DBMS_OUTPUT.PUT_LINE('  AND DATA_OBJECT_ID IS NULL;');
          END IF;
          DBMS_OUTPUT.PUT_LINE('------------------------------------------');
      END LOOP;
  END;
  /