Home » RDBMS Server » Server Administration » DBMS_STATS and ORA-20000 (oracle 64 bit server 11.2.0.1.0, windows 7)
DBMS_STATS and ORA-20000 [message #536417] Tue, 20 December 2011 21:24 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I've just installed an Oracle 11.2.0.1.0 64 bit server on my windows 7 machine in order to play around with some probably futile thoughts in regards to the eternity ii puzzle.

Anyway, while using attempting to run

exec dbms_stats.gather_table_stats('eii','v2x4e')


I get the following:

ORA-20000: Unable to analyze TABLE "EII"."V2X4E", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 20327
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1


My initial google searches indicate that I need the select any table and analyze any privileges. I don't think that can be right/appropriate - but I've granted them anyway to no avail.

Additionally any thoughts of why

Select * from user_tables


returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?

Thanks!

[Updated on: Tue, 20 December 2011 21:26]

Report message to a moderator

Re: DBMS_STATS and ORA-20000 [message #536421 is a reply to message #536417] Tue, 20 December 2011 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?
You must have been logged into the DB as SYSTEM.

http://docs.oracle.com/cd/E11882_01/server.112/e25789/datadict.htm#BHAHEADF

USER_TABLES - shows tables owned by current USER
ALL_TABLES - shows all tables on which you have privileges
DBA-TABLE - shows all tables in the DB

Rather than poking blindly at the DB; take a few hours to Read The Fine Manual below; Concepts Manual

http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm

[Updated on: Tue, 20 December 2011 22:33]

Report message to a moderator

Re: DBMS_STATS and ORA-20000 [message #536442 is a reply to message #536421] Wed, 21 December 2011 00:54 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
BlackSwan wrote on Tue, 20 December 2011 21:19
>returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?
You must have been logged into the DB as SYSTEM.


No I've set up my own schema and tablespace, and quite clearly connected as eii/password@orcl.

But on second pass that seems to be an issue with toad. Connecting via sqlplus does not give the same result.

And on third glace that's the same reason dbms_Stats isn't working. Hmm something is strange with toad then. Nevermind. Same problem different forum Razz.

[Updated on: Wed, 21 December 2011 00:55]

Report message to a moderator

Re: DBMS_STATS and ORA-20000 [message #536462 is a reply to message #536442] Wed, 21 December 2011 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once more here a proof why you MUST through away TOAD.
You can't trust it.
You can NEVER know what it really does.
You can't know and trust what it returns.

Regards
Michel
Re: DBMS_STATS and ORA-20000 [message #536499 is a reply to message #536462] Wed, 21 December 2011 03:39 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Do you have another tool you'd recommend; or just a text editor and sqlplus purist?
Re: DBMS_STATS and ORA-20000 [message #536503 is a reply to message #536499] Wed, 21 December 2011 03:54 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*Plus is the only one you can trust.

Regards
Michel
Previous Topic: eRROR ora-12560
Next Topic: pga allocation done on what basis ??
Goto Forum:
  


Current Time: Thu Mar 28 09:25:39 CDT 2024