So far, any table that has a column name longer than 30 characters gives an UNSUPPORTED Operation when querying V$LOGMNR_CONTENTS
If I drop the column or adjust the size to be <=30 then all the CRUD operations are reported fine.
In Oracle 12.2 128 Character Objects are supported, so I'm trying to understand if I've configured something wrong. Endless googling has gotten me nowhere, nor has the Oracle documentation.
Thanks in advance!
Edit
Just checked 19c, same behaviour. Compatability set at 19.0.0
EDIT
Been a lot of comments regarding the use of supplemental logging, but can't create the same scenario as the accepted answer.
Either way, given Oracle have now said it'll never be supported it doesn't matter too much!
Test I ran where it's still failing to work
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SELECT supplemental_log_data_min, supplemental_log_data_pk
FROM V$Database;
SUPPLEME SUP
-------- ---
YES NO
CREATE TABLE "REPLICATION_OWNER"."ATABLEWITHQUITEALONGNAMELIKEVERYLONGACTUALLY" (
"ID" NUMBER(10,0),
"NAME" VARCHAR2(254 BYTE)
);
ALTER TABLE "REPLICATION_OWNER"."ATABLEWITHQUITEALONGNAMELIKEVERYLONGACTUALLY" MODIFY ("ID" NOT NULL ENABLE);
ALTER TABLE "REPLICATION_OWNER"."ATABLEWITHQUITEALONGNAMELIKEVERYLONGACTUALLY" MODIFY ("NAME" NOT NULL ENABLE);
INSERT INTO atablewithquitealongnamelikeverylongactually VALUES (1, 'My Name');
DECLARE CURSOR LogMinerFileCursor IS
SELECT LogFile
FROM (
SELECT V$LOGFILE.Member AS LogFile,
FIRST_CHANGE# AS FirstSCN,
NEXT_CHANGE# AS LastSCN
FROM V$LOGFILE
INNER JOIN V$LOG ON V$LOGFILE.GROUP# = V$LOG.GROUP#
WHERE V$LOG.STATUS <> 'UNUSED'
AND FIRST_CHANGE# >= (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
UNION ALL
SELECT Name AS LogFile,
FIRST_CHANGE# AS FirstSCN,
NEXT_CHANGE# AS LastSCN
FROM V$ARCHIVED_LOG
WHERE FIRST_CHANGE# < (
SELECT MIN(FIRST_CHANGE#)
FROM V$LOGFILE
INNER JOIN V$LOG ON V$LOGFILE.GROUP# = V$LOG.GROUP#
WHERE V$LOG.STATUS <> 'UNUSED'
) AND FIRST_CHANGE# >= (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
) LogFiles WHERE FirstSCN >= 0 OR LastSCN > 0;
sDDL varchar2(2000);
BEGIN FOR LogMinerFileCursorRecords in LogMinerFileCursor LOOP
sDDL := 'BEGIN DBMS_LOGMNR.ADD_LOGFILE('''|| LogMinerFileCursorRecords.LogFile ||'''); END;';
DBMS_OUTPUT.Put_Line(sDDL);
execute immediate sDDL;
END LOOP;
COMMIT;
END;
BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + + DBMS_LOGMNR.COMMITTED_DATA_ONLY ); END;
SELECT SQL_REDO AS RedoSQL
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'REPLICATION_OWNER'
AND TABLE_NAME = 'ATABLEWITHQUITEALONGNAMELIKEVERYLONGACTUALLY'
REDOSQL
--------------------------------------------------------------------------------
CREATE TABLE "REPLICATION_OWNER"."ATABLEWITHQUITEALONGNAMELIKEVERYLONGACTUALLY"
(
"ID" NUMBER(10,0),
"NAME" VARCHAR2(254 BYTE)
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REPLICATION_DATA";
REDOSQL
-------------------------------------------------------------------------------- ALTER TABLE "REPLICATION_OWNER"."ATABLEWITHQUITEALONGNAMELIKEVERYLONGACTUALLY" M ODIFY ("ID" NOT NULL ENABLE);
REDOSQL
-------------------------------------------------------------------------------- ALTER TABLE "REPLICATION_OWNER"."ATABLEWITHQUITEALONGNAMELIKEVERYLONGACTUALLY" M ODIFY ("NAME" NOT NULL ENABLE);
REDOSQL
-------------------------------------------------------------------------------- Unsupported
BEGIN DBMS_LOGMNR.END_LOGMNR; END;
Just to close the loop on this. Documentation has been updatd in Oracle 21c to specify the limitation.
Really dislike how Oracle does their documentation but hey, at least it's there.