Home » RDBMS Server » Server Administration » What privileges for GATHER_TABLE_STATS on table in other schema
What privileges for GATHER_TABLE_STATS on table in other schema [message #274547] Tue, 16 October 2007 06:54 Go to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
USER sysadm has ANALYZE ANY privilege,infact user has got dba privliege. but still i am getting THIS ERROR ONLY FOR THIS TABLE IN THAT SCHEMA. what could be the issue here.

SQL> select * from user_role_privs where username='SYSADM' ;

USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SYSADM CONNECT NO YES NO
SYSADM DBA NO YES NO
SYSADM PSADMIN NO YES NO
SYSADM RESOURCE NO YES NO



SQL> connect sysadm/sysadm
Connected.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'sysadm', tabname => 'PS_SJT_PERSON');
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname=>'sysadm', tabname => 'PS_SJT_PERSON'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYSADM"."PS_SJT_PERSON", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13046
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1
Re: What privileges for GATHER_TABLE_STATS on table in other schema [message #274556 is a reply to message #274547] Tue, 16 October 2007 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does the table exist?

Regards
Michel
Re: What privileges for GATHER_TABLE_STATS on table in other schema [message #274560 is a reply to message #274547] Tue, 16 October 2007 07:36 Go to previous messageGo to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
yes.

SELECT COUNT(*) FROM PS_SJT_PERSON;

count(*)
26115
Re: What privileges for GATHER_TABLE_STATS on table in other schema [message #274567 is a reply to message #274560] Tue, 16 October 2007 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
show user
select table_name from user_tables where table_name='PS_SJT_PERSON'
minus
select view_name from user_views where view_name='PS_SJT_PERSON'
/

Regards
Michel

[Updated on: Tue, 16 October 2007 08:17]

Report message to a moderator

Re: What privileges for GATHER_TABLE_STATS on table in other schema [message #274571 is a reply to message #274547] Tue, 16 October 2007 08:21 Go to previous messageGo to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
SQL> show user
USER is "SYSADM"
SQL> select table_name from user_tables where table_name='PS_SJT_PERSON'
2 minus
3 select view_name from user_views where view_name='PS_SJT_PERSON'
4 /

TABLE_NAME
------------------------------
PS_SJT_PERSON

1 row selected.
Re: What privileges for GATHER_TABLE_STATS on table in other schema [message #274719 is a reply to message #274547] Tue, 16 October 2007 23:15 Go to previous messageGo to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
I am not able to solve this issue still. could someone please help me,. i am able to gather the statistics for the same table in SYSADM schema if i login as SYS user but not as SYSADM user.
Re: What privileges for GATHER_TABLE_STATS on table in other schema [message #274725 is a reply to message #274547] Wed, 17 October 2007 00:06 Go to previous messageGo to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
I found the issue,. i refreshed my database few days back somehow my temp file is gone from temp tablespace. I just added one temp file and it works fine now. Thanks for the help michel.
Re: What privileges for GATHER_TABLE_STATS on table in other schema [message #274738 is a reply to message #274725] Wed, 17 October 2007 01:06 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
I add this to the possible reason of 1031 on dbms_stats (a poor designed package, it seems there is an "when others then raise insufficient_privileges;"). Mad

Regards
Michel
Previous Topic: v$session-v$process
Next Topic: ORA-01654
Goto Forum:
  


Current Time: Sat Dec 10 20:30:25 CST 2016

Total time taken to generate the page: 0.08749 seconds