Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Solaris: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
We have a bad package that is causing phantom processes to build. The change is coming to fix it, but is suppose take a few weeks to get finished.
In the mean time I'm trying to (learning pl/sql) write a block to "search and destroy" these phantom processes.
I built the code first to just SHOW what it was going to do. And that part works. But when I add in the actual execution, I'm getting the PLS-00103 error.
Desired result is to spool a file that shows what sessions will be killed for record, and then kills them. Block will be put into a cron job to be called X times a day, and logging to the spool file.
Here is the code that works:
SET serveroutput ON;
DECLARE
v_sid NUMBER;
v_serial# NUMBER;
CURSOR b_ses_cur IS
SELECT sid, serial# FROM v$session WHERE module LIKE 'osc%';
BEGIN
dbms_output.put_line('spool /tmp/phantom_sessions.lst');
dbms_output.put_line('The following sessions will be killed.');
FOR v_ses IN b_ses_cur
LOOP
dbms_output.put_line('alter system kill session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');
dbms_output.put_line('alter system disconnect session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');
END LOOP;
END;
/
when I add in an "EXECUTE IMMEDIATE" statement, and it's declaration, that's when I get the fault.
SET serveroutput ON;
DECLARE
v_sid NUMBER;
v_serial# NUMBER;
sqlTxt VARCHAR2(1000);
CURSOR b_ses_cur IS
SELECT sid, serial# FROM v$session WHERE module LIKE 'osc%';
sqlTxt := Q'{alter system kill session '''v_ses.sid,v_ses.serial#''' immediate; alter system disconnect session '''v_ses.sid, v_ses.serial#''';}'
BEGIN
spool /tmp/phantom_sessions.lst
dbms_output.put_line('The following sessions will be killed.');
FOR v_ses IN b_ses_cur
LOOP
dbms_output.put_line('alter system kill session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');
dbms_output.put_line('alter system disconnect session '''||v_ses.sid||','||v_ses.serial#||''' immediate;');
EXECUTE IMMEDIATE sqlTxt;
END LOOP;
spool off
END;
/
Again, I'm trying to learn pl/sql....So regardless if there is a better WAY of doing it, I would like to figure this piece out. Feel free to recommend better methodology, but please also help me figure this method out too.
Here is the full error.
ORA-06550: line 9, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table long double ref
char time timestamp interval date binary national character
nchar
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I looked up the syntax for the execute immediate and it's declaration. The "=" does have to be ":="
So I'm confused.
Thanks in advance for any help.