| error while executing DBMS_STATS.GATHER_SCHEMA_STATS [message #163628] |
Fri, 17 March 2006 09:41  |
gpraju
Messages: 3
Registered: December 2005
Location: Hyderabad, India
|
Junior Member |
|
|
Hi,
I have Oracle 10g 10.2.0.1 installed on Linux
I created one user and imported data.
While trying to execute DBMS_STATS.GATHER_SCHEMA_STATS
I am getting the following error:
BEGIN dbms_stats.gather_schema_stats(null); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1
Here the wonder is, I am getting the same error by logging as both the owner and SYS also.
Note: For other users this procedure is working fine.
Will be thankful any body gives an idea.
Advance thanks
GPRaju
|
|
|
|
| Re: error while executing DBMS_STATS.GATHER_SCHEMA_STATS [message #163896 is a reply to message #163628] |
Mon, 20 March 2006 10:38  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I don't think (but have not confirmed) that users, by default, have execute permission on dbms_stats. I have a login.sql script that executes a few such procedures. When I create a new user and login, they bomb. When I connect as my normal dba user, they work just fine, presumably since dba role has most everything, including execute any procedure. You can confirm by checking user_role_privs and user_tab_privs and the like.
SYS AS SYSDBA> create user a identified by a;
User created.
SYS AS SYSDBA> grant connect to a;
Grant succeeded.
SYS AS SYSDBA> connect a/a
Connected.
BEGIN dbms_stats.set_param('method_opt', 'for all columns size 254'); END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 19075
ORA-06512: at line 1
BEGIN dbms_stats.set_param('estimate_percent','null'); END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 19075
ORA-06512: at line 1
BEGIN dbms_stats.set_param('granularity', 'all'); END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 19075
ORA-06512: at line 1
BEGIN dbms_stats.set_param('cascade', 'true'); END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 19075
ORA-06512: at line 1
A > connect mydba/orcl;
Connected.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
MYDBA >
|
|
|
|
|
|
| Re: error while executing DBMS_STATS.GATHER_SCHEMA_STATS [message #164014 is a reply to message #163992] |
Tue, 21 March 2006 05:35 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
I remember having faced a similar (though may not be exactly same) situation a few months ago..
dbms_stats was giving errors and in my case, what we found was that it was an oracle bug..but before I go into those details, what I would suggest is to try to calculate statistics of each table in the schema..by using dbms_stats.gather_table_stats...if you hit the same bug that I did, then for one of the tables , the procedure will fail...
may be u can try a procedure like;
declare
cursos c is select object_name from user_objects where object_type='TABLE';
begin
for cc in c loop
dbms_stats.gather_table_stats(ownname=>USER,tabname=>cc.table_name,cascade=>true);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
(I have not tested this code..so it is more of psuedocode..)
|
|
|
|
| Re: error while executing DBMS_STATS.GATHER_SCHEMA_STATS [message #164386 is a reply to message #164014] |
Thu, 23 March 2006 04:24 |
gpraju
Messages: 3
Registered: December 2005
Location: Hyderabad, India
|
Junior Member |
|
|
hi,
Thanks fro your reply.
Very bad is, eventhough while trying this also ( to gather stats for each table individually), I am getting the same error,
Insufficient privileges.
Very Strange!!!!!!!!
The schema owner has no privileges on his own objects.
Any more ideas. What can i do ????
Advance Thanks,
GPRaju
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
