I'm missing something easy here, so I'd really appreciate a fresh set
of eyes. I've got a persistent jdbc connection to a primary
database I need to get rid of before I can switch to the standby.
The only way I've been able to come with to get rid of it for the time
I need to switch over is to lock the user account, find the SID and
SERIAL# of the session(s) and kill them. Maybe there's a better
way, but it works. Until I try to script it. Here's the
relevant portion of the script(ksh):
declare
STR VARCHAR2(2000);
begin
for x in (select * from v\$session) LOOP
IF x.user# = 27 then
str := ' alter system kill session '''
|| x.sid || ',' || x.serial# ||'''';
execute immediate str;
END IF;
END LOOP;
end;
/
But when I run it I get:
sqlplus "/ as sysdba" <<EOJ
declare
STR VARCHAR2(2000);
begin
for x in (select * from v$session) loop
IF x.user# = 27 then
str := ' alter system kill session ''' || x.sid || ',' || x.serial# ||'''';
execute immediate str;
End if;
END LOOP;
end;
EOJ
session: Undefined variable.
logout
It works in "pure" sqlplus, just not in the script.
Ideas? Thanks.