Database Components   «Prev  Next»

Lesson 3 Data dictionary views
Objective Name several data dictionary views and their purpose.

Oracle Data Dictionary Views


Perhaps you have heard the term meta-data[1].
Meta-data is literally beyond data. In an Oracle database, meta-data is the description of your data. Questions answered by meta-data include:
  1. What are the names of my tables and columns?
  2. What is the maximum size of this table?
  3. Who can view this table?
  4. When was this user created?

Oracle Exclusive

Oracle stores meta-data in its own set of relational tables. These tables cannot be seen or modified. However, Oracle provides a set of views called the data dictionary views to allow you access to the information. Data dictionary views contain rows and columns of data, just like an ordinary view. This means that you can use them in SQL queries.

Groups of views

The name of the data dictionary view helps you decipher what the view contains. There are three general groups of views, based on the first word in the name.

User indexes all Objects
Nearly all the data dictionary views come in sets of three views, one for USER, one for ALL, and one for DBA.
For example, one set of views is USER_VIEWS, another is ALL_VIEWS, and the third is DBA_VIEWS.

Data Dictionary Views

The following table lists a few of the hundreds of data dictionary views available to you.

Data dictionary view name Description
ALL_CATALOG All tables, views, and synonyms that the current user is allowed to access
ALL_USERS This includes names of all Oracle users. This view is available to the Database Administrator. Passwords are never displayed.
DBA_CONSTRAINTS All constraints in the database
DBA_ROLES All roles that exist in the database
USER_CATALOG Tables, sequences, views, and other objects owned by the current user
USER_ROLE_PRIVS Roles assigned to the current user
USER_TAB_COL_STATISTICS Low, high, and average values in a table's columns
USER_TAB_PRIVS Privileges granted to the current user
USER_TAB_PRIVS_MADE Privileges granted to others by the current user
USER_TABLES This shows tables owned by the current user. This view includes statistics such as the number of rows and space allocated and space used.
USER_TYPES Object types created by the current user

You can use the DESC command within SQL*Plus to display a list of the columns contained in any table or view, including the above data dictionary views. In the next lesson, you will begin looking at database tables using SQL*Plus.

Five Data dictionary Views

Click the link below to read about five data dictionary views that can be answered by that view.
Five Data dictionary Views
[1]Meta-data: Information (data) about database structures. For example, the meta-data about a table includes its name, its column names, and the tablespace in which it resides.