Database server crashed after memory on server used by inactive sessions
The Following Example used to illustrate the scenario for User XX, please Review and modify whatever needed
to confirm Automatic Kill of the Huge number of Inactive Sessions after a period of Idle Time .
1.CREATE THE PLAN
begin
dbms_resource_manager.create_pending_area();
end;
/
begin
dbms_resource_manager.create_plan( plan => 'TEST_PLAN', comment => 'Resource plan/method for Idle
time kill sessions');
end;
/
6.ASSIGN DATABASE USERS A DEFAULT INITIAL CONSUMER GROUP AT CONNECTION TIME
For Example :
A)User SYSTEM is made member of the group LONG_RUNNING
B)User XX is made member of the group SHORT_RUNNING
begin
dbms_resource_manager.create_pending_area();
end;
/
begin
dbms_resource_manager.set_initial_consumer_group( user => 'SYSTEM',
consumer_group => 'LONG_RUNNING');
dbms_resource_manager.set_initial_consumer_group( user => 'XX',
consumer_group => 'SHORT_RUNNING');
end;
/
begin
dbms_resource_manager.validate_pending_area();
end;
/
begin
dbms_resource_manager.submit_pending_area();
end;
/
7.MAKE THE NEW PLAN THE ACTIVE ONE FOR THE DATABASE
SQL> alter system set resource_manager_plan=’TEST_PLAN’;
This query will show the sessions that are killed when the idle time thresholds set for the plan are crossed.
SQL> select NAME, ACTIVE_SESSIONS_KILLED, IDLE_SESSIONS_KILLED FROM V$RSRC_CONSUMER_GROUP WHERE NAME='SHORT_RUNNING';
SQL>select SID,MODULE,STATUS from V$session where USERNAME='XX';
SID MODULE STATUS
---------------------------------
365 SQL*Plus KILLED
