SQL> SELECT   *
  2    FROM   user_sys_privs
  3   WHERE   privilege = 'ALTER SESSION'
  4  ;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
testdbauser                      ALTER SESSION                            NO

SQL> 
SQL> SELECT   u.owner,
  2           u.table_name,
  3           u.grantor,
  4           u.privilege,
  5           u.grantable,
  6           u.hierarchy
  7    FROM   sys.user_tab_privs_recd u
  8   WHERE   table_name = 'V_$SESSION';

OWNER  TABLE_NAME   GRANTOR  PRIVILEGE  GRANTABLE     HIERARCHY
------ ------------  -------- ---------- ------------- ----------
SYS    V_$SESSION   SYS      SELECT     YES       NO


SQL>



grant execute on kill_session to testuser;

conn to testuser.

SQL> exec testdbauser.kill_session(1016,808);
BEGIN testdbauser.kill_session(1016,808); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204
ORA-06512: at "SYS.DBMS_SQL", line 323
ORA-06512: at "FXBLOTTER.KILL_SESSION", line 9
ORA-06512: at line 1
 

SQL> show user
USER is "SYS"

SQL> CREATE OR REPLACE PROCEDURE system.dev_flush_cache AS
  2      l_ddl VARCHAR2(100);
  3  BEGIN
  4      l_ddl := 'ALTER SYSTEM FLUSH SHARED_POOL';
  5      dbms_output.put_line(l_ddl);
  6      EXECUTE IMMEDIATE l_ddl;
  7      l_ddl := 'ALTER SYSTEM FLUSH BUFFER_CACHE';
  8      dbms_output.put_line(l_ddl);
  9      EXECUTE IMMEDIATE l_ddl;
 10  END dev_flush_cache;
 11  /

Procedure created.

SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> grant execute on system.dev_flush_cache to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> exec system.dev_flush_cache;
BEGIN system.dev_flush_cache; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.DEV_FLUSH_CACHE", line 6
ORA-06512: at line 1

HI Tom,

Thanks for your valuable informations.

can you please clarify below issue.

Database : 11gr2. 4 node RAC
Server : Exadata half rack.
O/s : Linux

i am writing a package for the developers. through which they can view all of their sessions using one function and they can kill their session using another procedure.

kill session procedure looks working fine. No issues.

but view sessions function is not showing the results properly.
suppose , i have only one session for the user " AELYAS " From  
instance 01. no sessions from other other nodes. when executing this function , it shows one session additionaly from each nodes.

following is the code for viewing sessions

function List_My_Session return Kill_Table PIPELINED is 
begin
for c1 in ( select inst_id,sid,serial#,username,osuser,sql_id from gv_$session 
where username in sys_context('USERENV', 'SESSION_USER'))
loop PIPE ROW (Kill_Format(c1.inst_id, c1.sid,c1.serial#,c1.username,c1.osuser,c1.sql_id ));
end loop;
return;
end;

create or replace type Kill_Format
as object(f int,
          a int,
          b int,
          c varchar2(20),
          d varchar2(20),
          e varchar2(100))
/


create or replace type Kill_Table
as table of Kill_Format
/

then i tested manually as follows:

first as sys user

SYSSQL> select count(*),inst_id from gv$session where username='AELYAS' group by inst_id;

  COUNT(*)    INST_ID
---------- ----------
  1     1

SYSSQL> 

it is fine.

now as normal user : granted select on gv_$session to this user


AELYASSQL> select count(*),inst_id from gv$session where username='AELYAS' group by inst_id;

  COUNT(*)    INST_ID
---------- ----------
  2     1
  1     2
  1     3
  1     4

AELYASSQL> 

here we can see one addition session from each node apart from the actual one session from node1

i really confused what went wrong here.  tried google as well. i could not find any solution

can you please help me here

Thanks
Aliyar