oracle-databaseoracle12clogminer

In Oracle 12c R2 does LogMiner support Table/Column names longer than 30 characters?


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;

Solution

  • Just to close the loop on this. Documentation has been updatd in Oracle 21c to specify the limitation.

    https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/oracle-logminer-utility.html#GUID-7594F0D7-0ACD-46E6-BD61-2751136ECDB4

    Really dislike how Oracle does their documentation but hey, at least it's there.