locked objects

[MarcLara]

I am trying to write a script that I could use to kill invalid sessions. This is what I got so far. There will be times when there are multiple invalid sessions. How do I put the sid and serial_num variables into the alter system statement?

declare
sid number(10,0);
serial_num number(10,0);
rec_cursor is
select sid, serial# from v$session a, sys.v_$locked_object b where
b.session_id = a.sid and b.xidsqn = 0;
begin
for rec in rec_cursor
loop
ALTER SYSTEM KILL SESSION 'sid,serial_number';
end loop;
end;
/

 

[rcurva]

You may need to read about dynamic SQL.

Syntax (pre 8.1.6):

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record}]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...];

From 8.1.6 the syntax has been extended to include a "returning into"
clause:

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record}]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...]
[{RETURNING | RETURN} INTO bind_arg3 [, bind_arg4] ...];

where "dynamic_sql_string" may be any of non-query DML, single row
query, anonymous PL/SQL block, call statement, transaction control
statement, DDL or session control command.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

To use quotes within string literal you need duplicate them:

....
for rec in rec_cursor
loop
execute immediate 'ALTER SYSTEM KILL SESSION '''
||rec.sid||','||rec.serial#||'''';
end loop;
....