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

advertisements

_____________________________________________________________________________________________________________________

Error Description:

Delete command on one table is working properly. But truncate command is failing with following error.

SQL> SELECT COUNT(*) FROM SCOTT.QII_TSKERR_CMSQTAB;

  COUNT(*)

----------

      2125

SQL>   DELETE FROM SCOTT.QII_TSKERR_CMSQTAB;

3 rows deleted.

SQL> truncate table SCOTT.QII_TSKERR_CMSQTAB;

truncate table SCOTT.QII_TSKERR_CMSQTAB

                       *

ERROR at line 1:

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

SCOTT.QII_TSKERR_CMSQTAB

SQL> truncate table SCOTT.QII_TSKERR_CMSQTAB;

truncate table SCOTT.QII_TSKERR_CMSQTAB

                       *

ERROR at line 1:

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

Solution Description:

You cannot directly perform any DDL command to the queue tables. Truncate is a DDL command and delete is a DML command. All DDL commands to the Queue tables need to perform though some Oracle standard packages. Here is the example to purge the records from the Queue table.

DECLARE

qt dbms_aqadm.aq$_purge_options_t;

BEGIN

   qt.block := FALSE;

   DBMS_AQADM.PURGE_QUEUE_TABLE(

     queue_table     => 'scott.QII_TSKERR_CMSQTAB',

     purge_condition => NULL,

     purge_options   => qt);

END;

/

PL/SQL procedure successfully completed.

See more details here http://docs.oracle.com/cd/B28359_01/server.111/b28420/aq_admin.htm

_____________________________________________________________________________________________________________________

Website Stats