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

Question

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;

Answer 1

NEW UPDATE

I confirmed a few days that this was a bug. Unfortunately, now the support team is telling me the following:

"It’s not a bug. As of 12.2 new types/features are only supported for dbms_rolling and golden gate." Conclusion, tables with names greater than 30 characters will not be supported on LogMiner, even if supplemental_logging is disabled. They are going to update the documentation. I will update the answer as long as I have more details regarding this.

SQL> select supplemental_log_data_min, supplemental_log_data_pk from v$database;

SUPPLEME SUP
-------- ---
YES YES

In this case, it will always show UNSUPPORTED for any DML operation when the table has a name with more than 30 characters.

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
         0

PL/SQL Release 12.2.0.1.0 - Production
         0

CORE    12.2.0.1.0      Production
         0


BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
TNS for Linux: Version 12.2.0.1.0 - Production
         0

NLSRTL Version 12.2.0.1.0 - Production
         0

Let's start the test case

SQL> create table cpl_rep.my_table_with_a_very_long_name_with_more ( c1 number );

Table created.

SQL> insert into cpl_rep.my_table_with_a_very_long_name_with_more values ( 1 ) ;

1 row created.

SQL> insert into cpl_rep.my_table_with_a_very_long_name_with_more values ( 2 ) ;

1 row created

SQL> commit;

Commit complete.

SQL> select length(table_name) from dba_tables where table_name =  upper('my_table_with_a_very_long_name_with_more');

LENGTH(TABLE_NAME)
------------------
                40

SQL>

Then I start my logminer session, first I switch my logfile

SQL> alter system switch logfile ;

System altered.

SQL> exit

Then I enter again to open my logminer session

SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo11.ora' , 1);
 exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo21.ora' , 1);
 exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo12.ora' , 1);
 exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo22.ora' , 1);
 exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo13.ora' , 1);
 exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo23.ora' , 1);

PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.


SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL>  select count(*) from v$logmnr_contents where seg_name =  upper('my_table_with_a_very_long_name_with_more');

  COUNT(*)
----------
         3

SQL> select operation,seg_name,sql_redo from v$logmnr_contents where seg_name =  upper('my_table_with_a_very_long_name_with_more');

OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
DDL
MY_TABLE_WITH_A_VERY_LONG_NAME_WITH_MORE
create table cpl_rep.my_table_with_a_very_long_name_with_more ( c1 number );

OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------

INSERT
MY_TABLE_WITH_A_VERY_LONG_NAME_WITH_MORE
insert into "CPL_REP"."MY_TABLE_WITH_A_VERY_LONG_NAME_WITH_MORE"("C1") values ('
1');

OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
INSERT
MY_TABLE_WITH_A_VERY_LONG_NAME_WITH_MORE
insert into "CPL_REP"."MY_TABLE_WITH_A_VERY_LONG_NAME_WITH_MORE"("C1") values ('
2');

So, as you can see, in my case, there is no such thing as a limitation for 30 characters when the element affected is a table.

Let's see when the element is a column

SQL> create table cpl_rep.table_with_name_greater_than_30_characters ( column_greater_than_30_characters_test_case number );

Table created.

SQL> select length('table_with_name_greater_than_30_characters') , length('column_greater_than_30_characters_test_case') from dual ;

LENGTH('TABLE_WITH_NAME_GREATER_THAN_30_CHARACTERS')
----------------------------------------------------
LENGTH('COLUMN_GREATER_THAN_30_CHARACTERS_TEST_CASE')
-----------------------------------------------------
                                                  42
                                                   43



SQL> insert into cpl_rep.table_with_name_greater_than_30_characters values ( 1 );

1 row created.

SQL> r
  1* insert into cpl_rep.table_with_name_greater_than_30_characters values ( 1 )

1 row created.

SQL> commit;

Commit complete.

SQL> insert into cpl_rep.table_with_name_greater_than_30_characters values ( 2 );

1 row created.

SQL> r
  1* insert into cpl_rep.table_with_name_greater_than_30_characters values ( 2 )

1 row created.

SQL> commit;

Commit complete.

SQL> delete from cpl_rep.table_with_name_greater_than_30_characters where column_greater_than_30_characters_test_case=2 ;

2 rows deleted.

SQL> commit;

Commit complete.

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 19 17:07:58 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo11.ora' , 1);
     exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo21.ora' , 1);
     exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo12.ora' , 1);
     exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo22.ora' , 1);
     exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo1/redo13.ora' , 1);
     exec dbms_logmnr.add_logfile('/bbdd_odcgrc1r/redo2/redo23.ora' , 1);
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents where seg_owner = 'CPL_REP' and seg_name = upper('table_with_name_greater_than_30_characters') ;

  COUNT(*)
----------
         3

SQL> select operation,seg_name,sql_redo from v$logmnr_contents where seg_owner = 'CPL_REP' and seg_name = upper('table_with_name_greater_than_30_characters') ;

OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
DDL
TABLE_WITH_NAME_GREATER_THAN_30_CHARACTERS
create table cpl_rep.table_with_name_greater_than_30_characters ( column_greater
_than_30_characters_test_case number );

INSERT
TABLE_WITH_NAME_GREATER_THAN_30_CHARACTERS

OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
insert into "CPL_REP"."TABLE_WITH_NAME_GREATER_THAN_30_CHARACTERS"("COLUMN_GREAT
ER_THAN_30_CHARACTERS_TEST_CASE") values ('1');

INSERT
TABLE_WITH_NAME_GREATER_THAN_30_CHARACTERS
insert into "CPL_REP"."TABLE_WITH_NAME_GREATER_THAN_30_CHARACTERS"("COLUMN_GREAT
ER_THAN_30_CHARACTERS_TEST_CASE") values ('1');

OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------

So, in my case, I can operate with both columns and tables greater than 30 characters.

UPDATE

After the comments sections, I decided to try the test with SUPPLEMENTAL_LOGGING and it works. However, when I add SUPPLEMENTAL_LOGGING for all columns PK, then it does not work

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> select supplemental_log_data_min, supplemental_log_data_pk from v$database;

SUPPLEME SUP
-------- ---
YES      NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Database altered.

SQL>  select supplemental_log_data_min, supplemental_log_data_pk from v$database;

SUPPLEME SUP
-------- ---
YES      YES

SQL> create table cpl_rep.my_test_with_a_very_very_long_name_for_test ( c1 number ) ;

Table created.

   
SQL> insert into cpl_rep.my_test_with_a_very_very_long_name_for_test values ( 1 ) ;

1 row created.

SQL> insert into cpl_rep.my_test_with_a_very_very_long_name_for_test values ( 2 ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> insert into cpl_rep.my_test_with_a_very_very_long_name_for_test values ( 3 ) ;

1 row created.

SQL> commit;

Commit complete.


SQL> delete from cpl_rep.my_test_with_a_very_very_long_name_for_test where c1 = 3 ;

1 row deleted.

SQL>

Switch logfile and start logminer. The contents show now the value UNSUPPORTED.

SQL> select sql_redo , operation, seg_name from v$logmnr_contents where seg_name = upper('my_test_with_a_very_very_long_name_for_test') ;

SQL_REDO
--------------------------------------------------------------------------------
OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
create table cpl_rep.my_test_with_a_very_very_long_name_for_test ( c1 number ) ;
DDL
MY_TEST_WITH_A_VERY_VERY_LONG_NAME_FOR_TEST

Unsupported
UNSUPPORTED
MY_TEST_WITH_A_VERY_VERY_LONG_NAME_FOR_TEST

SQL_REDO
--------------------------------------------------------------------------------
OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------

Unsupported
UNSUPPORTED
MY_TEST_WITH_A_VERY_VERY_LONG_NAME_FOR_TEST

Unsupported
UNSUPPORTED

SQL_REDO
--------------------------------------------------------------------------------
OPERATION
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
MY_TEST_WITH_A_VERY_VERY_LONG_NAME_FOR_TEST

Unsupported
UNSUPPORTED
MY_TEST_WITH_A_VERY_VERY_LONG_NAME_FOR_TEST


SQL>

The key is clearly SUPPLEMENTAL_LOGGING PK, and also that only affects to DML operations, as DDL show the real command executed.