How to Force-Logout Idle or Stuck Oracle EBS Users With One PL/SQL Package (Free Script Inside)

If you've ever administered Oracle E-Business Suite, you know the pain: a user's session freezes, they lock a record, they leave for the day without logging out, or a "ghost" ICX session just sits there blocking data. Killing it manually through System Administrator > Monitor > Users works — until you need to do it for dozens of users at once, or automate it as part of a nightly job.

This post walks through a lightweight, production-ready PL/SQL package — XXBFARM_SESSION_LOGOUT_PKG — that force-disables active EBS sessions for one user (or all users) in a single concurrent program run, with full logging built in.

What the Script Actually Does

At its core, the package does three things:

1. Finds active sessions in ICX_SESSIONS that connected within the last 30 minutes and aren't already disabled.
2. Logs full session context — user name, responsibility, application, form, and last connect time — to the concurrent request log via FND_FILE.PUT_LINE.
3. Disables each session by flipping disabled_flag to 'Y', which is the same mechanism EBS itself uses when a session is terminated.

It's designed to run as a standard Concurrent Program, so you can schedule it, run it on demand, or trigger it from a workflow.

The Package Spec

CREATE OR REPLACE PACKAGE XXBFARM_SESSION_LOGOUT_PKG AS
  PROCEDURE logout_active_sessions
  (
    errbuf      OUT VARCHAR2,
    retcode     OUT VARCHAR2,
    p_user_name IN  VARCHAR2
  );
END XXBFARM_SESSION_LOGOUT_PKG;
/

Note the standard errbuf / retcode OUT parameters — this is what makes the procedure directly registerable as a Concurrent Program executable in EBS without any wrapper needed.

The Package Body

CREATE OR REPLACE PACKAGE BODY XXBFARM_SESSION_LOGOUT_PKG AS
  PROCEDURE logout_active_sessions
  (
    errbuf      OUT VARCHAR2,
    retcode     OUT VARCHAR2,
    p_user_name IN  VARCHAR2
  )
  IS
    l_updated_count NUMBER := 0;

    CURSOR c_sessions IS
      SELECT fu.user_id,
             fu.user_name,
             fu.description AS full_name,
             TO_CHAR(icxs.last_connect, 'DD-MON-YYYY HH24:MI:SS') AS last_connect,
             icxs.session_id,
             icxs.responsibility_application_id,
             fat.application_name,
             icxs.responsibility_id,
             frt.responsibility_name,
             icxs.function_id,
             fft.user_function_name AS form_name
      FROM icx_sessions icxs,
           fnd_user fu,
           fnd_application_tl fat,
           fnd_responsibility_tl frt,
           fnd_form_functions_tl fft
      WHERE icxs.user_id = fu.user_id
        AND icxs.responsibility_application_id = fat.application_id(+)
        AND icxs.responsibility_id = frt.responsibility_id(+)
        AND icxs.responsibility_application_id = frt.application_id(+)
        AND icxs.function_id = fft.function_id(+)
        AND NVL(icxs.disabled_flag, 'N') = 'N'
        AND icxs.last_connect > SYSDATE - (30 / 1440)
        AND fu.user_name = NVL(p_user_name, fu.user_name);
  BEGIN
    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'EBS Active Session Logout Program Started');
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Time          : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter User Name : ' || NVL(p_user_name, 'ALL'));
    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');

    FOR r IN c_sessions LOOP
      FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------------------------------');
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'User ID        : ' || r.user_id);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'User Name      : ' || r.user_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Full Name      : ' || r.full_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Session ID     : ' || r.session_id);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last Connect   : ' || r.last_connect);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Application    : ' || r.application_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Responsibility : ' || r.responsibility_name);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Form Name      : ' || r.form_name);

      UPDATE icx_sessions
      SET disabled_flag = 'Y'
      WHERE session_id = r.session_id
        AND NVL(disabled_flag, 'N') = 'N';

      IF SQL%ROWCOUNT > 0 THEN
        l_updated_count := l_updated_count + SQL%ROWCOUNT;
        FND_FILE.PUT_LINE(FND_FILE.LOG, 'Status         : LOGOUT UPDATED');
      ELSE
        FND_FILE.PUT_LINE(FND_FILE.LOG, 'Status         : NOT UPDATED / ALREADY DISABLED');
      END IF;
    END LOOP;

    COMMIT;

    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Sessions Logged Out : ' || l_updated_count);
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Time : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'EBS Active Session Logout Program Completed');
    FND_FILE.PUT_LINE(FND_FILE.LOG, '==============================================');

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      retcode := '2';
      errbuf  := SQLERRM;
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR OCCURRED');
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message : ' || SQLERRM);
      FND_FILE.PUT_LINE(FND_FILE.LOG, 'Program rolled back.');
  END logout_active_sessions;
END XXBFARM_SESSION_LOGOUT_PKG;
/

Registering It as a Concurrent Program

To use this from the EBS front end (not just SQL*Plus), register it like any custom PL/SQL concurrent program:

1. Define the Executable System Administrator > Concurrent > Program > Executable

   • Execution Method: PL/SQL Stored Procedure
   • Execution File Name: XXBFARM_SESSION_LOGOUT_PKG.logout_active_sessions

2. Define the Concurrent Program System Administrator > Concurrent > Program > Define

   • Attach the executable above
   • Add one parameter: P_USER_NAME (optional, VARCHAR2) — leave blank to log out all active users, or supply a specific user name to target just one.

3. Add to a Request Group so it's runnable from your responsibility of choice.

4. Run it from Submit a New Request, check the log for a full audit trail of who got logged out and when.

Why This Is Handy

• Targeted or bulk: pass a user name to force-logout one person, or leave the parameter null to sweep every active session.
• Full audit trail: every disabled session is logged with user, responsibility, form, and timestamp — useful for security reviews or troubleshooting "why was I logged out" tickets.
• Safe by design: only touches sessions connected in the last 30 minutes and not already disabled, so it won't churn through your entire session history.
• Standard EBS pattern: uses errbuf/retcode and FND_FILE, so it behaves exactly like any other concurrent program your ops team already knows how to run and monitor.

A Word of Caution

Before running this in production:

• Test in a non-prod instance first. Force-disabling a session mid-transaction can interrupt unsaved work.
• Restrict access to this concurrent program to admin-level responsibilities only — this is a session-management tool, not something to expose broadly.
• Consider notifying users if you're running this as a scheduled sweep, so people aren't surprised by sudden logouts