Oracle Data Dictionary and the Dynamic Performance Views

An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database.

A data dictionary contains information such as the following:

·         The definitions of every schema object  in the database, including default values for columns and integrity constraint information

·         The amount of space allocated for and currently used by the schema objects

·         The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users

The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:

·         Accesses the data dictionary to find information about users, schema objects, and storage structures

·         Modifies the data dictionary every time that a DDL statement is issued

Because Oracle Database stores data dictionary data in tables, just like other data, users can query the data with SQL. For example, users can run SELECT statements to determine their privileges, which tables exist in their schema, which columns are in these tables, whether indexes are built on these columns, and so on.

 

Some commonly used Data Dictionary views include:

USER_ Views (or ALL_ and DBA_ Views):

USER_TABLES: Information about tables owned by the current user.

USER_COLUMNS: Details about columns of tables owned by the current user.

USER_VIEWS: Information about views owned by the current user.

ALL_ Views:

Similar to USER_ views, but they show information about objects accessible to the current user.

DBA_ Views:

These views show information about all objects in the database, but access to these views requires DBA privileges.

Dynamic Performance Views

Throughout its operation, Oracle Database maintains a set of virtual tables that record current database activity.

These views are dynamic because they are continuously updated while a database is open and in use. The views are sometimes called V$ views because their names begin with V$.

Dynamic performance views contain information such as the following:

• System and session parameters
• Memory usage and allocation
• File states (including RMAN backup files)
• Progress of jobs and tasks
• SQL execution
• Statistics and metrics

Some commonly used Dynamic Performance Views include:

V$SESSION:

Information about current sessions, including session ID, username, and status.

V$SQL:

Details about SQL statements currently in the shared SQL area, including SQL text and execution statistics.

V$WAITSTAT:

Information about wait statistics, showing where sessions are waiting.

V$DATAFILE:

Details about database data files, including file name, size, and status.