 |
|
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
View - The user view
will return those items that you own. Your tables, indexes, sequences
etc.
The
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
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.
SQL> SELECT
2 table_name
3 FROM
4 user_tables;
TABLE_NAME
------------------------------
AUTHOR
EMP
JOB
PUBLISHER
SALES
STORE
BOOK_AUTHOR
BOOK
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
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
