Skip to content

How to Resolve ORA-32589: unable to drop minimal supplemental logging

  • by

ORA-32589

Tried to drop the minimal supplemental logging from the database, but it failed with 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

ORA-32589 means that you cannot drop it at this moment since some other supplemental logs are still depending on the minimal one.

Let's check the statuses of other supplemental logs by querying V$DATABASE.

SQL> select supplemental_log_data_min min, supplemental_log_data_pk pk, supplemental_log_data_ui ui, supplemental_log_data_fk fk, supplemental_log_data_all al, supplemental_log_data_pl pl from v$database;

MIN      PK  UI  FK  AL  PL
-------- --- --- --- --- ---
YES      YES YES NO  YES NO

As we can see, some other supplemental logs are still enabled.

For 19c or later release, you may add SUPPLEMENTAL_LOG_DATA_SR to the column list of query.

Solution

In order to remove the minimal supplemental logs from the database, we should drop dependent supplemental logs first.

SQL> alter database drop supplemental log data (all) columns;

Database altered.

SQL> alter database drop supplemental log data (unique) columns;

Database altered.

SQL> alter database drop supplemental log data (primary key) columns;

Database altered.

Let's check their statuses again.

SQL> select supplemental_log_data_min min, supplemental_log_data_pk pk, supplemental_log_data_ui ui, supplemental_log_data_fk fk, supplemental_log_data_all al, supplemental_log_data_pl pl from v$database;

MIN      PK  UI  FK  AL  PL
-------- --- --- --- --- ---
YES      NO  NO  NO  NO  NO

Now we can drop the minimal supplemental log.

SQL> alter database drop supplemental log data;

Database altered.

We solved ORA-32589.

To see more use cases of supplemental logging, you may check the post: How to Enable Flashback Transaction Query.

Leave a Reply

Your email address will not be published. Required fields are marked *