I'm using this little script:
v_flush varchar2(3) := '&1';
if substr(upper(v_flush),1,2) = 'BP' then
dbms_output.put_line ('*** Buffer Pool Flushed ***') ;
if substr(upper(v_flush),1,2) = 'SP' then
dbms_output.put_line ('*** Shared pool flushed ***') ;
when others then
err_mesg := SQLERRM;
dbms_output.put_line ('****** Error! '||err_mesg) ;
And it's not working, as in, it's not flushing the pools. I get no errors. For the life of me I can't figure it out. I put in several DBMS_OUTPUT.PUT_LINE commands to print out what is going on as it runs. Eventually I notice that the variable v_flush is set to literally &1 while this thing runs. WHAT?
It turned out that DEFINE had some how gotten turned OFF in the session. How I'm not exactly sure, but now this script file has:
set define on
at the top of it.