REM $Header: wfqclean.sql 26.0 2000/07/05 22:07:00 kma ship $
REM +=======================================================================+
REM |    Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA     |
REM |                         All rights reserved.                          |
REM +=======================================================================+
REM | FILENAME
REM |   wfqclean.sql
REM |
REM | DESCRIPTION
REM |   Cleans up Workflow Queues in system tables.
REM |   This is only necessary if you drop your user or tablespace
REM |   without previously dropping the queues using sql/wfqued.sql.
REM |
REM |   A bug in DROP USER CASCADE or DROP TABLESPACE INCLUDING CONTENTS  
REM |   leaves queue data in system tables resulting in ORA-600 when 
REM |   recreating the queues. This should be corrected in 8.1
REM |
REM |   To avoid this issue, always run sql/wfqued.sql to drop the queues
REM |   prior to dropping the user or tablespace.
REM |
REM | NOTES
REM |   User must have access to SYSTEM tables
REM |   un is the username of the account that experiences ORA-600.
REM +=======================================================================+
prompt =========================WARNING==============================
prompt This should only be run if you are experiencing ORA-600 when 
prompt creating queues.
prompt ==============================================================
Prompt Running this script at any other time will corrupt your schema. 
prompt ==============================================================

delete from system.aq$_queues 
where table_objno in 
       (select objno from system.aq$_queue_tables where schema=upper('&un'))
/
delete from system.aq$_queue_tables where schema=upper('&un')
/