Home » SQL & PL/SQL » SQL & PL/SQL » sql generating sql
sql generating sql [message #183955] Mon, 24 July 2006 09:37 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I frequently will execute this statement to provide the commands to compile invalid objects:

select 'alter '||object_type||' '||object_name||' compile;' from user_objects where status = 'INVALID';

I'm attempting another with ' ' included within a statement for terminating sessions, but have been unable to figure it out:

set pagesize 0
select 'alter system kill session '||sid||','||serial#||';' from v$session;

How can I generate the statments for all sid & serial#?
i.e.
alter system kill session '25','123';

Thanks.
Re: sql generating sql [message #183958 is a reply to message #183955] Mon, 24 July 2006 09:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You had a lucky escape there. If your query had worked, you'd have killed off all the Oracle processes that keep your Db on it's feet, and it would probably have had to go for a long lie down.

You need to use '' to get a single quote in a string.

So, something like:

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';'
FROM   v$session
WHERE  username IS NOT NULL;


should do the job.

I wouldn't recommend killing sessions this way though. What are you trying to achieve?
Re: sql generating sql [message #183959 is a reply to message #183958] Mon, 24 July 2006 09:51 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I would eliminate Oracle processes from being included in the script. Occasionally, when I perform DB work for one of my clients, all users need to be disconnected from the database. Once the set time to disconnect has been reached, they enter sqlplus and kill each user session manually. I didn't recommend this method, but I figured I could try to make it easier for them with this statement.
Re: sql generating sql [message #183999 is a reply to message #183955] Mon, 24 July 2006 15:05 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
A much cleaner way to get rid of everybody is to perform a SHUTDOWN IMMEDIATE and then a STARTUP RESTRICT. This will kick everyone out and only allow dba uses to get back in.
Re: sql generating sql [message #184047 is a reply to message #183999] Tue, 25 July 2006 02:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's kind of drastic, and has the unfortunate side effect of flushing the shared pool and buffer cache, meaning that all the I/O you're doing after the restart is going to be physical I/O, and all your parses are going to be hard parses.

If there's no way you can get the application to log the users out, I'd be tempted to create a profile for the users, and when I wanted them all out, set the connect time to 1, and put the system into restricted session to stop them getting back in.
Re: sql generating sql [message #184113 is a reply to message #183958] Tue, 25 July 2006 07:38 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
To produce exact query that you want to kill, use this query

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||''','''||serial#||''';'
FROM v$session
WHERE username IS NOT NULL;
Re: sql generating sql [message #184121 is a reply to message #184113] Tue, 25 July 2006 08:07 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom's solution is the correct one. The proper format of KILL SESSION command is like:
ALTER SYSTEM KILL SESSION '7,795';
not
ALTER SYSTEM KILL SESSION '7','795';
Re: sql generating sql [message #184127 is a reply to message #184121] Tue, 25 July 2006 08:23 Go to previous message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Frame your where clause correctly. I usually used to do this way when db objects used to get locked.

CREATE OR REPLACE PROCEDURE Kill_Session AS
	   CURSOR CUR IS SELECT s.sid S1,
       s.serial# S2 FROM   v$session s WHERE UPPER(osuser) NOT LIKE 'OR%' AND LOWER(osuser) NOT LIKE '%sriv%';
	   TYPE kh IS RECORD (
	   		a NUMBER(20),
			b NUMBER(20));
	   H kh;
BEGIN
	 OPEN CUR;
	 LOOP
	 	 FETCH CUR INTO H;
		 EXIT WHEN CUR%NOTFOUND;
		 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '||CHR(39)||H.a||','||H.b||CHR(39);
	 END LOOP;
	 CLOSE cur;
END;
/


regards
Srivaths
Previous Topic: DEODE function - how many arguments?
Next Topic: Validate Email
Goto Forum:
  


Current Time: Sat Dec 10 06:38:00 CST 2016

Total time taken to generate the page: 0.15105 seconds