Oracle Concepts - Data Dictionary
Oracle Tips by Burleson Consulting
Oracle Data Dictionary Concepts
The data dictionary is full of “Metadata”, information
about what is going-on inside your database. The data dictionary is presented to
us in the form of a number of views. The dictionary views come in two primary
The DBA, ALL or USER views
- These views are used to manage database structures.
The V$ Dynamic Performance Views
- These views are used to monitor real time database
Throughout the rest of this book we will introduce you
to data dictionary views that you can use to manage your database. You will find
the entire list of Oracle Data Dictionary views documented in the Oracle
There are hundreds of views in the data dictionary. To
see the depth of the data dictionary views, here are the views that store data
about Oracle tables:
Later in this chapter we will see simple data dictionary
scripts to see information about the internal structure of our datafiles,
tablespaces, tables and indexes. To learn more about the data dictionary, get
the free Oracle 10g data dictionary reference by Rampant (Just Google “bc free
10g poster” to order your copy). For a collection of pre-written Oracle data
dictionary scripts, see
Tip: You can also query the DICT or DICTIONARY view to
see a list of all views and comments about them that exist in the data
dictionary. This view is a quick way to find exactly what you’re looking for in
the data dictionary.
Inside the Oracle Data Dictionary
If you are like me, you are a bit forgetful. The data
dictionary is a repository of information about the Oracle database, known as
metadata. Metadata is “information about information,” and the data dictionary
is information about the database. In this section we want to show you how to
use the data dictionary to get information on tables.
Oracle provides several data dictionary views that you
can use to collect information on views in the database. These views include:
* dba_tables, all_tables, user_tables
* dba_tab_columns, all_tab_columns and user_tab_columns
So, we forgot where the BOOKS table is located. From the
SYSTEM account, we can query the dba_tables view to find our table:
Other views that show you where your tables are include
user_tables and all_tables.
Oracle also provides views that allow you to view the
attributes of table columns. The dba_tab_columns view (and all_tab_columns and
user_tab_columns) gives you a variety of information on table columns.
Oracle Data Dictionary
Managing Oracle requires the use of a number of Oracle
supplied views. These views include the data dictionary and the dynamic
performance views. Together these views allow you to:
* Manage the database
* Tune the database
* Monitor the database
In this chapter we will fist look at the data dictionary
views. We will then look at the dynamic performance views available in Oracle
At the heart of every Oracle database is the data
dictionary. The data dictionary is generated when the database is first created.
In this section we will discuss the Oracle data dictionary. In it we will
* The purpose of the data dictionary
* The architecture of the data dictionary
* Uses of the data dictionary
The Purpose of the Data Dictionary
Metadata is data about data, or data that defines other
data. The Oracle data dictionary is metadata about the database. For example, if
you create a table in Oracle, metadata about that table is stored in the data
dictionary. Such things as column names, length, and other attributes are
stored. Thus, the data dictionary contains a great volume of useful information
about your database. Pretty much everything you would want to know about your
database is contained in the data dictionary in some form.
As a DBA then, you can see why the data dictionary is so
important. Since you can’t possibly remember everything about your database
(like the names of all the tables and columns) Oracle remembers this for you.
All you need to do is learn how to find that information. We will be showing you
how to do this in a later section in this book.
The Architecture of the Data Dictionary
The data dictionary is created when the Oracle database
is created. It is owned by the SYS user, and is stored principally in the SYSTEM
tablespace, though some components are stored in the SYSAUX tablespace in Oracle
The data dictionary is comprised of a number of tables
and Oracle views. Oracle wants you to keep your hands off these tables, and
unless you are a real expert I’d recommend you do just that.
Of course, the data dictionary would be pretty worthless
if we could not access the data. Oracle supplies a number of views that you can
query that will give you direct access into the data dictionary tables. These
views are generally tuned by Oracle for quick access to the underlying objects
and the names of the views often reflect the use of that view much better than
the names of the underlying objects. The data dictionary views come in three
* User views
* All views
* DBA views
For example, if you want to look at user information
there are three views, USER_USERS, ALL_USERS and dba_users. Each of these views
sees the user a bit differently.
All views that start with USER only sees the information
that pertains to the user you are logged in as. For example, if you are logged
in as SCOTT, when you look at the user_tables view, you will only see
information on tables that are owned by the SCOTT user. You might have access to
tables in the GEORGE schema, but you won’t see them in the user_tables view.
Here is an example of a simple query against the user_tables view:
SELECT table_name FROM user_tables;
The ALL views allow you to see all objects that you have
access to. For example, if you are logged in as SCOTT and you query the
all_tables view, you will see all the tables owned by SCOTT but you will also
see any tables you have access to that are owned by GEORGE, or any other user.
You have to have access rights to these objects (which you would have received
via the grant command which we discussed in an earlier chapter).
Generally the two main differences between the USER and
ALL views is that the owner of the object is included in the ALL views, and this
is not included in the USER views which makes sense since you will only be
seeing your objects. In this example, we query the all_tables view for all
tables that start with EMP:
The granddaddy of the data dictionary views are the DBA
views. These views are unrestricted windows into all Oracle data dictionary
objects. Because of this, they are only accessible by DBA’s (as the name seems
to suggest). All DBA views start with DBA. In this example, we query the
dba_tables view for all tables that start with EMP and owned by users whose
names start with ROBERT:
You can find the data dictionary tables documented in
the Oracle Database 10g Reference Guide, which is part of the overall Oracle
database documentation set. There are almost 600 DBA views in Oracle Database
10g alone, and a like number of USER and ALL views. The ALL and USER views are
pretty much children of the DBA views, and you will not find ALL or USER views
for each DBA view. You can also find the views documented within the data
dictionary itself. The DICTIONARY (or DICT for short) view contains all the
tables of the data dictionary, plus comments on what each table is used for.
Data Dictionary Scripts
Like many things, using the data dictionary takes some
practice. It takes understanding what you want to find, and then looking at the
view, figuring out how to find the view. Sometimes, of course, you will find
yourself having to join two, three or more views together to get the answer you
To give you a start, in this section we are going to
provide you with some example queries against the data dictionary. These will be
queries that you might use in your early DBA exploits. Most DBA’s have a
collection of data dictionary scripts right at hand (I confess, we get a big
thrill out of just typing queries as we sit at the computer, testing my data
In this section we will provide you with examples of how
* Determine what users are setup in your database
* Determine what tablespaces are configured in your
database, and where the related datafiles are located.
* Determine who owns a specific table and its
* Determine what indexes are associated with a specific
Hopefully these examples will give you some insight into
how you can use the data dictionary to manage your database.
These examples are designed to give you some ideas of
how you can use the data dictionary views to manage your database. Very often,
good DBA’s will put scripts together that run on a regular schedule via CRON or
some other scheduling facility. These scripts will monitor the database looking
for problems, like running out of disk space.
There are tons of scripts out on the internet that you
can use for purposes like this. We want you to understand what these scripts are
doing though, and that is what these examples are for. Use them to learn about
how the data dictionary works, and to see the powerful information it provides.
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It’s only $19.95 when you buy it directly from the
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.