|
|
| Re: Urgent! Please help! Oracle time out session [message #59604 is a reply to message #59603] |
Sun, 07 December 2003 09:09   |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Sheila,
enable resource_limit and assign the user a profile whose idle_time is limited .
eg)
thiru@9.2.0:SQL>alter system set resource_limit=true;
System altered.
Elapsed: 00:00:04.03
thiru@9.2.0:SQL>set timing off
-- Create a profile limiting the idle_time to 1 minute.
thiru@9.2.0:SQL>create profile my_profile limit idle_time 1;
Profile created.
thiru@9.2.0:SQL>drop user test cascade;
User dropped.
-- Create the user and assign the profile
thiru@9.2.0:SQL>create user test identified by test PROFILE my_profile
2 ;
User created.
thiru@9.2.0:SQL>grant connect to test;
Grant succeeded.
thiru@9.2.0:SQL>connect test/test
Connected.
thiru@9.2.0:SQL>set time on
11:02:33 thiru@9.2.0:SQL>select * from user_users;
USERNAME USER_ID ACCOUNT_STATUS LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE
--------------- ---------- -------------------------------- --------- --------- --------------------------
TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP
------------------------------ --------- ------------------------------
EXTERNAL_NAME
----------------------------------------------------------------------------------------------------------
TEST 89 OPEN SYSTEM
TEMP1 07-DEC-03 DEFAULT_CONSUMER_GROUP
11:02:40 thiru@9.2.0:SQL>
-- After few minutes of being idle,he is disconnected
11:05:07 thiru@9.2.0:SQL>select * from user_users;
select * from user_users
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
-Thiru
|
|
|
|
|
|
| Re: Urgent! Please help! Oracle time out session [message #59607 is a reply to message #59606] |
Sun, 07 December 2003 12:57 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Prasad,
sqlnet.expire_time actually works on a different principle and is used to detect dead connections as opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes.
Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server.
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again.
But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.
-Thiru
|
|
|
|
| Re: Urgent! Please help! Oracle time out session [message #59633 is a reply to message #59603] |
Wed, 10 December 2003 07:30 |
scott
Messages: 73
Registered: September 1999
|
Member |
|
|
If you are willing to run a script to check for inactive sessions and store the info, here it is.
You can adjust the LAST_CALL_ET > VALUE to decide how long you want to allow a session to be inactive
Complete Script
----------------
CREATE OR REPLACE PROCEDURE KILL_INAC_USER
IS
----------------
--------- DECLARING VARIABLES
----------------
KILL_cur INTEGER;
KILL_cur_feedback INTEGER;
session_id NUMBER;
serial_no NUMBER;
--------------
--------- DECLARE A CURSOR TO GET THE SESSIONS THAT ARE INACTIVE
--------------
CURSOR cur_GET_SESSIONS
IS
SELECT * FROM V$SESSION WHERE STATUS = 'INACTIVE'AND USERNAME = 'UNAME'
AND LAST_CALL_ET > VAL IN SECONDS;
BEGIN
-- LOOP THROUGH THE RECORDS OBTAINED AND START KILLING THE INACTIVE SESSION
FOR result_GET_SESSIONS IN cur_GET_SESSIONS
LOOP
INSERT INTO TABLE_NAME(DATE_OF_KILL,UNAME,MACHINE,TERMINAL,SERIAL_NO,SESSION_ID,INACTIVE_TIME)
VALUES
(SYSDATE,result_GET_SESSIONS.USERNAME,result_GET_SESSIONS.MACHINE,
result_GET_SESSIONS.TERMINAL,result_GET_SESSIONS.SERIAL#,result_GET_SESSIONS.SID,
result_GET_SESSIONS.LAST_CALL_ET);
COMMIT;
session_id := result_GET_SESSIONS.SID;
serial_no := result_GET_SESSIONS.SERIAL#;
-- DYNAMIC CURSOR TO KILL THE SESSION
-- OPEN THE DYNAMIC CURSOR
KILL_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(KILL_cur,
'ALTER SYSTEM KILL SESSION ''' || session_id || ', ' || serial_no || '''',DBMS_SQL.NATIVE);
KILL_cur_feedback := DBMS_SQL.EXECUTE(KILL_cur);
-- CLOSE THE DYNAMIC CURSOR
DBMS_SQL.CLOSE_CURSOR(KILL_cur);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_SQL.CLOSE_CURSOR(KILL_cur);
-- DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
Scott
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
