文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >在Oracle12c R2中，LogMiner是否支持大于30个字符的表/列名？

问在Oracle12c R2中，LogMiner是否支持大于30个字符的表/列名？
EN

Stack Overflow用户
提问于 2020-04-08 15:27:37
回答 3查看 1.1K关注 0票数 9

到目前为止，任何列名超过30个字符的表在查询V$LOGMNR_CONTENTS时都会提供不受支持的操作

如果我删除列或调整大小为<=30，那么所有CRUD操作都会很好地报告。

在Oracle 12.2中，支持128个字符对象，因此我试图了解是否配置错了什么。没完没了的googling让我一无所获，Oracle的文档也没有。

提前感谢！

编辑

刚刚检查了19c，同样的行为。可压缩性设置为19.0.0

编辑

对于补充日志记录的使用有很多评论，但不能创建与接受的答案相同的场景。

不管是哪种方式，考虑到Oracle现在都说它永远不会被支持，这并不重要！

测试我运行的地方，它仍然不能工作

代码语言：javascript
运行
复制
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;
oracle
oracle12c
logminer
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2021-03-09 22:29:06

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

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

非常不喜欢Oracle如何编写文档，但是嘿，至少它在那里。

票数 1
EN

Stack Overflow用户

发布于 2020-07-19 15:14:03

新更新

我确认了几天这是个窃听器。不幸的是，现在支持小组告诉我以下几点：

“这不是一个bug。从12.2开始，新的类型/特性只支持dbms_rolling和金门。”结论，即使禁用了LogMiner，也不支持名称大于30个字符的表。他们将更新文件。只要我对此有更详细的了解，我就会更新答案。

代码语言：javascript
运行
复制
SQL> select supplemental_log_data_min, supplemental_log_data_pk from v$database;

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

在本例中，当表名超过30个字符时，将始终不支持任何DML操作。。

代码语言：javascript
运行
复制
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

让我们开始测试用例

代码语言：javascript
运行
复制
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>

然后我启动我的日志程序会话，首先切换我的日志文件。

代码语言：javascript
运行
复制
SQL> alter system switch logfile ;

System altered.

SQL> exit

然后，我再次进入以打开我的logminer会话。

代码语言：javascript
运行
复制
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个字符的限制。

让我们看看元素什么时候是列

代码语言：javascript
运行
复制
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个字符的列和表进行操作。

更新

在评论部分之后，我决定尝试使用SUPPLEMENTAL_LOGGING进行测试，它可以工作。但是，当我为所有PK列添加SUPPLEMENTAL_LOGGING时，它将无法工作。

代码语言：javascript
运行
复制
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>

切换日志文件并启动日志记录器。内容现在显示不受支持的值。

代码语言：javascript
运行
复制
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>

的关键显然是SUPPLEMENTAL_LOGGING PK，这也只影响SUPPLEMENTAL_LOGGING操作，因为DDL显示了执行的真正命令.

票数 3
EN

Stack Overflow用户

发布于 2020-12-24 12:13:51

最后，甲骨文为这个问题打开了一个bug : longer显示SQL_REDO不支持超过30个字符的表名(Doc 2703406.1)。

但仍未通过决议:(

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/61104335

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档