2 Replies Latest reply: Oct 9, 2013 5:13 AM by user648708 RSS

    Purge AQ table AQ$_<QUEUE_TABLE_NAME>_S

    Angela0086-Oracle

      https://mosemp.us.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=1156523.1note 1156523.1

      We followed the note 115653.1 to purge queue table APPLSYS.FND_CP_GSM_OPP_AQTBL and related tables.

      DECLARE
      po dbms_aqadm.aq$_purge_options_t;
      BEGIN
      po.block := FALSE;
      DBMS_AQADM.PURGE_QUEUE_TABLE(
      queue_table => 'APPLSYS.FND_CP_GSM_OPP_AQTBL',
      purge_condition => NULL,
      purge_options => po);
      END;
      /
      After the code executed, the records in the following tables are all purged except  table AQ$_FND_CP_GSM_OPP_AQTBL_S
      APPLSYS.FND_CP_GSM_OPP_AQTBL
      AQ$_FND_CP_GSM_OPP_AQTBL_T
      AQ$_FND_CP_GSM_OPP_AQTBL_H
      AQ$_FND_CP_GSM_OPP_AQTBL_I
      AQ$_FND_CP_GSM_OPP_AQTBL_G

       

      We tried the to use the above code to purge AQ$_FND_CP_GSM_OPP_AQTBL_S, but the got the error 'ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters'.

       

      My questions is

      1. Why queue table and other tables with suffix _T, _H, _I and _G can be purged with the above  code but the table with suffix _S can not?

      2. What is purge criteria to AQ$_<QUEUE_TABLE_NAME>_S when executing DBMS_AQADM.PURGE_QUEUE_TABLE to purge <QUEUE_TABLE_NAME>?

      3. Can I use the DML delete to delete the records from _S table directly.

       

      Thanks & Best regards,

        • 1. Re: Purge AQ table AQ$_<QUEUE_TABLE_NAME>_S
          ankitbarsainya

          You can try stopping the queue and then executing the same code..

          might be the case where an up and running queue would still hold connections to this one.

          Even i'm not sure about this but you can give it a try.

          Regards,

          Ankit

          • 2. Re: Purge AQ table AQ$_<QUEUE_TABLE_NAME>_S
            user648708

            Hello,

             

            the reason for not purging the AQ$"_S" table is that this table contains purely static informations about your AQ configuration

            These are no runtime data to purge!

             

            >What is purge criteria to AQ$_<QUEUE_TABLE_NAME>_S when executing DBMS_AQADM.PURGE_QUEUE_TABLE to purge <QUEUE_TABLE_NAME>?

             

            If you will setup a new AQ configuration these data will also be updated / recreated again.

             

            >Can I use the DML delete to delete the records from _S table directly.

             

            No, this makes no sense. You should never ever edit the AQ$ manually!

             

            Kind regards,

             

            WoG