purge ESB instances

Background

Within SOA suite there is console to view and manage ESB instances.
When trying to purge instances the process might take a long time, and the log could contain entries like:

11/03/16 10:37:39 at oracle.tip.esb.monitor.manager.database.DBActivityMessageStore.purge(DBActivityMessageStore.java:1092)
11/03/16 10:37:39 … 20 more
11/03/16 10:37:57 oracle.tip.esb.console.exception.ConsoleException: An unhandled exception has been thrown in the ESB system. The exception reported is: “oracle.tip.esb.monitor.MonitorException: Could not purge the messages due to the error : ORA-01795: maximum number of expressions in a list is 1000

Workaround

Oracle provides sql-procedures for this, in folder $ORACLE_HOME/integration/esb/sql/other.

steps:

  1. $ORACLE_HOME\integration\esb\sql\other> sqlplus oraesb/[password]@(description=(address_list=(address=(protocol=tcp)(host=[hostname])(port=[port])))(connect_data=(sid=[sid])))
  2. sql>@purge_by_date.sql
  3. sql>alter session set nls_date_format=’YYYY-MM-DD’;
  4. sql>exec purge_by_date_package.purge_by_date(to_date(’2010-10-19′));
  5. sql>commit;

The date_format could also just be YYYY-MM-DD, or any valid date format.

sql>alter session set nls_date_format=’YYYY/MM/DD hh24:mi:ss’;
sql>exec purge_by_date_package.purge_by_date(to_date(’2010/10/19 00:00:00′));

If you hit the error:
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1′
try to delete using smaller date window, like deleting month by month.

Comment are closed.