在Oracle 12c R2中，LogMiner是否支持超过30个字符的表/列名

Question

到目前为止，任何列名超过30个字符的表在查询V$LOGMNR_CONTENTS 时都会给出一个UNSUPPORTED操作

如果我删除列或将大小调整为<30，则所有CRUD操作被报告为良好。

在Oracle 12.2中，支持128个字符的对象，所以我试图了解我是否配置了错误的东西。无休止的谷歌搜索让我一无所获，甲骨文的文档也没有。

提前感谢！

编辑

刚刚检查了19c，同样的行为。兼容性设置为19.0.0

编辑

有很多关于使用补充日志的评论，但无法创建与公认答案相同的场景。

不管怎样，鉴于甲骨文现在已经表示永远不会支持它，这并不重要！

我在仍然无法工作的地方进行了测试

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

新更新

几天前我确认了这是一个错误。不幸的是，现在支持团队告诉我以下内容：

"这不是bug。从12.2起，仅dbms_rolling和金门支持新的类型/功能"结论：LogMiner不支持名称超过30个字符的表，即使supplement_logging被禁用。他们将更新文件。我会更新答案，只要我有更多的细节。

SQL> select supplemental_log_data_min, supplemental_log_data_pk from v$database;
SUPPLEME SUP
-------- ---
YES YES

在这种情况下，当表的名称超过30个字符时，它将始终显示任何DML操作的UNSUPPORTED

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

让我们开始测试用例

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>

然后我开始我的日志矿工会话，首先我切换我的日志

SQL> alter system switch logfile ;
System altered.
SQL> exit

然后我再次进入以打开我的日志矿工会话

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');

因此，正如您所看到的，在我的例子中，当受影响的元素是一个表时，没有限制30个字符。

让我们看看元素何时是列

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
--------------------------------------------------------------------------------

因此，在我的情况下，我可以对大于30个字符的列和表进行操作。

更新

在评论部分之后，我决定尝试使用SUPPLMENTAL_LOGING进行测试，它确实有效。但是，当我为所有列PK添加SUPPLMENTAL_LOGING时，它不起的作用

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>

切换日志文件并启动logminer。内容现在显示值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>

键显然是SUPPLMENTAL_LOGGING PK，而且它只影响DML操作，因为DDL显示执行的真实命令

Answer 2

最后，Oracle对此问题打开了一个bug：对于超过30个字符的表名(Doc ID 2703406.1(，Log Miner将SQL_REDO显示为UNSUPPORTED。

但还没有解决方案：(

Answer 3

只是为了结束这个循环。Oracle 21c中的文档已更新，以指定限制。

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

我真的不喜欢Oracle如何编写文档，但嘿，至少它在那里。