Change Oracle Profile idle Time | Application Will disconnect if it is IDLE | Sniped Session

select * from dba_profiles;

select profile, limit from DBA_PROFILES

where profile = 'DEFAULT'

and resource_name = 'IDLE_TIME';

select profile, limit from DBA_PROFILES

where profile <> 'DEFAULT'

and resource_name = 'IDLE_TIME';

alter profile BOPOLICY LIMIT IDLE_TIME 30;

alter profile DEFAULT LIMIT IDLE_TIME 30;

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;

It is changed to 30 min of idle time. The application will disconnect if it is idle for 30.

Status of session will be marked as sniped.

TO clean sniped session, need to schedule job to kill sniped session. 

CREATE OR REPLACE PROCEDURE "FLUSH_SNIPED_SESSION" 

AS

BEGIN

    FOR X IN (

            SELECT inst_id,SID, SERIAL#

            FROM GV$SESSION

            WHERE USERNAME IS NOT NULL

             AND STATUS='SNIPED'

        ) LOOP

        EXECUTE IMMEDIATE 'alter system disconnect session '''|| X.SID

                     || ',' || X.SERIAL# || ',@' || X.inst_id || ''' immediate';

    END LOOP;

END;

/

BEGIN

DBMS_SCHEDULER.CREATE_JOB(job_name        => 'FLUSH$SNIPED$SESSION',

                          job_type        => 'STORED_PROCEDURE',

                          job_action      => 'FLUSH_SNIPED_SESSION',

          start_date      => '10-MAR-13 10:00.00.00 AM ASIA/CALCUTTA',

                          repeat_interval => 'freq=minutely; interval=5;byhour=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23',

                          end_date        => NULL,

                          enabled         => TRUE,

                          comments        => 'Flush Sniped Session Kshitij Agarwal');

END;

/

exec dbms_scheduler.run_job('FLUSH$SNIPED$SESSION');