ERROR OGG-08221 Cannot register or unregister EXTRACT EXTRACT-NAME because of the following SQL error: OCI Error 24,001.
CAUSE
An integrated extract with the same name was already registered in the database.
SOLUTION
In this particular case it was a new database where they were trying to register a new Integrated Extract, no one ever had created an IE with the same name earlier but this database where they were trying to register the integrated extract was actually copied/cloned from an existing database which had Integrated Extracts registered.
As per company's standard they wanted to register an IE with the same name and were not able to do so.
First the below was tried but didn't help:
SQL> execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'OGG$<<Queue-Table-Name>>', force => true);
To resolve the issue all the objects pertaining to the extract name were first queried from the dictionary (dba_objects) and then dropped using below statement before registering the extract successfully:
SQL> execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'AQ$_<<Queue-Table-Name>>_T', force => true);
SQL> execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'AQ$_<<Queue-Table-Name>>_H', force => true);
SQL> drop sequence AQ$_<<Queue-Table-Name>>_N;
SQL> drop view AQ$OGG$<<Queue-Table-Name>>_S;
SQL> drop view AQ$OGG$<<Queue-Table-Name>>_R;
etc...
NOTE: In the images and/or the document content above, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public
Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.