RE: SQLPLUS issue from ksh script
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <Ron.Reidy@xxxxxxxxxxxxxxxxxx>, <david.barbour1@xxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 May 2006 12:40:39 -0400
Put it into a sql script so that the KSH doesn't see it. Your KSH
script then becomes
sqlplus "/ as sysdba" @kill.sql
_____
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Reidy, Ron
Sent: Friday, May 26, 2006 12:34 PM
To: david.barbour1@xxxxxxxxx; Oracle-L Freelists
Subject: RE: SQLPLUS issue from ksh script
Escape the "$" in v$session => v\#session
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Barbour
Sent: Friday, May 26, 2006 10:29 AM
To: Oracle-L Freelists
Subject: SQLPLUS issue from ksh script
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.
_____
This electronic message transmission is a PRIVATE communication which
contains information
which may be confidential or privileged. The information is intended to
be for the use of the individual
or entity named above. If you are not the intended recipient, please be
aware that any disclosure,
copying, distribution or use of the contents of this information is
prohibited. Please notify the sender
of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0),
and then delete it from your system.
Other related posts:
