DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, July 13, 2016

Steps to purge the FND_OPP_AQ Table if the count reaches the threshold

Steps to purge the FND_OPP_AQ Table if the count reaches the threshold


1) Bring down the Application services before performing this activity

2) Reboot the database once.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 3 00:33:57 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2228344 bytes
Variable Size             612372360 bytes
Database Buffers          440401920 bytes
Redo Buffers               13934592 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEV       READ WRITE
SQL>
SQL>
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;


  COUNT(*)
----------
    821105

SQL>
SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_2May16 as select * from applsys.FND_CP_GSM_OPP_AQTBL;

Table created.

SQL> conn
Enter user-name: applsys
Enter password:
Connected.
SQL>
SQL>
SQL> 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;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.




----------------------------------

[oraprod@mercury:PROD] su - applprod
applprod's Password:
$
$
$ envconc (Calling concurrent env)
$
$ cd $FND_TOP
$ ls
3rdparty    bin         fndenv.env  help        include     lib         mds         mesg        patch       reports     secure      usrxit
admin       bin_bkp     forms       html        java        log         media       out         perl        resource    sql         xml
$
$ cd patch/115/sql
$
$ sqlplus apps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 3 00:41:46 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> @afopp002.sql
Enter value for 1: applsys
Enter value for 2: dev8mgr
Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$
$
$
$ sqlplus apps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 3 00:51:19 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL>
SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

  COUNT(*)
----------
         0



Check whether the queue is enabled or not with the following sql
----------------------------------------------------------------

SQL>  select * from dba_queues where name = 'FND_CP_GSM_OPP_AQ';

Enqueu and Dequeue should be 'YES', If not run the below begin statement

If the queue status returned 'NO'. Enable the queue using the below statement.

conn applsys

Statement
---------
begin
 dbms_aqadm.start_queue(‘AQ$_WF_NOTIFICATION_OUT_E’,FALSE,TRUE);
 end;
 /

In our case the value was 'YES'. Hence did not perform the above statement and started the application.



Thank you All. Hope it is a useful document !

No comments: