Oracle19c分区表调用SET_ROWID_THRESHOLD报ORA-29971错误咨询

问题根因 #

• Oracle 19c中DBMS_CHANGE_NOTIFICATION.SET_ROWID_THRESHOLD接口的底层校验逻辑，要求传入的表对象上必须存在已激活的变更通知注册记录，否则直接抛出ORA-29971错误。
• 针对分区表场景，Oracle的变更通知注册默认是绑定到每个独立的分区/子分区对象上，而非顶层的分区表父对象。你调用接口时传入的是父表名，而父表本身没有对应的注册记录，因此会触发报错。
• 这是Oracle 19c的已知版本限制：SET_ROWID_THRESHOLD接口在19c版本中没有适配分区表父对象的寻址逻辑，只会检索普通非分区堆表的注册记录，不会关联子分区的注册信息。

可用解决方案 #

• 方案1：针对分区表的每个独立分区单独调用阈值设置接口，传入对应分区的schema名和分区名即可完成设置，例如你的分区名为SCH.TABLE_P1、SCH.TABLE_P2，分别执行命令即可：

  exec dbms_change_notification.SET_ROWID_THRESHOLD('SCH.TABLE_P1', 1000);
  exec dbms_change_notification.SET_ROWID_THRESHOLD('SCH.TABLE_P2', 1000);
  

• 方案2：升级Oracle数据库到21c及以上版本，该版本开始已经支持对分区表父对象直接设置阈值，设置后会自动同步到所有下属子分区。

注意：无论使用哪种方案，都需要先确保目标对象上已经完成变更通知注册，再执行阈值调整操作，否则仍会触发ORA-29971错误。

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