CODEWITHSUNDEEP
oracle-databaseoracle11gqueue
Icaro
Icaro

Reputation: 14845

Oracle queue is not been imp or exp in Oracle 11g

I exported a oracle database using the command

exp system/password@sid file=c:\expdb.dmp

After I import it back in another instance using 

imp system/password@sid file=c:\expdb.dmp

Everything looks good but in a closer look I notice that all my queues and all my queue_tables did not get into the new database.

After try to get the exp/imp to work for a while I decide it would be easier to reinstall the queues just to find your I had tables that related to that queries in the database so I first try 

Delete from table

And I got the error:

ORA-24005: Inappropriate utilities used to perform DDL on AQ table

so I try

BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE('table',TRUE);
END;

And that finally work. But my question is, how do I avoid all this trouble in the first place? Is there a way to export the database with the queues? Am I missing some flag? As it is a very painful exersise for each time we have to import a new database with queues.

Upvotes: 0

Views: 2898

Answers (1)

Daniel Vukasovich
Daniel Vukasovich

Reputation: 1742

You don't export Oracle queues using exp or expdp.

You must create new queues in the target database.

exp/imp queues will lead you into database catalog corruption.

Note: In 11GR2 documentation states:

Export import of queues is now fully supported at queue table level granularity. The user only needs to export the queue table. All the queues in the queue table, primary object grants, related objects like views, IOTs, rules are automatically exported.

This is only valid if you are exp/imp using 11GR2 as source and target database. AQ definitions changes from 11GR1 to 11GR2 for instance.

And about the error message you face when submitting delete statement:

This is expected behaviour in 11.2. You are prevented from performing inappropriate DDL on AQ objects in 11.2 and the ORA-24005 is reported.

To truncate a queue table and the associated IOTs, use DBMS_AQADM.PURGE_QUEUE_TABLE.

Upvotes: 2

Related Questions