Oracle analytical functions to the rescue

 Below is a typical example of how using analytical functions speeds up a query, which is having a subquery inside.

Original query:

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
                        ALT_MODEL_DESC
                        FROM TEST1, TEST2, TEST3
                        WHERE TEST1.CUSTOMER_ID = 517091995
                        AND TEST1.UNIT_ESN = ESN
                        AND TEST1.ESN_SEQ_NO =
                        (SELECT MAX (ESN_SEQ_NO)
                        FROM TEST1 PD1
                        WHERE PD1.CUSTOMER_ID = TEST1.CUSTOMER_ID
                        AND PD1.SUBSCRIBER_NO = TEST1.SUBSCRIBER_NO
                        AND LENGTH(UNIT_ESN) <> 20 )
                        AND TEST2.TEST3 = TEST3.TEST3
                        AND ( TEST3.EXPIRATION_DATE  IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE          )

;


The new query, using analytical functions (much faster and accesing the TEST1 table only once):

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
                        ALT_MODEL_DESC
                        FROM
(select UNIT_ESN,ESN_SEQ_NO,CUSTOMER_ID, SUBSCRIBER_NO, max(ESN_SEQ_NO) over (partition by CUSTOMER_ID,SUBSCRIBER_NO)  as max_ESN_SEQ_NO
from TEST1 where LENGTH(UNIT_ESN) <> 20 ) phd, TEST2, TEST3
                        WHERE phd.CUSTOMER_ID = 517091995
                        and ESN_SEQ_NO=phd.max_ESN_SEQ_NO
                        AND UNIT_ESN = ESN
                        AND TEST2.TEST3 = TEST3.TEST3
                        AND ( TEST3.EXPIRATION_DATE  IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE)
;

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

I was getting this error while running the RMAN duplicate database in 11g. As I was duplicating only selected tablespaces over, oracle internally was trying to drop the unnecesary tablespaces and the whole duplicate process was failing with as below:

 sql statement: drop tablespace  "TEST1" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/26/2013 13:06:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/26/2013 13:06:34
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace  "TEST1" including contents cascade constraints
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

RMAN> **end-of-file**


I was trying manually to drop the tablespace and I was getting the same error, but no queue tables whatsoever reside in this tablespace:

SQL> drop tablespace  "TEST1" including contents cascade constraints;
drop tablespace  "TEST1" including contents cascade constraints
*
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



SQL> select * from dba_queue_tables where table_name in
         (select segment_name from dba_segments where tablespace_name='TEST1';

no rows selected

 At this point, my goal was to find out which statement is failing exactly.

 Solution:

SQL> ALTER system SET EVENTS='24005 TRACE NAME ERRORSTACK FOREVER, LEVEL 1';

System altered.


I then checked the alert log file for ORA-24005 and then inside the trace file I've found the exact statement failing:


ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
----- Current SQL Statement for this session (sql_id=0000000000000) -----
drop table "florin"."DDD#DEF$_AQERROR" cascade constraints purge

 In conclusion, oracle treats a table name with special characters as a queue table.
 I was able to drop this table, using DBMS_AQADM.DROP_QUEUE_TABLE, indeed, and then I've dropped the tablespace without any issue.