-
ORA-01031: insufficient privileges
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...
-
grant alter session to your_user_name;
has to be directly granted to you, not by a role
-
sorry, forgot to mention, im connected sys as sysdba
-
Works fine for me (9.2.0.3)
Code:
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>
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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
-
Originally posted by stecal
has to be directly granted to you, not by a role
Jeff Hunter
-
Isn't Sys the master of all master?
Why grant to sys?
He's the man.
-
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?
Last edited by steeve123; 06-10-2003 at 09:38 AM.
-
grant execute any procedure to sys
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
forgot about grant execute any, thanks!
Sys supose to have that right? Or you have to grant it manualy when you need it?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|