Investigate compatibility with DBMS_REDEFINITION

[Naros]

The DBMS_REDEFINITION PL/SQL package is used by DBAs to reorganize tables on disk for performance. It does so by creating a temporary table, copying data from the base table while keeping it synchronized with current actions on the base table, and ultimately replacing the base table with the redefined one.

The key concern is the final table replacement, since the interim table's object identity is retained, meaning that a table would randomly move from being OBJECT_ID 12345 to 24567. For redo_log_catalog and online_catalog mining strategies, this is not an issue since object identity isn't used; however, for hybrid, this could be a concern.

The key question is how DBMS_REDEFINITION renames the table; does it use ALTER TABLE <old> RENAME TO <new> or does it use some other internal or undocumented way to rename the table?

If it uses DDL statements, we already handle this scenario; however, if it uses another approach (including a currently unsupported DDL syntax), the table object identity would be out of sync, which would be problematic.

We should investigate how the use of DBSM_REDEFINITION affects captured tables.

See #community-oracle > Debezium Oracle interaction with DBMS_REDEFINITION

[Naros]

I tested this with very basic tables:

CREATE TABLE debezium.test (id numeric(9,0) primary key, data varchar2(50));
CREATE TABLE debezium.test_int (id numeric(9,0) primary key, data varchar2(50));

The TEST table is assigned OBJECT_ID=77008 and the TEST_INT is assigned OBJECT_ID=77010. The procedure uses a combination of Oracle Snapshot jobs and Materialized Views to effectively reinsert all the data from the source table into the interim. This means that all caveats with respect to Materialized Views apply, which includes the fact that every row from the source table will have a matching DIRECT INSERT operation in the redo logs.

When the DIRECT INSERT operations are performed, they are recorded as being applied to the old table name, but using the new object id. This confuses the LogMiner online catalog dictionary lookups, which leads to the insert statements being recorded as though the column information is unknown:

INSERT INTO "DEBEZIUM"."TEST" ("COL 1", "COL 2") VALUES (HEXTORAW('<hex>'), HEXTORAW('<hex>'));

Under normal circumstances, this is a classic situation where the recommendation would be for the connector to use the log.mining.strategy=hybrid setup, allowing the HEXTORAW and COL n namings to be properly decoded by the connector looking up the object id from the connector table schema. Using online_catalog would cause a connector failure because events with STATUS=2 are failures.

If the connector is using hybrid and given that the table name is known in this context, the OBJECT_ID will actually not be used, and the table will be decoded properly; however, the relational model will continue to refer to the old 77008 object id versus the new 77010 id, which could lead to future issues depending on the other DDL statements that are executed.