Oracle Fundamental I - Chapter 5: Data Dictionary and Dynamic Performance Views

In this chapter, we will discuss the other system-built objects, that are central to the Oracle database, which provides the important information to manage the Oracle database and administration work.

Data Dictionary
One of the most important parts of an oracle database is its data dictionary, which is a read-only set of tables and views that provides information about its associated database. The data dictionary is updated by the oracle server whenever a data definition language (DDL) command is executed. In addition, data manipulation language (DML) commands, such as one that causes a table to extend, can update the data dictionary.
Not only is the data dictionary central to every oracle database, it is an important source of information for all users, from end users to application designers and database administrators.
SQL statements are  used to access the data dictionary.

The data dictionary contains two parts:

• Base tables
• stores description of the database
• Created with CREATE DATABASE
• Data dictionary views
• Used to simplify the base table information
• Accessed through public synonyms
• Created with the catalog.sql script

The data dictionary provides information about:

• Logical and physical database structures
• Definitions and space allocations of objects
• Integrity constraints
• Users
• Roles
• Privileges
• Auditing

There are three categories of Data Dictionary static views:
Distinguished by their scope:
DBA: What is in all the schemas
All: What the user can access
USER: What is in the user's schema

To query on all objects in the database, the DBA can issue the following statement:
SQL> SELECT owner, object_name, object_type FROM dba_objects;
The following example returns information about all the objects to which a user has access:
SQL> SELECT owner, object_name, object_type FROM all_objects;
The following query returns all the objects contained in the user's schema:
SQL> SELECT OBJECT_NAME, OBJECT_TYPE from users_objects;

Some Data Dictionary Examples below:

• General overview: DICTIONARY, DICT_COLUMNS
• Schema objects: DBA_TABLES, DBA_INDEXES, DBA_CONSTRAINTS
• Space allocation: DBA_SEGMEN?TS, DBA_EXTENTS
• Database structure: DBA_TABLESPACES, DBA_DATA_FILES

Dynamic Performance Tables:
Throughout its operation, the Oracle server records current database activity in a set of virtual tables called dynamic performance views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation. They point to actual sources of information in memory and the control file.
These tables are not true tables, and are not to be accessed by most users; however, DBA can query, grant the SELECT privilege, and create views on these views. These views are sometimes called fixed views because they cannot be altered or removed by the DBA.
The dynamic performance tables are owned by SYS, and their names all begin with V_$.
Views are creaetd on these tables, and then public synonyms are created for the views. The synonym names begin with V$. For example, the V$DATAFILE view contains information about the database's data files, adn the V$FIXED_TABLE view containts information about all of the dynami performance tables and views in the database.
Some examples: V$CONTROLFILE, V$DATABASE, V$DATAFILE, V$INSTANCE, V$PARAMETER, V$SESSION, V$SGA.

Summary:
There are more than hundreds of data dictionary views and dynamic views in Oracle database. It's almost impossible for any one to remember all the views in head. One general way to find out the data dictionary view is to issue the query like:
"select TABLE_NAME from DICTIONARY where TABLE_NAME like 'INDEX';"
It will return the data dictionary views which has INDEX in its name.