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?
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SHOW VIEWS is MySQL, not oracle ...
all tables:
select * from dba_all_tables

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
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.

select * from tab where tabtype='TABLE';
select * from tab where tabtype='VIEW';
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?
try to use your ORACLE SQL *Plus. you will get the results.
aburaihanxpAuthor Commented:
SQL*Plus: Release - 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 - 64bit Production
With the Partitioning, OLAP and Data Mining options

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

no rows selected

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.
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.