Oracle kill sessions procedure

Question

I want to create procedure which will kill all session. After I run the statement i get error:

[Warning] ORA-24344: success with compilation error 10/13 PL/SQL: ORA-00942: table or view does not exist 6/6 PL/SQL: SQL Statement ignored 15/31 PLS-00364: loop index variable 'V_KILL' use is invalid 15/9 PL/SQL: Statement ignored (1: 0): Warning: compiled but with compilation errors

 CREATE OR REPLACE PROCEDURE KILL_ORACLE_SESSIONS
    IS

    BEGIN
        FOR v_kill IN
        (SELECT
                'alter system kill session '''
                ||sid||','||serial#||',@1'|| ''' immediate;' as statement
                        FROM
                v$session
                    WHERE
                sql_id='sql_id_here'
        )
        LOOP
            dbms_output.put_line (v_kill.statement);
        END LOOP;
    END;
    /

Where is the catch ? Thanks

Just that I couldn't do GRANT SELECT ON V$SESSION TO {username}; instead I do grant select on V_$SQL to {username}; — civesuas_sine

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2018-05-24T10:57:06

Most likely you don't have permissions to select view v$session because your user received this privilege by a ROLE. Privileges inside a PL/SQL block must be granted directly to the user (i.e. GRANT SELECT ON V$SESSION TO {username};). A role (for example DBA ROLE) does not apply inside PL/SQL.

Oracle kill sessions procedure

1 Answers