Home » SQL & PL/SQL » SQL & PL/SQL » select/describe not working on table present in all_tables from sqlplus
select/describe not working on table present in all_tables from sqlplus [message #187516] Mon, 14 August 2006 05:39 Go to next message
yadav00
Messages: 1
Registered: August 2006
Junior Member
I have Oracle 10g on Linux. I am using sqlplus on same m/c

I can see a table in it using following query, but select/describe isnt working on it. Do I have to prefix something before the table while using select statement ?


SQL> select TABLE_NAME from all_tables where TABLE_NAME like '%DATA%';

TABLE_NAME
------------------------------
MYDATA

SQL> describe MYDATA;
ERROR:
ORA-04043: object MYDATA does not exist


SQL>



-TIA
Re: select/describe not working on table present in all_tables from sqlplus [message #187519 is a reply to message #187516] Mon, 14 August 2006 05:46 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This table isn't in your schema; in order to see it, use owner's name, such as

SQL> DESC owner_of_the_table.mydata;
Re: select/describe not working on table present in all_tables from sqlplus [message #187525 is a reply to message #187516] Mon, 14 August 2006 06:20 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Hai Yadav..

when you use
all_tables view, the tables returned includes tables belonging to other schemas.

select table_name,owner from all_tables


will show you what i mean just see the owner column..

In fact you shud be using user_tables view to get list of all tables you have access to

Re: select/describe not working on table present in all_tables from sqlplus [message #187539 is a reply to message #187525] Mon, 14 August 2006 06:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

In fact you shud be using user_tables view to get list of all tables you have access to

Not true. all_tables lists tables you have access to, user_tables list tables in your own schema.
Previous Topic: Estimating cache size
Next Topic: Quarter first and last month
Goto Forum:
  


Current Time: Sat Dec 10 05:02:28 CST 2016

Total time taken to generate the page: 0.16521 seconds