about IDLE session timers in Apex 5

Introduction

In a recent project I wanted to use the idle session timer. The final goal was to give the end user a possibility to see how soon his session will end. It happens when the user enters a lot of data in a tabular form and work is interupted, that all the effort is in vain because the idle timeout forces a new login.

Martin D’Souza wrote an article about that some time ago: http://www.talkapex.com/2009/09/enhanced-apex-session-timeouts.html. But it is a bit outdated now, and he does not reuse the Apex settings, and simply uses his own timer that accidentically is the same as the Apex timeout. Better would be an approach to use the Apex settings.

Surprisingly it was not so easy to find where the idle session time is set and how to use it progammatically. Here are my findings.

How to set the idle timeout

See also this older blog post from Patrick Wolf about setting the timeout in Apex 3.2. Some of it is still true, however some has change since then. For example we can now set the timeouts also on workspace level.

Is your Oracle APEX session timing out?

There are 3-4 levels where the session idle timeout can be set.
Each specific level overwrites the setting of the more generic level.

Instance Level

Login as instance administrator (INTERNAL workspace).

Manage instance / Security / Session Timeout

The default is 1 hour (=3600 seconds).

Workspace Level

As a instance administrator (INTERNAL workspace) go to

Manage Workspaces / Existing Workspace / Click on Workspace Name / Session Timeout
 

Application Level

Application / Shared Components / Application Definition Attributes / Security / Session Management

 

Session Level

This can only be set programmatically.

http://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_util.htm#AEAPI355

BEGIN
APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => 1200);
END;

How to read the settings

Here are some commands that help to find out about the current session timeouts/idle timeouts. Note that to get the instance level settings (e.g. the default if nothing else was set), we can not use the official apex apis.
Update! This has been changed with version 5.0.4!

--- Find apex session idle times

-- Application level
select application_id, application_name, maximum_session_idle_Seconds, maximum_session_life_seconds, session_lifetime_exceeded_url, session_idle_time_exceeded_url
from apex_applications
--where application_id = :APP_ID
;

-- Application level - DBA access only
select security_group_id, display_id as application_id, name as application_name, max_session_length_sec, on_max_session_timeout_url, max_session_idle_sec, on_max_idle_timeout_url
from apex_050000.wwv_flows
--where display_id = :APP_ID
;

-- Workspace level
select workspace, workspace_display_name, maximum_session_idle_Seconds, maximum_session_life_seconds
from apex_workspaces;

-- Workspace level - DBA access only
select id, short_name, display_name, source_identifier, max_session_length_sec,max_session_idle_sec
from apex_050000.wwv_flow_companies;

-- Instance level - minimum Apex 5.0.4 needed + APEX_ADMINISTRATOR_READ_ROLE
select name, value
from APEX_INSTANCE_PARAMETERS
where name in ('MAX_SESSION_LENGTH_SEC','MAX_SESSION_IDLE_SEC');

-- Instance level - DBA access only
select name, value
from apex_050000.WWV_FLOW_PLATFORM_PREFS
where name in ('MAX_SESSION_LENGTH_SEC', 'MAX_SESSION_IDLE_SEC');

-- Instance level alternative - DBA access only
select apex_050000.wwv_flow_platform.get_preference('MAX_SESSION_LENGTH_SEC') as MAX_SESSION_LENGTH_SEC
      ,apex_050000.wwv_flow_platform.get_preference('MAX_SESSION_IDLE_SEC') as MAX_SESSION_IDLE_SEC
from dual;

-- Workspace settings including Instance default overwrites - DBA access only
alter session set current_schema = APEX_050000;

set serveroutput on
declare
  v_ws wwv_flow_security.t_workspace;
  v_security_group_id number;
begin
  wwv_flow_security.g_security_group_id := 10; -- Internal
  v_security_group_id := wwv_flow_security.find_security_group_id (p_company => 'MYWORKSPACE');
  v_ws := wwv_flow_security.get_workspace(v_security_group_id);
  dbms_output.put_line('ws max kill timeout='|| v_ws.qos_max_session_kill_timeout );
  dbms_output.put_line('ws max session time in sec='|| v_ws.max_session_length_sec );
  dbms_output.put_line('ws max idle time in sec='|| v_ws.max_session_idle_sec );
end;
/

Please note that since Apex 5.0.4 we are able to read the instance settings from an official Apex view.

Therefore the following little script will give us the session idle time, regardless where it is set.

select coalesce(
   ( -- Application level
    select maximum_session_idle_Seconds
    from apex_applications
    where application_id = v('APP_ID'))
   ,( -- Workspace level
     select maximum_session_idle_Seconds
     from apex_workspaces
     where workspace = v('WORKSPACE_ID'))
   ,(-- Instance level
     select to_number(value)
     from APEX_INSTANCE_PARAMETERS
     where name ='MAX_SESSION_IDLE_SEC')
     ) max_idle_time     
from dual;

Unfortunatly to read from this APEX_INSTANCE_PARAMETERS view we need the new APEX_ADMINISTRATOR_READ_ROLE. The role is automatically granted to DBAs, Apex Admins and some other privileged database accounts during installation of Apex 5.0.4.

If you don’t have it already, then you can grant it like this

grant APEX_ADMINISTRATOR_READ_ROLE to mySchema;

Advertisement