删除supplemental log报ORA-32589错误

有些配置需要开启数据库追加日志，例如配置OGG时，可能需要增加supplemental log信息。
alter database add supplemental log data;

但删除时，可能会遇到ORA-32589错误，这通常可能是由于增加了其他键值的追加日志造成的

SQL> alter database drop supplemental log data;
alter database drop supplemental log data
*
ERROR at line 1:
ORA-32589: unable to drop minimal supplemental logging

 

Error:   ORA-32589  (ORA-32589)
Text:   unable to drop minimal supplemental logging
---------------------------------------------------------------------------
Cause: Minimal supplemental logging could not be dropped as one of
 primary key, foreign key, unique or all column supplemental
 logging is enabled at the database level.
Action: Use V$DATABASE to determine the databasewide supplemental logging
 directives. Minimal supplemental logging could be dropped if and
 only if no other databasewide supplemental logging directives are
 enabled.

删除SUPPLEMENTAL LOG DATA需要先删除其他的追加日志，可以通过查看v$database检查是否还有没有关闭的
select SUPPLEMENTAL_LOG_DATA_ALL
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI from v$database;

可以先删除某些键值的追加日志，执行例如：
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;