Data Dictionary

Data Dictionary is an important component of any database just like it is of oracle. Data Dictionary holds the meta data. By meta data we mean data about the data. Oracle maintains information about the data it stores in data dictionary. Data dictionary holds information like logical and physical structure of the database, object definitions, Integrity constraints, user privileges, and auditing.

When a database starts two scripts run. First is called sql.bsq and second is known as catalog.sql. When sql.bsq runs it creates the base tables. Base tables are the tables that contain the actual information. All the information stored in data dictionary is in the form of these base tables. When catalog.sql runs it creates views. These views are based on the base tables. Actually a user or even a DBA can’t see the base tables. Whatever information you need to retrieve from the data dictionary is available to you through these views and they are from one of the following categories.

With DBA Prefix: The views that have a prefix of DBA in their names are only visible to a DBA. You can see them when you are login as sysdba. They contain information about the entire database.

With User Prefix: The views with user as their prefix will show the user information about the objects which belong to him.

With All Prefix: The views with all prefix will show the user information about objects that belong to him and also on which he has been granted permission by some other user or DBA.

Let us clarify with an example. There are three views dba_tables, user_tables, and all_tables. The dba_tables view will show you the information about all the tables the database has. The user_tables view will show you the information about the tables which are your self created. The all_tables view will show you the information about the tables which are either your self created or on which you have permissions.

There is another category of data dictionary views Dynamic Performance Views. They contain all the dynamic information about the database which is running at that time. They all have v$ as the prefix with their names. And usually they are known as v$ views. These dynamic views take information from shared global area and from control files. Some examples of these are:

V$session: Will show you the information about current sessions.

V$instance: It will show you the information about current instance.

V$datafile: It will show you the information about currently used data files.