Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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 forms:

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 statistics

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

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:

* dba_all_tables

* dba_indexes

* dba_ind_partitions

* dba_ind_subpartitions

* dba_object_tables

* dba_part_col_statistics

* dba_subpart_col_statistics

* dba_tables

* dba_tab_cols

* dba_tab_columns

* dba_tab_col_statistics

* dba_tab_partitions

* dba_tab_subpartitions

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.  For a collection of pre-written Oracle data dictionary scripts, see www.dba-oracle.com/oracle_scripts.htm .

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:

CONNECT system/your_password
SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name='BOOKS';

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 Database 10g.

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 discuss:

* 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 Database 10g.

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 main flavors:

* 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:

SELECT
   table_name
FROM
   all_tables
WHERE
   table_name LIKE '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:

SELECT
   table_name
FROM
   dba_tables
WHERE
   table_name LIKE 'EMP%'
AND
   owner like '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 need.

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 dictionary knowledge).

In this section we will provide you with examples of how to:

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

* Determine what indexes are associated with a specific table.

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

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.