How to see all tables and Views in Oracle 10g and upwards?

When I put "show views" or "show tables", it gives me error like ORA-00900. How can i see the all tables, views and indexes associated by Schema?

What should be the exact SQL command?
aburaihanxpAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
use the views provided by Bawer in http:#30507684

Once you get the concept behind the naming they are straight forward:

USER_ - What you own.
ALL_ - What you have access to
DBA_ - everything

So if you are looking for tables you own select from: USER_TABLES.
Views you own: USER_VIEWS
Indexes you own:  USER_INDEXES

Any VIEW you have access to:  ALL_VIEWS
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SHOW VIEWS is MySQL, not oracle ...
all tables:

http://ss64.com/orad/DBA_ALL_TABLES.html
select * from dba_all_tables

views: http://ss64.com/orad/DBA_VIEWS.html
indexes:
http://ss64.com/orad/DBA_INDEXES.html
0
 
BawerCommented:
select ..... from user_tables

select ...... from user_views

select...... from user_indexes

if you want to know about the colomns then use

desc user_tables

desc  user_views

desc user_indexes
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
AbarajCommented:
select * from tab where tabtype='TABLE';
select * from tab where tabtype='VIEW';
0
 
aburaihanxpAuthor Commented:
Actually i am using oracle sql developer. when i am writing the sql, then, the list of all available view is coming like the attachment. But, when i am putting the "select * from tab where tabtype='VIEW';" query, i am getting nothing as output.

Can you please let me know what could be the possible reason?
Sample.jpg
0
 
AbarajCommented:
try to use your ORACLE SQL *Plus. you will get the results.
0
 
aburaihanxpAuthor Commented:
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Apr 12 15:30:54 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from tab where tabtype='VIEW';

no rows selected

SQL>
0
 
DavidSenior Oracle Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.