Forms Session Timeout Issues-
Often when using forms in 11i, users may complain that their session times out and that they are presented with the following request to login even though they are busily working:
Decision
--------------------------------------------
Your log on session is no longer valid.
Would you like to log back in so you can
continue working? If you do not log in, any
outstanding data changes in your forms will
not be saved.
--------------------------------------------
[Yes] [No]
When the user is idle for more minutes than specified by the system profile option "ICX:Session Timeout" or the number of milliseconds specified by the session.timeout parameter in zone.properties (these values should indicate the same time), then the user will be prompted to login again.
-note-171261.1
For situations such as this, there are (at least) two detectors that are useful. They can be used independantly or together depending on the circumstance. The first detector is a PL/SQL query that shows the user's status, while the second detector shows the user's interaction with forms and the forms interaction with the ICX_SESSIONS table
Detector 1 - Watch the User's Status in the ICX_SESSIONS Table
col timeout format a8
col user_name format a10
col mins_idle format 999.99
select
disabled_flag,
to_char(first_connect,'MM/DD/YYYY HH:MI:SS') Start_Time,
to_char(sysdate,'HH:MI:SS') Current_Time,
USER_NAME,
session_id,
(SYSDATE-last_connect)*24*60 Mins_Idle,
fnd_profile.value_specific
('ICX_SESSION_TIMEOUT',
a.user_id,
a.responsibility_id,
a.responsibility_application_id,
a.org_id,
NULL
) TimeOut
from
ICX_SESSIONS a, fnd_User b
where
a.user_id=b.user_id
and last_connect > sysdate-1/24;
EXAMPLE OUTPUT
==============
D START_TIME CURRENT USER_NAME SESSION_ID MINS_IDLE TIMEOUT
- ------------------- -------- ---------- ---------- --------- --------
N 09/30/2006 05:28:50 05:29:00 SYSADMIN 568577602 .17 30
N 09/30/2006 05:22:23 05:29:00 DCOLLIER 469704674 6.62 30
Understanding the Session Timeout Query (Detector 1)
The session timeout query is meant to be run again and again to confirm that a user's activity is being counted and can also be used for determining what activity is counted against the idle timeout and what is not. For example, when practicing with this you may note that when a user is within core forms the ICX_SESSIONS table is not updated with every single click. Instead, the update is made only if at least one minute has passed since the last update. The session begins when the user logs into applications using the supported "AppsLogin" method, but there is no session created (and therefore never a timeout window) when logging in directly via the unsupported "/dev60cgi/f60cgi" URL.
Column Specifics
D - The disabled flag. If this is set to 'Y', then the session has been explicitly disabled, such as from a user logging out. If this is 'N', then the session is not disabled.
START_TIME - The time of day the user first logged in.
CURRENT - The current time of day; useful when running the query repeatedly as this timestamps each query run.
USER_NAME - The login name of the user.
SESSION_ID - The session ID of the user. This can be confirmed from forms using the following navigation from the "System Administrator" responsibility:
- Help/Diagnostics/Examine
- - Block:$PROFILES$
- - Field: ICX_SESSION_ID
- - Value: Click this field for the value to appear
(The value of my session_id=469704674 as seen in the above Detector1 query result)
MINS_IDLE - The perceived number of minutes that the user has been idle.
TIMEOUT - The value of "ICX:Session Timeout" for the specific user
The query, as written, will dump a listing of all users that have connected within the last hour. If different users have different values for "ICX: Session Timeout", then this will be immediately apparent.
Certainly, the where clause can be altered (where session_id=469704674) to just watch a specific session, or even "where user_name='SOMEUSER' ", for example, to show all sessions open for a particular user. Early versions of 11i had problems with "session confusion" in that the timeout window created a new session rather than validating an existing session (bug:3102240, bug:3305591) and therefore user activity was counted in one session while the timeout was watching another. This led to an inability to revalidate an existing session and the repeated display of the timeout popup window.
Finally, the query, when evaluating the profile option value of "ICX:Session Timeout" only considers the site, application, responsibility, and user level. Typically these are the only levels that are allowed, but when troubleshooting it may be useful to see if an applications DBA has overridden these defaults and introduced a peculiar value. The following query will confirm that only these four levels need to be checked:
select
SITE_ENABLED_FLAG,
APP_ENABLED_FLAG,
RESP_ENABLED_FLAG,
USER_ENABLED_FLAG,
ORG_ENABLED_FLAG,
SERVER_ENABLED_FLAG,
SERVERRESP_ENABLED_FLAG
from
fnd_profile_options
where
upper(profile_option_name) = 'ICX_SESSION_TIMEOUT';
EXAMPLE OUTPUT
==============
S A R U O S S
- - - - - - -
Y Y Y Y N N N
See the above query for the full names of the columns. This query result just indicates the completely normal instance where the "ICX:Session Timeout" system profile option can only be set at the Site, Application, Responsibility, and User levels.
An Example Application of the Session Timeout Query
As described above, the session timeout query (Detector 1) can be rewritten as follows:
col timeout format a8
col user_name format a10
col mins_idle format 999.99
select
disabled_flag,
to_char(first_connect,'MM/DD/YYYY HH:MI:SS') Start_Time,
to_char(sysdate,'HH:MI:SS') Current_Time,
USER_NAME,
session_id,
(SYSDATE-last_connect)*24*60 Mins_Idle,
fnd_profile.value_specific
('ICX_SESSION_TIMEOUT',
a.user_id,
a.responsibility_id,
a.responsibility_application_id,
a.org_id,
NULL
) TimeOut
from
ICX_SESSIONS a, fnd_User b
where
a.user_id=b.user_id
and user_name='&USER_NAME_IN_UPPER_CASE';
Things to look for:
1. Verify that the user has only one active session.
This rendition of the query will prompt you to enter the username of the user you are watching and will display all rows with that username. If the user has more than one active session, this will be indicated by having more than one row with 'N' under the "Disabled" column and a "Mins_Idle" less than the timeout value.
2. Confirm that the user's activity is being counted at allAs the user works, the "Mins_Idle" should change at least every minute and not accumulate more than a few minutes of time. Depending on what form the user is using and what exactly the user is doing (long running query, etc.) the "Mins_Idle" can accumulate. The key is finding conditions where the user is busily updating a form, but the "Mins_Idle" continues to accumulate in excess of several minutes. Such forms, often custom, were not built correctly to make the check_session calls described below in the section on "Detector 2". Detector 2 watches user activity from a different perspective.
3. Confirm that the proper ICX_SESSION is updated.The only session that should be updated by the user's activity within forms should be the one identified by the ICX_SESSION_ID profile field as seen by navigating to Help/Diagnostics/Examine as detailed above.
It is somewhat normal to have several hundred rows returned because the ICX_SESSIONS table contains information on both active and inactive sessions for everyone who has logged in since the table was last cleaned. It is generally recommended to schedule the concurrent request "Delete Data From Temporary Table" to run at least weekly. This request runs the script $ICX_TOP/sql/ICXDLTMP.sql which deletes any information in ICX_SESSIONS (and its dependant tables) that is more than 4 hours old and alleviates the performance issues associated with letting these tables grow with obsolete data. This script can be run manually.
Before watching any particular user, it is best to tidy up these tables by running the $ICX_TOP/sql/ICXDLTMP.sql script. This script is run by the apps user from sqlplus, requires no arguments, and 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. Generally this script is only run at night or during a time when there are only a minimum number of active users.
Detector 2 - Track the User's Perceived Idle Status via Forms Runtime Diagnostic
The second detector is simply just a "tail -f" of an FRD (forms runtime diagnostic) with a grep for session tickling. Clearly, this detector is only valid for forms users whereas the first detector will also work with self-service users. When I run this test, on one PC I have the 11i forms session where I am logged in. On another PC, so that I can see behavior immediately, I have two telnet windows that serve as user activity detectors. Having these two detectors displayed on a laptop while sitting next to a specific user that constantly complains of being timed out is instructive. Quite often a user's trip to the water cooler is much longer than they admit to on a help ticket and therefore the session timeout on their unattended workstation is doing exactly what it should do.
Each of the two telnet windows is running one of the user activity detectors:
Detector1 - the time checking query as above
Detector2 - the command "tail -f username_frd.log | grep -n fnd_session.check_session"
With Detector2, the "username_frd.log" is an ongoing FRD trace as setup by
Note:150168.1 section 'C'. Essentially the FRD is activated by navigating via System Administrator to Profile/System and querying up the profile option "ICX: Forms Launcher" and specifying to display at the Site and user level for whatever user you will be tracing. You can copy the current Site level value to the user level value and then add the FRD arguments to trace only that specific user. For example:
http://server.domain:8000/dev60cgi/f60cgi?record=all&log=username_frd.log will create a "username_frd.log" file with tracing details of just one specific user. Note that the log will be written on the forms server in the directory specified by the environment variable FORMS60_TRACE_PATH regardless of the location specifed for the log.
When testing, you should find that for every single time the user clicks in a field, Detector2 gives two more lines of the following where each pair of lines has their own, new line numbers indicating activity:
29441:In Argument 0 - Type: String Value: Entering fnd_session.check_session.
29482:In Argument 0 - Type: String Value: Completed fnd_session.check_session.
The definitions for fnd_session.check_session and fnd_session.session_status are in $AU_TOP/resource/FNDSQF.pll and are they main driver for the session timeout determination within forms. The function "check_session" calls "session_status" which calls FND_ICX_SEC.CHECK_SESSION in the database which uses a query similar to "Detector1", above, to determine timeout status.
A look at the code for session_status within FNDSQF.pll makes the difference between the two detectors clear:
PACKAGE BODY fnd_session IS
last_verify_time DATE := NULL; /* The last time session verified*/
check_delta NUMBER := 1/(24*60); /* = 1 minute. Number of Days betw chks */
If "last_verify_time" (which is set to fnd_standard.system_date on exit) is greater than one minute, then session_status won't bother the database with a call to FND_ICX_SEC.CHECK_SESSION and will just return that the session is valid. If "last_verify_time" is greater than one minute, then there will be a call to FND_ICX_SEC.CHECK_SESSION and the session tickler will update the last_connect time which can be seen in the results of the Detector1 query. Of course, the actual code within FNDSQF will vary with each version, so it is always useful to know what version of FNDSQF is in use by reading the header line (for example: strings -a $AU_TOP/resource/FNDSQF.pll | grep '$Header').
Detector 2 is useful in determining what activity within forms counts against the idle timeout clock. It is useful to demonstrate the effectiveness of this detector on a known good form such as the profile options query form from the System Administrator responsibility. Essentially any mouse click into any field results in two lines being added to the "tail -f|grep" output, while not clicking anything leaves the telnet window seemingly hung. This is a clear, interactive demonstration that the session tickler is working.
Certain product teams forms and certain custom forms do not properly call fnd_session.check_session and therefore don't count user activity. If by using these methods you find an Oracle form behaving this way, get the shortname and version of that form (Help/About Oracle Applications) along with the FNDSQF.pll versoin.
![[identityManager.jpg]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWqFmnG2DDe7uo_dwcxJde7IVI5XQqdDvdW-nbJNb-DcLW1Iaaj0A1o9F9Qi6cSAKj0upIbsfk_TkDXsM7acxoqdRJQrh2c03KgGUestJn_PIKp-5odN6GH6NUwMhI8SrlOgkoPviEn50/s1600/identityManager.jpg)