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


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.

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google