Handling Oracle Object ID Drift: A Comprehensive Guide
If you maintain a metadata table (e.g., DB_OBJECT_ID_CAPTURED) that tracks Oracle Object IDs, you know the struggle. Every time partition maintenance occurs—splits, merges, truncates—the DATA_OBJECT_ID changes. If your downstream jobs rely on these static IDs, they fail.
Keeping this table in sync manually is error-prone. You need a way to filter out logical "Composite" partitions and track only the physical segments that hold data.
Here are the three industry-standard solutions to solve this, ranked from the best architectural fix to the best maintenance script.
Solution 1: The Architectural Fix (The View)
Best for: Systems where historical ID tracking is not required. The Concept: Instead of fighting to keep a physical table in sync, replace it with a View. The View passes the query directly to the live system dictionary (ALL_OBJECTS), ensuring your IDs are always 100% real-time.
-- 1. Backup the old table
ALTER TABLE db_object_id_captured RENAME TO db_object_id_captured_old;
-- 2. Create the View (Replaces the Table)
CREATE OR REPLACE VIEW db_object_id_captured AS
SELECT
279 AS PUR_ID, -- NOTE: Hardcoded Group ID (Adjust as needed)
object_id,
data_object_id,
object_name AS table_name,
subobject_name AS partition_name,
object_type,
owner
FROM all_objects
WHERE owner = 'YOUR_SCHEMA'
AND object_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
-- This filter ensures we only see physical data segments (Composite = NO)
AND data_object_id IS NOT NULL;
Solution 2: The Real-Time Fix (The Trigger)
Best for: Applications that require a physical table but need instant updates. The Concept: A DDL Trigger fires the millisecond a CREATE or ALTER command finishes, instantly updating your capture table.
CREATE OR REPLACE TRIGGER trg_sync_object_ids
AFTER DDL ON SCHEMA
DECLARE
v_obj_name VARCHAR2(128);
v_obj_type VARCHAR2(128);
BEGIN
v_obj_name := ora_dict_obj_name;
v_obj_type := ora_dict_obj_type;
IF v_obj_type IN ('TABLE','INDEX','TABLE PARTITION','TABLE SUBPARTITION') THEN
MERGE INTO db_object_id_captured target
USING (
SELECT object_id, data_object_id, object_name, subobject_name, object_type
FROM all_objects
WHERE object_name = v_obj_name AND owner = ora_dict_obj_owner
) source
ON (target.table_name = source.object_name
AND NVL(target.partition_name, '###') = NVL(source.subobject_name, '###'))
WHEN MATCHED THEN
UPDATE SET target.object_id = source.object_id,
target.data_object_id = source.data_object_id
WHEN NOT MATCHED THEN
INSERT (pur_id, table_name, partition_name, object_type, object_id, data_object_id)
VALUES (279, source.object_name, source.subobject_name, source.object_type, source.object_id, source.data_object_id);
END IF;
END;
/
Solution 3: The Batch Fix (The Master Script)
Best for: Controlled environments where you want to review drift before applying changes. The Concept: A robust PL/SQL block with two modes: Preview (Health Check) and Execute (Apply). It handles the complex logic of matching Partitions vs. Subpartitions and excluding Composite parents.
SET SERVEROUTPUT ON;
DECLARE
-- CONFIGURATION
v_pur_id NUMBER := 279;
v_schema_owner VARCHAR2(50) := 'YOUR_SCHEMA';
v_table_filter VARCHAR2(50) := NULL; -- Set NULL for ALL tables
-- MODE: FALSE = Preview (Health Check), TRUE = Execute (Apply Changes)
v_apply_changes BOOLEAN := FALSE;
v_rows_merged NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('MODE: ' || CASE WHEN v_apply_changes THEN 'EXECUTE' ELSE 'PREVIEW' END);
IF NOT v_apply_changes THEN
-- PREVIEW LOGIC
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)
),
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 -- Filters out Composite Partitions
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;
ELSE
-- EXECUTE LOGIC (MERGE)
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
AND (v_table_filter IS NULL OR o.object_name = v_table_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
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)
VALUES (source.pur_id, source.object_name, source.subobject_name, source.object_type, source.object_id, source.data_object_id);
v_rows_merged := SQL%ROWCOUNT;
COMMIT;
DBMS_OUTPUT.PUT_LINE('SUCCESS. Rows Synced: ' || v_rows_merged);
END IF;
END;
/
Appendix: Manual Verification Cheat Sheet
If you need to verify a specific table manually, run these 3 statements in order.
1. Check Live Data (Source)
SELECT OBJECT_ID, DATA_OBJECT_ID, OBJECT_NAME, SUBOBJECT_NAME
FROM ALL_OBJECTS
WHERE OWNER = 'YOUR_SCHEMA_NAME'
AND OBJECT_NAME = 'YOUR_TABLE_NAME'
AND DATA_OBJECT_ID IS NOT NULL -- Filters Composite Partitions
ORDER BY OBJECT_NAME, SUBOBJECT_NAME;
2. Check Captured Data (Target)
SELECT * FROM DB_OBJECT_ID_CAPTURED
WHERE PUR_ID = 279 AND TABLE_NAME = 'YOUR_TABLE_NAME';
3. Run the Safe Update
UPDATE DB_OBJECT_ID_CAPTURED
SET OBJECT_ID = <NEW_OBJECT_ID>,
DATA_OBJECT_ID = <NEW_DATA_OBJECT_ID>
WHERE PUR_ID = 279
AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND NVL(PARTITION_NAME, '###') = NVL('<PARTITION_NAME>', '###')
AND OBJECT_ID = <OLD_OBJECT_ID>; -- Safety Check
No comments:
Post a Comment