OraFAQ Forum: SQL & PL/SQL » Kill a user session from a database procedure

Home » SQL & PL/SQL » SQL & PL/SQL » Kill a user session from a database procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Kill a user session from a database procedure [message #4903]

Mon, 20 January 2003 08:32

billy
Messages: 9
Registered: March 2002

Junior Member

Hello can someone please help me here. I am a newbie using PL/SQL.

I would like to implement this kill_session procedure but it's not working:

I am running Oracle8 Enterprise Edition Release 8.0.5 on Windows NT 4.0.

I have logged on as SYS and I am using Oracle Enterprise Manager SQL Worksheet.

First I have created this procedure:

create or replace procedure kill_session( p_sid in varchar2,
p_serial# in varchar2)
is
cursor_name pls_integer default dbms_sql.open_cursor;
ignore pls_integer;
BEGIN
select count(*) into ignore
from V$session
where username = USER
and sid = p_sid
and serial# = p_serial# ;

if ( ignore = 1 )
then
dbms_sql.parse(cursor_name,
'alter system kill session '''
||p_sid||','||p_serial#||'''',
dbms_sql.native);
ignore := dbms_sql.execute(cursor_name);
else
raise_application_error( -20001,
'You do not own session ''' ||
p_sid || ',' || p_serial# ||
'''' );
end if;
END;
/

This procedure was created successfully:

I than proceeded and granted these privileges:

GRANT ON V_$SESSION TO PPSPROD;
GRANT ALTER SYSTEM TO PPSPROD;
GRANT EXECUTE ON KILL_SESSION TO PPSPROD;

When I went to execute the procedure I received this error:

SQLWKS> EXECUTE KILL_SESSION;
KILL_SESSION;
*
ORA-06550: line 2, column 2:
PLS-00201: identifier 'KILL_SESSION' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

What am I missing....... :-(

Thanks
Billy

Report message to a moderator

Re: Kill a user session from a database procedure [message #4907 is a reply to message #4903]

Mon, 20 January 2003 10:34

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

NOT GOING into the details and functionality of procedure,
the procedure is owned by sys.
you are granting execute priv on the priv to another user PPSPROD.
then user PPSPROD should execute the procedure
as
exec sys.kill_session(parmeter1,parameter2);
Note:
You didnt menttion the usage of parameters.
that will again whine with some errors

Report message to a moderator

Re: Kill a user session from a database procedure [message #4915 is a reply to message #4907]

Mon, 20 January 2003 15:02

billy
Messages: 9
Registered: March 2002

Junior Member

Mahesh thank you for your reply. I have decided to use another procedure, I would like to explain what I did and what I am trying to achive to give you a better picture:

I have a couple of users who forget to log off there sessions causing tables to lock up and causing our production batch jobs to fail in the evening:

I understand that sessions are killed via the command ALTER SYSTEM KILL SESSION 'SID,SERIAL#' where SID AND SERIAL# are obtained from the V$SESSION dynamic performance table.

What I am looking for is a procedure that will get 'SID & SERIAL#' from V$SESSION and excute a LOOP that will automatically KILL ALL SESSIONS.
If I can execute this procedure either by SQL Worksheet or SQL*PLUS I than can proceed to put a call in one of our batch jobs so it can call the procedure and kill any user session before our evening batch jobs run.

As stated before I am running:

Oracle8 Enterprise Edition VLM Release 8.0.5.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.5.0.0 - Production

I also learned that the EXECUTE IMMEDIATE statement was not available in Oracle8, but only in Oracle8i and above. The DBMS_SQL package was used to create dynamic SQL inside PL/SQL. In older versions of Oracle, you can simulate the behaviour by creating your own execute_immediate procedure that calls the DBMS_SQL procedures.

So this is what I have done:

I have logged on as user PPSPROD using SQL*PLUS, in the PPSPROD schema I have created the execute_immediate procedure:

SQL> show user;
USER is "PPSPROD"
SQL> create or replace
2 procedure execute_immediate( p_sql in varchar2 )
3 is
4 cursor_name pls_integer default dbms_sql.open_cursor;
5 ignore pls_integer;
6 BEGIN
7 dbms_sql.parse(cursor_name, p_sql, dbms_sql.native);
8 ignore := dbms_sql.execute(cursor_name);
9 dbms_sql.close_cursor(cursor_name);
10 END;
11 /

Procedure created.

I than proceeded to use this procedure.

create or replace procedure kill_all
as

CURSOR user_list IS
SELECT sid, serial#
FROM v$session
WHERE username is not null
and audsid <> SYSCONTEXT('USERENV','SESSIONID');

BEGIN

FOR rec IN user_list LOOP

execute_immediate 'alter system kill session '''||sid||','||serial#||'''';

END LOOP;

END;
/

But when I ran this procedure I received an error:

SQL> create or replace procedure kill_all
2 as
3
4 CURSOR user_list IS
5 SELECT sid, serial#
6 FROM v$session
7 WHERE username is not null
8 and audsid <> SYSCONTEXT('USERENV','SESSIONID');
9
10 BEGIN
11
12 FOR rec IN user_list LOOP
13
14 execute_immediate 'alter system kill session '''||sid||','||serial#||'''';
15
16 END LOOP;
17
18 END;
19 /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE KILL_ALL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
14/19 PLS-00103: Encountered the symbol "alter system kill session '"
when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "alter system kill session '"
to continue.

This procedure is exactly what I need, what do I need to fix this error....?

Thanks Mahesh for your time and understanding

Report message to a moderator

Re: Kill a user session from a database procedure [message #4917 is a reply to message #4915]

Mon, 20 January 2003 17:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

there was a similiar thread a few days ago
pls look at the possible solution for that(i got no feedback).
[url=http://www.orafaq.net/msgboard/serverutil/messages/6776.htm]Re: Kill Inactive Session after 2 hours[/url]
it is almost the same. It kills users that have an in inactive session for more than 2 hours.
Change the procedure according to your need.
and u can use dbms_job(within oracle) or any cron job to
schedule the procedure to be executed periodically.
Whatever it is , 
execute immediate is not available for version prior 8i.
you have to use dbms_sql. 
so, change the proceudre using dbms_sql if your using oracle version that is less than 8i.

Report message to a moderator

Re: Kill a user session from a database procedure [message #4939 is a reply to message #4915]

Tue, 21 January 2003 14:25

billy
Messages: 9
Registered: March 2002

Junior Member

Mahesh thank you for your input. Who would you log on as to create the Kill Inactive Session after 2 hours procedure would you log on as SYS, SYSTEM or PPSPROD the user who is going to execute the procedure..???

Sorry if this sounds trivial........... :-)

Report message to a moderator

Previous Topic:	How to copy oracle schema
Next Topic:	Is it possible to create two tables with the same name in two different tablespaces in the same db?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 17 05:13:43 CST 2025