Re: Kill sessions

From: Mark Wagoner <mwagoner_at_iac.net>
Date: 1997/05/09
Message-ID: <5kv5jj$c80$1@ocoee.iac.net>#1/1

scheuric_at_sprynet.com (Sheilah Scheurich) wrote:

>On 8 May 1997 14:53:46 GMT, afc_at_afc.com (afc) wrote:
 

>>Hi all,
>>
>>Does anybody have a script that will kill all sessions in Oracle?.
>>
>>Cheers.
>>Chris
>>cookcs_at_logica.com
 

>This is a self generating script -
 

>select 'alter system kill session'
>||''''||sid||''''||','||''''||serial#||''''||';' from v$session where
>username is not null;
 

>You will try to kill yourself but Oracle won't let you.
 

>A much easier way to kill all sessions is to shut down immediate;
 

>Sheilah Scheurich
>DBA
I tried writting a stored procedure that will do this, but I get an Insufficient Privleges error, even when I call the procedure logged in as System. If I try to execute the statement directly, it work fine.

Doesn't a procedure execute under the same privleges as the user who called it? Any idea what I am doing wrong?

Here is a copy of the procedure. Any advice would be welcome.

create or replace procedure KILL_ALL as

	vSQL	varchar2(100);
	iRVAL	integer;
	iCUR	integer;
	cursor cCURSOR is
		select 'alter system kill session '||''''||SID||','||SERIAL#||''''
		from V$SESSION
		where USERNAME is not null and USERNAME != (select USER from dual);
begin
	open cCURSOR;
	loop
		fetch cCURSOR into vSQL;
		exit when cCURSOR%notfound;
		iCUR := dbms_sql.open_cursor;
		dbms_sql.parse(iCUR,vSQL,dbms_sql.v7);
		iRVAL := dbms_sql.execute(iCUR);
		dbms_sql.close_cursor(iCUR);
	end loop;
	close cCURSOR;
exception
	when OTHERS then
		raise_application_error(-20001,'Error executing '||vSQL,TRUE);
		if dbms_sql.is_open(iCUR) then
			dbms_sql.close_cursor(iCUR);
		end if;
		if cCURSOR%isopen then 
			close cCURSOR;
		end if;

end;

--
Mark Wagoner
mwagoner_at_medplus.com (work)
mwagoner_at_iac.net     (life)

Received on Fri May 09 1997 - 00:00:00 CDT