ORA-01031: insufficient privileges
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: ORA-01031: insufficient privileges

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    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...

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    grant alter session to your_user_name;

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

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    sorry, forgot to mention, im connected sys as sysdba

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    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

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by stecal

    has to be directly granted to you, not by a role
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2000
    Posts
    440
    Isn't Sys the master of all master?
    Why grant to sys?
    He's the man.

  8. #8
    Join Date
    Nov 2000
    Posts
    440
    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 10:38 AM.

  9. #9
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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

  10. #10
    Join Date
    Nov 2000
    Posts
    440
    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
  •  


Click Here to Expand Forum to Full Width