Kill Session question

Hello,

I want to write a stored procedure that - if tested - should run by dbms_job every 5 minutes to detect and eventually kill blocking sessions automatically.

Now I am standing closed to the goal, and this is the part still making trouble:

PROCEDURE blocker_killer

.......

      IF [conditions to kill session , = INACTIVE and BLOCK > 0 and idle since 3 minutes ]

      and cS_USERNAME != 'VITAL_OPERATOR' -- being excluded from automatic blocker killing

      THEN
         --set target ...
         cKiller := 'ALTER SYSTEM KILL SESSION '
            ||CHR(39)||nS_sid||','||nS_serial#||CHR(39)
            ||' IMMEDIATE';
            --protocol
         insert into mastermind.jg_test (text1) values (cKiller);
         commit;

         -- ... shoot.
         EXECUTE IMMEDIATE cKiller;

         cJg_Action := 'killed';

      ELSE --observe only

         cJg_Action := 'observed';

      END IF;

      write protocol to table ...

      ...

END blocker_killer ;

It compiles with no problems.

The SP is owned and executed by MASTERMIND user only, who has DBA-Default-Role,
that afaik includes ALTER SESSION.

If I run it by commenting EXECUTE ... out for testing, it writes protocol as it should, pretending the session to be killed. I am testing by blocking myself with two sessions, and the cursor to find blocking sessions is yet restricted to kill one of my sessions only (TERMINAL in v$session).

But when I activate EXECUTE ... cKiller, I see

ORA-01031: UNSUFFICIENT PRIVILIGES
ORA-06512: in "MASTERMIND.BLOCKER_KILLER", Zeile 156 (where EXECUTE .. is on)
ORA-06512: in Zeile 1

Then I connected as SYS and re-granted ALTER SESSION to MASTERMIND - nothing doing.
(Did not make sense anyway, but I had the same effect at selecting from v$* in a SP ...)
Strange.

But when I am MASTERMIND in SQL*Plus, I *can* kill sessions just as I expect:

alter system kill session '123,45768';

But by using a SP, executing from SQL*Plus

@kill

, I cannot ?!

Hmmmmmmmmmmmmmmm ....

How come ? AFAIK, DBA-Role is the most powerful role of all in a schema. What am I missing or doing wrong ?

TIA Jan Received on Fri Jan 24 2003 - 05:55:02 CST