Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

The Data Dictionary - Finding Data in the Database

Since we have the pubs.ppt slide, it is easy to see what each table in our schema consists of.  If we didn’t have the slide, we could describe the tables to find out what makes up the tables.  But what if we didn’t know which tables are in the PUBS schema?  Oracle provides views that allow us to query this information from the database. 

In Oracle, a view is a pseudo-table that is created when a query is run against it.  In other words, there is no table called user_tables .  When I query my table_names from user_tables, Oracle temporarily creates the table to answer the query.  There are three levels of views:

The USER View - The user view will return those items that you own.  Your tables, indexes, sequences etc.

The ALL View – The all view will return those objects that you own and those objects that you have been granted rights on.  If another schema has granted select on one of their tables to you (the PUBS user), then it will appear in the all views.  It will not appear in the user view.

The DBA View – The dba view returns all objects in the database.

If an object exists but is not returned in the view you use, the database returns an “object does not exists” error.  This is a security feature, because if you are not granted access to it, you are not allowed to know it exists.

To get a list of the PUBS tables, we query the user_tables  view.

SQL> desc user_tables;

Name                              Null?     Type
--------------------------------- --------  ------------TABLE_NAME                        NOT NULL  VARCHAR2(30)
TABLESPACE_NAME                             VARCHAR2(30)
CLUSTER_NAME                                VARCHAR2(30)
IOT_NAME                                    VARCHAR2(30)
 .  .  .

We are looking for the table names. 

  2    table_name
  3  FROM
  4    user_tables;


To find all the tables we have access to, use the all view.  The difference between the user_tables  view and the all_tables/dba_tables view is the addition of the owner column.  Since the user view only shows us our own tables, there is no need for the owner column.  Now, select the table_name from the all_tables view.  Wow!  I got 1515 tables (you may get a different number depending on the features you installed in the database).  If I query the table names from the dba_tables view, I get the same number.  This is because we granted the user PUBS the DBA role.  Since a DBA has access to all objects in the database, the all_tables and dba_tables are the same.

There are many user/all/dba views, and we will introduce more of them as we progress.  Almost all objects have a view: user_indexes, user_sequences, etc.

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.