Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01031: insufficient privileges (ORacle 10g,Win XP)
ORA-01031: insufficient privileges [message #383122] Tue, 27 January 2009 05:55 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I am getting this error ORA-01031: insufficient privileges when trying to run analyze table table_name compute statistics in a procedure

thanks and regards
Re: ORA-01031: insufficient privileges [message #383126 is a reply to message #383122] Tue, 27 January 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have not the privilege to do it.

Regards
Michel
Re: ORA-01031: insufficient privileges [message #383129 is a reply to message #383126] Tue, 27 January 2009 06:08 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


Thanks Sir for your reply ,but Sir I have DBA role
Re: ORA-01031: insufficient privileges [message #383131 is a reply to message #383129] Tue, 27 January 2009 06:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Which, as it is a role, rather than an explicitly granted privilege, does not apply inside a procedure.
Re: ORA-01031: insufficient privileges [message #383132 is a reply to message #383131] Tue, 27 January 2009 06:15 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Sir .Then how can i attain privilege from a procedure .
Re: ORA-01031: insufficient privileges [message #383133 is a reply to message #383132] Tue, 27 January 2009 06:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Check this link.

Regards

Raj
Re: ORA-01031: insufficient privileges [message #383137 is a reply to message #383133] Tue, 27 January 2009 06:39 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Its not helping me
Re: ORA-01031: insufficient privileges [message #383140 is a reply to message #383137] Tue, 27 January 2009 06:47 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Quote:
You must have the CREATE PROCEDURE system privilege to create a
procedure or package in your schema, or the CREATE ANY
PROCEDURE system privilege to create a procedure or package in
another user’s schema.


yours
dr.s.raghunathan
Re: ORA-01031: insufficient privileges [message #383141 is a reply to message #383137] Tue, 27 January 2009 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rakeshramm wrote on Tue, 27 January 2009 13:39
Its not helping me

Did you read the prerequisite for ANALYZE TABLE or DBMS_STATS depending on what you use and didn't think it is important to tell us.

Regards
Michel
Re: ORA-01031: insufficient privileges [message #383217 is a reply to message #383137] Tue, 27 January 2009 16:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you, actually, do that? Which code did you use? Because, even if you don't do anything, it works ...

I'll create a "dummy" user which will do the test; privileges aren't anything special and, besides, were granted via predefined CONNECT and RESOURCE roles:
SQL> select privilege from role_sys_privs where role in ('CONNECT', 'RESOURCE');

PRIVILEGE
----------------------------------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE SESSION
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

9 rows selected.

SQL> create user brisime identified by sime;

User created.

SQL> grant connect, resource to brisime;

Grant succeeded.

Now let's create user, let him create a table and analyze it in SQL and PL/SQL:
SQL> connect brisime/sime
Connected.

Session altered.

SQL> create table test (id number);

Table created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> begin
  2    execute immediate ('analyze table test compute statistics');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>

So far so good.

Now, perhaps I didn't get the point, but - what "insufficient privileges" are we talking about?
Re: ORA-01031: insufficient privileges [message #383313 is a reply to message #383217] Wed, 28 January 2009 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think the OP must be trying to gather stats on a table in someone elses schema.

Or they're really realy confused.
Re: ORA-01031: insufficient privileges [message #389973 is a reply to message #383313] Wed, 04 March 2009 06:53 Go to previous message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


It is solved by using keyword
AUTHID CURRENT_USER



Thnaks for support
Previous Topic: Column to Row
Next Topic: NLS Language Problem
Goto Forum:
  


Current Time: Thu Dec 08 14:42:45 CST 2016

Total time taken to generate the page: 0.06357 seconds