Dropping Oracle AQ Table

Lately I’ve been working on a project that uses Oracle AQ.  A procedure call that I’ve found extremely helpful is EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE.  This allows you to drop your AQ tables.  We use this are part of our build/rebuild scripts when testing.

EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'SCOTT.MY_QTABLE', force =>TRUE);

* Just replace 'SCOTT.MY_QTABLE' with your schema and table information.

We also use the below script to reset of build environment.

1 SELECT 'EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (QUEUE_TABLE => '''||OWNER||'.'||QUEUE_TABLE||''', FORCE =>TRUE);'
2 FROM SYS.ALL_QUEUE_TABLES
3 WHERE OWNER NOT IN ('SYS','SYSTEM')
4 AND OWNER = USER;

0 comments:

Post a Comment