OraFAQ Forum: Server Administration » What privileges for GATHER_TABLE

Home » RDBMS Server » Server Administration » What privileges for GATHER_TABLE_STATS on table in other schema

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

What privileges for GATHER_TABLE_STATS on table in other schema [message #274547]

Tue, 16 October 2007 06:54

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Does the table exist?

Regards
Michel

Report message to a moderator

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

lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India

Junior Member

yes.

SELECT COUNT(*) FROM PS_SJT_PERSON;

count(*)
26115

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	v$session-v$process
Next Topic:	ORA-01654

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 07:52:17 CST 2024