Basic procedures to give db level grants to users

Some times we need to grant a particular db user to kill sessions and for that we need to  grant alter system access which is not a best practice. So to over come we create a simple procedure to do the required actions and grant execute permissions to the user on that procedure.

Here are few examples..

Killing session procedure

CREATE OR REPLACE procedure kill_db_session
 ( v_sid number, v_serial number )  as
 v_varchar2 varchar2(100);
begin
execute immediate 'ALTER SYSTEM KILL SESSION '''   || v_sid || ',' || v_serial || ''' IMMEDIATE';
end;
/

Refresh materialized view under a different user

CREATE OR REPLACE PROCEDURE SSS.REFRESH_MV AS

BEGIN

    DBMS_MVIEW.REFRESH('xxx','C');

END REFRESH_MV;

/

Here xxx is tablename and C means complete refresh

Make sure you give grants on tables explicitly for mviews or else they will throw table not found error

Kill sessions

Finding spid for killing process
SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID = <sid_number>;


Finding locks
get sid here
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;


Alternatively try this

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

**********************************************************************************************************************************

find serial here using sid
select * from v$session where sid=6;

Kill session here
alter system kill session '6, 10405';

check the status to see if the session is killed.
select * from v$session where sid=6;

If it says kill pending then
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

**********************************************************************************************************************************

Changes in 11g require instance number to kill sessions on rac

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620

SQL>  alter system kill session '130,620,1';
 alter system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

Now, it works:

SQL>  alter system kill session '130,620,@1';

System altered.