Home » RDBMS Server » Server Administration » ORA-00942: table or view does not exist (Oracle 11gR2)
ORA-00942: table or view does not exist [message #561762] Wed, 25 July 2012 03:53 Go to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Hi All,

I am facing a strange issue, Please find the below mentioned SQLs

SQL> lock table lqs_cold_recovery in exclusive mode;
Table(s) Locked.

SQL> LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE;
LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE
*
ERROR at line 1:
ORA-00942: table or view does not exist


2 identical queries failed.

Could you please help me out in solving this ?

Thanks in advance,
Veeresh
Re: ORA-00942: table or view does not exist [message #561763 is a reply to message #561762] Wed, 25 July 2012 03:58 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem here (copied/pasted your LOCK TABLE statements):
SQL> create table  lqs_cold_recovery (col number);

Table created.

SQL> lock table lqs_cold_recovery in exclusive mode;

Table(s) Locked.

SQL> LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE;

Table(s) Locked.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>
Re: ORA-00942: table or view does not exist [message #561765 is a reply to message #561763] Wed, 25 July 2012 04:10 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
My Mistake,

Its Oracle 10.2.0.3.0.

Regards,
Veeresh
Re: ORA-00942: table or view does not exist [message #561767 is a reply to message #561765] Wed, 25 July 2012 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change anything.
You simply have not this table or does not have access to it.

Regards
Michel
Re: ORA-00942: table or view does not exist [message #561768 is a reply to message #561767] Wed, 25 July 2012 04:14 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem either (10.2.0.3):
SQL> create table  lqs_cold_recovery (col number);

Table created.

SQL> lock table lqs_cold_recovery in exclusive mode;

Table(s) Locked.

SQL> LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE;

Table(s) Locked.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL>
Re: ORA-00942: table or view does not exist [message #561770 is a reply to message #561767] Wed, 25 July 2012 04:22 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Hi Michel,

I have only created this table.

I Just ran the above commands back to back. One succeeds and one failed.

Thanks,
Veeresh
Re: ORA-00942: table or view does not exist [message #561771 is a reply to message #561770] Wed, 25 July 2012 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you missed something but Oracle is right.
If you want us to diagnose you MUST copy and paste ALL what you do and get.

Regards
Michel
Re: ORA-00942: table or view does not exist [message #561772 is a reply to message #561771] Wed, 25 July 2012 04:38 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Here is the spool file output:

SQL> sho user
USER is "SEMONET"
SQL>
SQL>
SQL> lock table lqs_cold_recovery in exclusive mode;

Table(s) Locked.

SQL> LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE;
LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> spool off

Regards,
Veeresh
Re: ORA-00942: table or view does not exist [message #561774 is a reply to message #561772] Wed, 25 July 2012 04:42 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But, look the asterisk position - under the LOCK. If the table doesn't exist, here's how it looks like:
SQL> lock table does_not_exist in exclusive mode;
lock table does_not_exist in exclusive mode
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

So, how did you do what you did? Really typed those statements or ran a script or ... what?
Re: ORA-00942: table or view does not exist [message #561778 is a reply to message #561774] Wed, 25 July 2012 04:50 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
It is a script, Its failing because of "LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE;"

When the user complained about this, i tried "lock table lqs_cold_recovery in exclusive mode;" It worked.

Similiary i tried "LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE;" and it failed.

For a time being i told them to change the script to "lock table lqs_cold_recovery in exclusive mode;".

I am wondering what is the difference between two.

Thank You for your help Michel and Littlefoot(I dont know the real name).

Regards,
Veeresh
Re: ORA-00942: table or view does not exist [message #561780 is a reply to message #561778] Wed, 25 July 2012 04:55 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you share the script? At least the part that contains these statements?

Because, it appears that LOCK command is trying to lock a table whose name is "LOCK" (and it doesn't exist).
Re: ORA-00942: table or view does not exist [message #561792 is a reply to message #561780] Wed, 25 July 2012 06:05 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Hi ,

After a long search, i found out its because of Bug 9577583.


Workaround:

alter system flush shared_pool;

and, Once i flush shared pool Its working.

SQL> LOCK TABLE lqs_cold_recovery IN EXCLUSIVE MODE;

Table(s) Locked.

Regards,
Veeresh
Re: ORA-00942: table or view does not exist [message #561793 is a reply to message #561792] Wed, 25 July 2012 06:09 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks for the Update.

Sriram Sanka
Re: ORA-00942: table or view does not exist [message #561806 is a reply to message #561793] Wed, 25 July 2012 06:38 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to the Metalink note, does it mean that all (or many?) users have a table with the same (lqs_cold_recovery) name?
Re: ORA-00942: table or view does not exist [message #561870 is a reply to message #561806] Wed, 25 July 2012 23:38 Go to previous message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Yes,

There are 63 tables with the same name across the database.

Regards,
Veeresh
Previous Topic: SGA_TARGET & SGA_MAX_SIZE not equal
Next Topic: can not audit entry in adump
Goto Forum:
  


Current Time: Wed Jul 30 05:24:05 CDT 2014

Total time taken to generate the page: 0.10407 seconds