Home » SQL & PL/SQL » SQL & PL/SQL » ORA-20000: Object ORDER does not exist or insufficient (Oracle9i)
ORA-20000: Object ORDER does not exist or insufficient [message #356961] Mon, 03 November 2008 06:16 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
We have moved 1 months data from Production to a new schema in Development. The new schema is say XXX1.Do I need to gather statistics on those tables/indexes?
I am finding there is a big difference in the num of rows and NUM_ROWS


Now when I wanted to gather statistics before making analysis(running show plan) on the new scema using my userid I got the below
error message.

ERROR at line 1:
ORA-20000: Object ORDER does not exist or insufficient privileges to analyze
ORA-06512: at "SYS.DBMS_STATS", line 9643
ORA-06512: at "SYS.DBMS_STATS", line 9657
ORA-06512: at line 1


Need your suggesion..

Regards,
Oli
Re: ORA-20000: Object ORDER does not exist or insufficient [message #356964 is a reply to message #356961] Mon, 03 November 2008 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, you'll need to gather stats.

Show us the code you used to gather stats with.
Re: ORA-20000: Object ORDER does not exist or insufficient [message #356966 is a reply to message #356964] Mon, 03 November 2008 06:28 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the reply


Here is what I did...

Logged in as User : YYY1 (say)


EXEC dbms_stats.gather_table_stats(ownname=>'XXX1',tabname=>'ORDER',cascade=>true); 



Grants on ORDER table is:
Priviledge              Gratee     Grantable     Grantor
INS/UPD/DEL/SELECT      YYY1          NO         XXX1


Regards,
Oli


[Updated on: Mon, 03 November 2008 06:51]

Report message to a moderator

Re: ORA-20000: Object ORDER does not exist or insufficient [message #356986 is a reply to message #356966] Mon, 03 November 2008 07:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
By having grants on an object doesn't make that schema as the owner of the object.

Regards

Raj
Re: ORA-20000: Object ORDER does not exist or insufficient [message #356988 is a reply to message #356986] Mon, 03 November 2008 07:29 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Raj for the reply.

So, what step need to folllow to excecute the below statement from another schema?


Regards,
Oli
Re: ORA-20000: Object ORDER does not exist or insufficient [message #356992 is a reply to message #356988] Mon, 03 November 2008 07:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try changing the owner to the actual owner and see what happens.

Regards

Raj
Re: ORA-20000: Object ORDER does not exist or insufficient [message #356994 is a reply to message #356992] Mon, 03 November 2008 07:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am using schema YYY1 and collecting statistics for a table which is in another schema(xxx1).


How would I change the owner to the actual owner. It would of be great if u let me know about how to access this new schema xxx1 directly.


Regards,
Oli
Re: ORA-20000: Object ORDER does not exist or insufficient [message #356996 is a reply to message #356994] Mon, 03 November 2008 08:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
SQL> select * from v$reserved_words where KEYWORD='ORDER';

KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
ORDER                                   5 Y N N N N

SQL> create table order (dummy number);
create table order (dummy number)
             *
ERROR at line 1:
ORA-00903: invalid table name
Re: ORA-20000: Object ORDER does not exist or insufficient [message #356997 is a reply to message #356996] Mon, 03 November 2008 08:09 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the reply.

The same happens for the tables when I try to execute from different schema for analyzing stats.


Tried executing the table using following syntax in sqlplus


SELECT * FROM XXX1.USR_STATUS


It works fine when I executed from schema YYY1(say).
But not being able to analyze the table.

Does DBA need to grant Execute priviledge to user YYY1 explicitely?


[Updated on: Mon, 03 November 2008 08:16]

Report message to a moderator

Re: ORA-20000: Object ORDER does not exist or insufficient [message #357010 is a reply to message #356997] Mon, 03 November 2008 09:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Try changing the owner to the actual owner and see what happens.

I didn't ask you to change the actual owner. I asked you to change the value of the owner to the actual owner while calling dbms_stats.

Anyways, Check this link. Hope this will help you out.

http://www.orafaq.com/forum/t/63428/2/

Regards

Raj

[Updated on: Mon, 03 November 2008 09:17]

Report message to a moderator

Re: ORA-20000: Object ORDER does not exist or insufficient [message #357014 is a reply to message #357010] Mon, 03 November 2008 09:36 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thank you very much. I got it.


Regards,
Oli
Previous Topic: ora-01839 : date not valid for month specified
Next Topic: Need trigger help unable to proceed
Goto Forum:
  


Current Time: Sat Dec 03 06:11:04 CST 2016

Total time taken to generate the page: 0.10020 seconds