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;
/
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
