ORA-01031: insufficient privileges

[steeve123]

why cant i use this i a procedure?

look at example...

SQL> EXEC dbms_shared_pool.keep('PYRADIA.GPR_GENERAL','P');

PL/SQL procedure successfully completed.
Works fine!

SQL> BEGIN
2 dbms_shared_pool.keep('PYRADIA.GPR_GENERAL','P');
3 END;
4 /

PL/SQL procedure successfully completed.

Works fine!

SQL> CREATE OR REPLACE PROCEDURE TEST AS
2 BEGIN
3 dbms_shared_pool.keep('PYRADIA.GPR_GENERAL','P');
4 END;
5 /

Procedure created.

SQL> EXECUTE TEST;
BEGIN TEST; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
ORA-06512: at "SYS.TEST", line 3
ORA-06512: at line 1

Dont work...

[stecal]

grant alter session to your_user_name;

has to be directly granted to you, not by a role

[steeve123]

sorry, forgot to mention, im connected sys as sysdba

[SANJAY_G]

Works fine for me (9.2.0.3)

SQL*Plus: Release 9.2.0.3.0 - Production on Tue Jun 10 08:35:08 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> connect sys as sysdba
Enter password: *********
Connected.
SQL> create or replace procedure p1
  2  is
  3  begin
  4  null;
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure test
  2  is
  3  begin
  4  dbms_shared_pool.keep('P1','P');
  5  end;
  6  /

Procedure created.

SQL> execute test;

PL/SQL procedure successfully completed.

SQL> show error
No errors.
SQL>

[steeve123]

Does anyone know why it does this?
If it's a procedure owned by sys, it works fine...
SQL> CREATE OR REPLACE PROCEDURE TEST AS
2 begin
3 dbms_shared_pool.keep('sys.test','P');
4 end;
5 /

Procedure created.

SYS @ orcl:
SQL> execute test;

PL/SQL procedure successfully completed.


But if its a procedure owned by onother user, it does not work...


SYS @ orcl:
SQL> CREATE OR REPLACE PROCEDURE TEST AS
2 begin
3 dbms_shared_pool.keep('demo40.proc_steeve','P');
4 end;
5 /

Procedure created.

SYS @ orcl:
SQL> execute test;
BEGIN test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
ORA-06512: at "SYS.TEST", line 3
ORA-06512: at line 1


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

[marist89]

Originally posted by stecal

has to be directly granted to you, not by a role

[steeve123]

Isn't Sys the master of all master?
Why grant to sys?
He's the man.

[steeve123]

It worked, i grant the procedure to sys.
But im writing a procedure that will ping lots of package, procedure and function in the shared_pool. I dont want to grant every object i want to ping to sys. Sys supose to be the king right?

[TomazZ]

grant execute any procedure to sys

[steeve123]

forgot about grant execute any, thanks!
Sys supose to have that right? Or you have to grant it manualy when you need it?