Data Dictionary

The Oracle data dictionary is the metadata of the database and contains the names and attributes of all objects in the database. The creation or modification of any object causes an update to the data dictionary that reflects those changes. This information is stored in the base tables that are maintained by the Oracle database, but you access these tables by using predefined views rather than reading the tables directly.

The data dictionary:

• Is used by the Oracle database server to find information about users, objects, constraints,and storage
• Is maintained by the Oracle database server as object structures or definitions are modified
• Is available for use by any user to query information about the database
• Is owned by the SYS user
• Should never be modified directly using SQL

The Data Dictionary consists of two components:

Base Tables: These tables store descriptions of objects in the database.

• These are the first objects created in the data dictionary.These are created when the oracle RDBMS software runs a special script named sql.bsq when a database is created by the CREATE DATABASE command – you do not see the sql.bsq script execute, but it does.
  You should never attempt to write to these tables – never use DML commands to attempt to update base tables directly.
  An example Base Table is IND$ that stores index information.

User-Accessible Views: These views summarize information in the base tables in order to make the information easier for a DBA to use.

• These views are created by the catalog.sql script.
• Using the Oracle Universal Installer to create a database, then the catalog.sql and catproc.sql scripts are run automatically.
• An example data dictionary user-accessible view is TABS – it stores information about tables you create as a system user. TABS is a synonym for the view ALL_TABLES.

The Data Dictionary stores information about all database objects created by system users and information systems professionals including tables, views, indexes, clusters, procedures, functions, synonyms, sequences, triggers and the like. For each object, the Data Dictionary stores:

• Disk space allocation (usually in bytes).
• Integrity constraint information.
• Default values for columns.
• Oracle user names (accounts)
• Privilege and role information for users.
• Auditing information – who has accessed/updated objects.

Data Dictionary Usage

The Data Dictionary is used by the Oracle RDBMS as illustrated in the figure shown below.

DBA: These views display information about objects stored in all schemas (a schema is a logical organization of objects belong to an individual system user). These views are named DBA_xxx where xxx is the object name.Because these views belong to the DBA and were created by the owner SYS so these views not access by public .
Example:
SELECT owner, object_name, object_type
FROM SYS.DBA_OBJECTS;

ALL: These views display "all" information that an individual user of the database is authorized to access – this will include information about your objects as well as information about objects for which you have access permissions.
If you connect to the database, the ALL_xxx views will display all information about objects of all database schemas (if you have access permissions).

SELECT owner, object_name, object_type FROM ALL_OBJECTS;

USER: These views display information that you would most likely want to access.

• These USER_xxx views refer to your own objects in your own schema.
• These views display only rows pertinent to you as a user.
• These views are a subset of the ALL views.
• These rows do not usually display the OWNER column.
  

          SELECT object_name, object_type  FROM USER_OBJECTS;

In general, Data Dictionary Views answer questions about:

• when an object was created,
• Is the object part of another objects,
• who owns the object,
• what privileges do you have as a system user,what restrictions are on an object.

Practice selecting information from the following three views: dba_objects, all_objects, user_objects. You may wish to use the SQL*Plus command DESC to describe the views.Additional queries you may wish to execute to practice accessing parts of the data dictionary:
SELECT table_name, comments
FROM dictionary
WHERE table_name LIKE '%SEGMENTS%';

Dynamic Performance Tables and Views

The Oracle Server records database activity to the Dynamic Performance Tables.
These are complemented by Dynamic Performance Views - virtual tables that exist only in memory when the database is running.

• The tables provide real-time condition information about database operation.
• DBAs use these tables—most ,users should not be able to access these tables.
• The tables cannot be altered – they are fixed and are owned by the user SYS.
• All Dynamic Performance Tables have names that begin with the letters V_$.
• Views of these tables are created along with synonyms that begin with the letters V$.
• Information about the database's datafiles and information about all of the dynamic performance tables and views.

Examples Dynamic Performance Table views:
V$CONTROLFILE: Lists the names of the control files
V$DATABASE: Contains database information from the control file.
V$DATAFILE: Contains datafile information from the control file
V$INSTANCE: Displays the state of the current instance
V$PARAMETER: Lists parameters and values currently in effect for the session
V$SESSION: Lists session information for each current session
V$SGA: Contains summary information on the system global area (SGA)
V$SPPARAMETER: Lists the contents of the SPFILE
V$TABLESPACE: Displays tablespace information from the control file
V$THREAD: Contains thread information from the control file
V$VERSION: Version numbers of core library components in the Oracle server

The DUAL Table

Oracle maintains a table named DUAL that is used by Oracle and by user programs to produce a guaranteed known result such as the production of a value through use of an Oracle defined function.
The table has one column named DUMMY and one row containing the value X.
SYS owns the DUAL table, but all users can select from it.
Selecting from DUAL is useful for computing a constant expression with a SELECT statement, since DUAL has only one row, the constant is returned only once.

Example

SQL> select 1+4 from dual;
       1+4
----------
         5

How the Data Dictionary Is Used

Oracle (internally) accesses information about users, schema objects, and storage structures.
This is done to validate a query executed by a system user.
Validates permission and security.
Verifies that referenced objects in queries actually exist.
Oracle modifies the data dictionary for each DDL statement executed.
Oracle users access the data dictionary as a read-only reference.

General Overview
– DICTIONARY, DICT_COLUMNS

• Schema objects
– DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS,
DBA_CONSTRAINTS

• Space allocation
– DBA_SEGMENTS, DBA_EXTENTS

• Database structure
– DBA_TABLESPACES, DBA_DATA_FILES

Modifying the Data Dictionary:

Only Oracle (SYS) should ever modify the data dictionary.During upgrades to new release versions of the Oracle RDBMS, scripts are provided to upgrade the data dictionary.