ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables .

Scenario

I was getting this error when I was trying to drop a schema.

Sql> drop user test cascade;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

Logged in as Test user and checked for queue tables .

SQL> select * from user_queue_tables;
no rows selected

SQL> select OWNER, NAME, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED
from DBA_QUEUES where OWNER='TEST';
 no rows selected

However I was able to locate few queue tables in the schema when I used 

Sql> select table_name from user_tables;

Got few Tables starting with AQ$_ *******

Tried to delete these tables using the DBMS_AQADM.DROP_QUEUE_TABLE procedure . However ended with the following error message .

SQL> begin

  2  DBMS_AQADM.DROP_QUEUE_TABLE(‘AQ$_test');

  3  end;

  4

  5  /

begin

*

ERROR at line 1:

ORA-24002: QUEUE_TABLE SCOTT.AQ$_test does not exist

ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084

ORA-06512: at "SYS.DBMS_AQADM", line 197

ORA-06512: at line 2

Working solution :

Logged in as sys and issued the following command :

alter session set events '10851 trace name context forever, level 2';

and then dropped all the AQ$.*****  tables from sys successfully.

Finally dropped the particular schema .

Issue resolved 

Linux shell script date formatting

Linux date FAQ: How do I create a formatted date in Linux? (Most likely, "How do I create a formatted date I can use in a Linux shell script?")
I just ran into a case where I needed to create a formatted date in a Linux shell script, where the date format looks like this:

2010-07-11

To create this formatted date string, I just used the Linux date command, using the "+" sign to specify that I want to use the date formatting option, like this:

thedate=`date +"%Y-%m-%d"`

The Linux date command shown in this line of code creates the date string in the format I want, and then I save the date output to a Linux shell script variable named "thedate".
This just shows one possible way to format a date in a Linux shell script. Of course you can use '-', '.', and many other characters as date field separators, or use no characters at all.

More Linux date formatting information

For more information on other Linux date formatting options (there are many more date formatting options), take a look at the man page for the date command on your Linux system, like this:

man date

When I run that command on a CentOS Linux system, it shows these date formatting operators:

%%     a literal %
%a     locale’s abbreviated weekday name (e.g., Sun)
%A     locale’s full weekday name (e.g., Sunday)
%b     locale’s abbreviated month name (e.g., Jan)
%B     locale’s full month name (e.g., January)
%c     locale’s date and time (e.g., Thu Mar  3 23:05:25 2005)
%C     century; like %Y, except omit last two digits (e.g., 21)
%d     day of month (e.g, 01)
%D     date; same as %m/%d/%y
%e     day of month, space padded; same as %_d
%F     full date; same as %Y-%m-%d
%g     last two digits of year of ISO week number (see %G)
%G     year of ISO week number (see %V); normally useful only with %V
%h     same as %b
%H     hour (00..23)
%I     hour (01..12)
%j     day of year (001..366)
%k     hour ( 0..23)
%l     hour ( 1..12)
%m     month (01..12)
%M     minute (00..59)
%n     a newline
%N     nanoseconds (000000000..999999999)
%p     locale’s equivalent of either AM or PM; blank if not known
%P     like %p, but lower case
%r     locale’s 12-hour clock time (e.g., 11:11:04 PM)
%R     24-hour hour and minute; same as %H:%M
%s     seconds since 1970-01-01 00:00:00 UTC
%S     second (00..60)
%t     a tab
%T     time; same as %H:%M:%S
%u     day of week (1..7); 1 is Monday
%U     week number of year, with Sunday as first day of week (00..53)
%V     ISO week number, with Monday as first day of week (01..53)
%w     day of week (0..6); 0 is Sunday
%W     week number of year, with Monday as first day of week (00..53)
%x     locale’s date representation (e.g., 12/31/99)
%X     locale’s time representation (e.g., 23:13:48)
%y     last two digits of year (00..99)
%Y     year
%z     +hhmm numeric timezone (e.g., -0400)
%:z    +hh:mm numeric timezone (e.g., -04:00)
%::z   +hh:mm:ss numeric time zone (e.g., -04:00:00)
%:::z  numeric time zone with : to necessary precision (e.g., -04, +05:30)
%Z     alphabetic time zone abbreviation (e.g., EDT)

Because Unix and Linux systems are very consistent in this area, you should be able to write a date formatting command on one Unix or Linux system, and use that same command on another Unix system.

http://www.devdaily.com/linux-unix/date-format-formatted-linux-shell-script-command