Oracle E-Business Suite 12.1.X – PURGING SELF SERVICE SESSIONS

1  Introduction

Failure to purge the self-service session and temporary data will over time result in performance issues and large volumes of temporary data being stored on-line.

Even if you have not installed Oracle Self-Service Web Applications you may still need to purge self service sessions as some functions of the Self Service Web Applications are available to the base E-Business Suite Application. If those functions are used, the Self Service temporary objects will grow.

The Self Service purge concurrent program is not assigned to the System Administrator Responsibility by default. That is most probably the reason why most Administrators miss it.

It is designed to clear out information on any session older than four hours (repeated for emphasis). Caution must be taken when running this in production since ACTIVE users who have been logged in for more than 4 hours will also lose their entry in ICX_SESSIONS. This will eventually cause them to be kicked out of applications with the error message “Your log on session has become invalid. Exiting Oracle Applications.” which is slightly different than the timeout window in that no opportunity is offered to log back in and continue.  The kicked out users can immediately log back in from the beginning, but will find this annoying.

Self-Service session database objects are:

ICX_SESSIONS

ICX_SESSION_ATTRIBUTES

ICX_TRANSACTIONS

ICX_TEXT

ICX_CONTEXT_RESULTS_TEMP

ICX_FAILURES

FND_SESSION_VALUES

FND_PS_TXN

2  Purging Self Service Sessions

In this section we will determine the amount of on-line Self Service Data, discuss the amount of history to keep on-line, and the steps to assign the “Purge Inactive Sessions” concurrent program to the System Administrator responsibility.

2.1 Determine the amount of on-line data

Run a row count on the 8 objects mentioned in the Introduction Section. Also get the age of the oldest record in the icx_session table (to get an idea of how old the data is).

SELECT min(first_connect), cont(*)

FROM icx.icx_sessions;

Run row counts after the purge to see how much space you have reclaimed.

2.2 How much data to keep?

Decide on the amount of on-line history you wish to keep in the Self-Service sessions tables.Do not hold less than 1 day’s data as this may cause abnormal termination of active Self Service sessions that have been active for longer than 1 day.

2.3 Edit the program FNDDLTMP.sql

Edit FNDDLTMP.sql only should you wish to change the amount of history to be held online.

$FND_TOP/sql/FNDDLTMP.sql

NOTE: The UNDO tablespace may become full if you have never purged self-service session data before. You either have to increase the size of the UNDO tablespace or run the purge manually in batches starting with all records older than 1 year, then 6 months, etc..

2.4 Make the program available to the System Administrator

Assign the “Purge Inactive Sessions” program to the System Administrator Responsibility by adding the program to the “System Administration Reports” Request Group.

Responsibility:   System Administrator

Navigation:        Security > Responsibility > Request

purge_self_service_ses1

2.5 Run a weekly scheduled request

Generally this Request is only run at night or during a time when there are only a minimum number of active users.

2.6 Maintenance activities

Run a regular index rebuild on the following objects:

ICX_SESSIONS

ICX_SESSION_ATTRIBUTES

ICX_TRANSACTIONS

ICX_TEXT

ICX_CONTEXT_RESULTS_TEMP

ICX_FAILURES

FND_SESSION_VALUES

FND_PS_TXN

Collect stats at least weekly on the ICX schema

3.  References: My Oracle Support

Can Not Login To The Applications: oracle.apps.fnd.framework.OAException: FND_NO_TRANSACTION_ID error (Doc ID 737960.1)

Framework JSP Pages Errors With FND, Message Name: FND_NO_TRANSACTION_ID (Doc ID 1577877.1)

Does Delete Data From Temporary Table Concurrent Program Need To Be Schedule In IProcurement? (Doc ID 367774.1)

Author:  Marie

Marie has over 12 years ICT experience and her experience includes:

• Oracle Applications (E-Business Suite) R11i and R12 – 11 years
• Oracle SQL – 10 years
• Oracle RDBMS (8/9i/10g/11g) – 12 years
• Oracle Enterprise Manager (10g and 11g) – 8 years
• Microsoft SQL Server – 3 years
• Novell Netware – 4 years

Featured Posts Slider Photo Credit : Collington (creative commons license)