Home » RDBMS Server » Server Administration » Error in using dbms_stats
| Error in using dbms_stats [message #172685] |
Wed, 17 May 2006 11:39  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I am trying to gather statistics for the tables of another user. I'm trying to do this with the minimal privs needed, so have created a new user just for this purpose. My problem is that it is working for certain tables, but not for others.
Anyone got a tip as to what is going wrong, or even as to where to look next? Below is some more info, with two tables, one of which works and the other doesn't.
prodcontrol@MOMP> select username, privilege from user_sys_privs;
USERNAME PRIVILEGE
------------------------------ --------------------
PRODCONTROL ANALYZE ANY
PRODCONTROL CREATE VIEW
PRODCONTROL CREATE TABLE
PRODCONTROL CREATE SESSION
PRODCONTROL CREATE PROCEDURE
prodcontrol@MOMP> select count(*) from openffs.mf_adlq_jn;
select count(*) from openffs.mf_adlq_jn
*
ERROR at line 1:
ORA-01031: insufficient privileges
prodcontrol@MOMP> select count(*) from openffs.mf_adse;
select count(*) from openffs.mf_adse
*
ERROR at line 1:
ORA-01031: insufficient privileges
prodcontrol@MOMP> exec dbms_stats.gather_table_stats('openffs','mf_adlq_jn',casc
ade=>true,method_opt=>'for all columns size 254');
PL/SQL procedure successfully completed.
prodcontrol@MOMP> exec dbms_stats.gather_table_stats('openffs','mf_adse',cascade
=>true,method_opt=>'for all columns size 254');
BEGIN dbms_stats.gather_table_stats('openffs','mf_adse',cascade=>true,method_opt
=>'for all columns size 254'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10084
ORA-06512: at line 1
prodcontrol@MOMP> exec dbms_stats.gather_table_stats('openffs','mf_adse',cascade
=>true);
BEGIN dbms_stats.gather_table_stats('openffs','mf_adse',cascade=>true); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10084
ORA-06512: at line 1
prodcontrol@MOMP> exec dbms_stats.gather_table_stats('openffs','mf_adse');
PL/SQL procedure successfully completed.
prodcontrol@MOMP> exec dbms_stats.gather_table_stats('openffs','mf_adse',method_
opt=>'for all columns size 254');
PL/SQL procedure successfully completed.
prodcontrol@MOMP>
sys@MOMP> select table_name, status, tablespace_name from dba_indexes
2 where table_name in ('MF_ADSE','MF_ADLQ_JN');
TABLE_NAME STATUS TABLESPACE_NAME
---------- -------- ------------------------------
MF_ADLQ_JN VALID TRAVEL_IDX
MF_ADLQ_JN VALID TRAVEL_IDX
MF_ADSE VALID DESK_IDX
MF_ADSE VALID DESK_IDX
MF_ADSE VALID DESK_IDX
sys@MOMP> select tablespace_name, status from dba_tablespaces
2 where tablespace_name in ('TRAVEL_IDX','DESK_IDX');
TABLESPACE_NAME STATUS
------------------------------ ---------
DESK_IDX ONLINE
TRAVEL_IDX ONLINE
sys@MOMP> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
|
|
|
|
|
|
| Re: Error in using dbms_stats [message #172703 is a reply to message #172685] |
Wed, 17 May 2006 12:47  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Thanks, will try those as soon as I get off the phone with someone...but....
Any idea as to why in my above output that even though I had select on neither table but yet still one of them worked and the other didn't?
Also, I do have (but did not show) the select_catalog_role. But that is of course different than the select any catalog sys priv. I need to double check, but I think the first one is a standard role which would not be enabled during the plsql procedure, whereas the second is a system priv so it would be in effect.
Hold off on your answer until I post back with testing with the new privs.
Update: When I said select any catalog the sys priv above, I meant select any dictionary, which is probably not recommended.
[Updated on: Wed, 17 May 2006 13:07] Report message to a moderator |
|
|
|
| Re: Error in using dbms_stats [message #172707 is a reply to message #172685] |
Wed, 17 May 2006 13:03 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ok, I did the select any table, and that seemed to do the trick by itself. Did not try, but did not need, the select any catalog.
But...why did I need the select any table? It is bugging me why this is needed...
sys@MOMP> col table_name format a15
sys@MOMP> col grantee format a15
sys@MOMP> select table_name, grantee, privilege from dba_tab_privs
2 where table_name in ('MF_ADSE','MF_ADLQ_JN') order by 1;
TABLE_NAME GRANTEE PRIVILEGE
--------------- --------------- ----------------------------------------
MF_ADLQ_JN AMSDBLOGIN ALTER
MF_ADLQ_JN AMSDBLOGIN DELETE
MF_ADLQ_JN AMSDBLOGIN INSERT
MF_ADLQ_JN AMSDBLOGIN SELECT
MF_ADLQ_JN AMSDBLOGIN UPDATE
MF_ADSE AMSDBLOGIN ALTER
MF_ADSE AMSDBLOGIN DELETE
MF_ADSE AMSDBLOGIN INSERT
MF_ADSE AMSDBLOGIN SELECT
MF_ADSE AMSDBLOGIN UPDATE
10 rows selected.
|
|
|
|
| Re: Error in using dbms_stats [message #172741 is a reply to message #172707] |
Wed, 17 May 2006 18:51 |
krystian.zieja
Messages: 12
Registered: May 2006
Location: Poland
|
Junior Member |
|
|
You do not need to have SELECT ANY TABLE privilege to use dbms_stats.gather* to analyze table in another user schema. You just need ANALYZE ANY system privilege and select on required table.
[email]mob@MOB92.WORLD[/email]> grant analyze any to scott;
[email]mob@MOB92.WORLD[/email]> connect hr/hr
[email]hr@MOB92.WORLD[/email]> grant select on departments to scott;
[email]hr@MOB92.WORLD[/email]> connect scott/tiger
[email]scott@MOB92.WORLD[/email]> exec dbms_stats.gather_table_Stats('hr','departments')
[email]scott@MOB92.WORLD[/email]> conn mob/mob
[email]mob@MOB92.WORLD[/email]> select grantee, owner, table_name, privilege
2 from dba_tab_privs
3 where grantee = 'SCOTT';
GRANTEE OWNER TABLE_NAME PRIVILEGE
------------------------------ ----------- ------------------------------ -----------------
SCOTT HR DEPARTMENTS SELECT
[email]mob@MOB92.WORLD[/email]> select grantee, privilege
2 from dba_sys_privs
3 where grantee = 'SCOTT';
GRANTEE PRIVILEGE
------------------------------ -------------------------
SCOTT ANALYZE ANY
SCOTT UNLIMITED TABLESPACE
Best Regards
Krystian Zieja / mob
[Updated on: Wed, 17 May 2006 18:52] Report message to a moderator |
|
|
|
|
|
| Re: Error in using dbms_stats [message #172900 is a reply to message #172685] |
Thu, 18 May 2006 09:49 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Correct, it definitely appears with the cascade of true as shown. Must be a bug...would make sense if it was. Unfortunately I'm stuck on 9i for the foreseeable future.
Thanks for the responses guys.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 11:34:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
