Home > Oracle Tutorials > Data Dictionary Tutorial

Data Dictionary Tutorial

Data dictionary — metadata — system & object privileges — dictionary structure — ``user'' tables - ``all'' tables — ``dba'' tables — ``v$'' tables — frequently used tables — usage examples — exercises — using the dictionary in PL/SQL programs — optional exercise.

Introduction

This document presents Oracle's data dictionary, also called the system catalogue. The data dictionary is the repository of all the meta-data relevant to the objects stored in the database—and also of information concerning the DBMS itself.

Dictionary Content

Defining metadata.

The term metadata is often defined as data about data. That is, data that provides information about the tables, views, constraints, stored procedures, etc. stored within the database. If we take a table as an example, the dictionary will store information such as:

its name
when it was created and when it was last accessed
the names and data types of its attributes (i.e. structural information)
its owner, who may read and write to it (i.e. security information)
where the data is stored (i.e. physical information)

Security in Oracle.

Oracle defines two categories of privileges: object privileges and system privileges. Both categories are granted and revoked using the GRANT and REVOKE SQL constructs: GRANT <object_privilege> ON <object> TO <user> and GRANT <system_privilege> TO <user>. You have already used the former (see Introduction to Oracle.)
System privileges mainly specify the types of objects a user is allowed to manipulate (tables,...) and what (s)he can do with them. Object privileges define the access rights at the objects level (and even at the attribute level for tables).

Dictionary Structure

The data dictionary is implemented in Oracle as a set of read-only tables and views.

**Figure 1:** Hierachical structure of the data dictionary.

Figure 1 presents the two-level structure of the dictionary. At the root of the tree is the dictionary table, that features two attributes: table_name and comments. The comment field presents an informal description of the corresponding dictionary table.
For instance, we can request information about the dictionary table:

SQL> SELECT comments 
  2  FROM dictionary WHERE table_name='DICTIONARY'
  3  /

and get:

Description of data dictionary tables and views

As an exercise, write a query to find out how many tables make up the data dictionary.

The second level of the dictionary is divided into four categories of tables. ``User'' tables describe the objects you own. They are only accessible to you. ``All'' tables describe the objects of all the users, and are accessible to all the users. ``DBA'' tables contain information only relevant and accessible to database administrators. And last, ``V$'' tables reflect the internal state of the DBMS and are mainly useful to DBAs for performance audit and optimisation.
You should refer to Figure 1 for a list of commonly-used dictionary tables. Also, remember that you can obtain the schema of any table with the desc command¹ (see Introduction to Oracle)

Add to del.icio.us
Contents
Data Dictionary Content
Data Dictionary Structure
Data Dict., Part 2
See Also
Oracle Basics
PL/SQL
SQL Built-In Functions
Oracle & Java
Advanced Oracle & Java

Tell Your Friends

Top