使用Debezium+Kafka同步Oracle数据遇'TABLE not found in schema'报错

报错信息 #

Caused by: java.sql.SQLException: ORA-31603: object "tableName" of type TABLE not found in schema "schemaName"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2588
ORA-06512: at "SYS.DBMS_METADATA", line 2827
ORA-06512: at "SYS.DBMS_METADATA", line 3608
ORA-06512: at "SYS.DBMS_METADATA", line 5008
ORA-06512: at "SYS.DBMS_METADATA", line 5336
ORA-06512: at "SYS.DBMS_METADATA", line 6702
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at line 1

源连接器配置 #

name=inventory-connector-test13
connector.class=io.debezium.connector.oracle.OracleConnector
tasks.max=1
database.server.name=**-*****-**.**.**
database.hostname=***.***.**.**
database.port=1521
database.user=username
database.password=pass
database.dbname=dbname
database.connection.adapter=logminer
database.history.kafka.bootstrap.servers=localhost:9092
database.history.kafka.topic=schema-changes.inventory
table.include.list=schmemaname.tablename

解决步骤 #

• 修正配置拼写错误：配置里的schmemaname是schema名的拼写错误，改为实际的schemaName，确保table.include.list的值为schemaName.tableName，和Oracle中实际的schema、表名完全匹配。注意Oracle对象名默认大写，若表是小写创建的，需用双引号包裹，比如"schemaName"."tableName"。
• 验证表的存在性：登录Oracle数据库执行查询，确认目标表存在：

  SELECT table_name FROM all_tables WHERE owner = 'SCHEMANAME' AND table_name = 'TABLENAME';
  

  若结果为空，需确认同步的表是否正确。
• 补充数据库用户权限：确保Debezium使用的账号拥有以下权限：

  -- 授予目标表的查询权限
  GRANT SELECT ON schemaName.tableName TO username;
  -- 授予访问元数据的权限
  GRANT EXECUTE ON SYS.DBMS_METADATA TO username;
  -- 若使用LogMiner适配器，补充必要角色
  GRANT SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE TO username;
  

• 检查Oracle归档日志状态：使用LogMiner适配器时，必须确保Oracle开启了归档日志模式，否则无法读取变更日志。

内容的提问来源于stack exchange，提问作者Muhammad Affan