Data Dictionary and Dynamic Performance Views

The central set of read-only reference tables and views of each Oracle database is known collectively as the data dictionary. The dynamic performance views are special views that are continuously updated while a database is open and in use.

Overview of the Data Dictionary : 

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.

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$.

These 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

Contents of the Data Dictionary : 

The data dictionary consists of base tables and views.

These objects are defined as follows:

• Base tables
  These store information about the database. Only Oracle Database should write to and read these tables. Users rarely access the base tables directly because they are normalized and most data is stored in a cryptic format.
• Views
  These decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. The views contain the names and description of all objects in the data dictionary. Some views are accessible to all database users, whereas others are intended for administrators only.

Typically, data dictionary views are grouped in sets. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes, as shown in the
following table. By querying the appropriate views, you can access only the information relevant for you.

Prefix	User Access	Contents	Notes
DBA_	Database administrators	All objects	Some DBA_ views have additional columns containing information useful to the administrator.
ALL_	All users	Objects to which user has privileges	Includes objects owned by user. These views obey the current set of enabled roles.
USER_	All users	Objects owned by user	Views with the prefix USER_ usually exclude the column OWNER. This column is implied in the USER_ views to be the user issuing the query.

Data Dictionary frequently used to monitor / Manage / Maintain Oracle Database 
Which users are in the database password file: 
V$PWFILE_USERS 

Where values set in the init.ora file can be viewed – all parameters: 
V$PARAMETER 

Script used to create the objects that comprise the data dictionary: 
catalog.sql 

To grant a special role to users so they can look at DBA views: 
SELECT_CATALOG_ROLE 

Information about all database objects in the database: 
DBA_OBJECTS 

Information about all tables in the database: 
DBA_TABLES 

Information about all indexes in the database: 
DBA_INDEXES 

Information about all views (including dictionary views) in the database: 
DBA_VIEWS 

Information about all sequences in the database: 
DBA_SEQUENCES 

Information about all users in the database: 
DBA_USERS 

Information about all constraints in the database: 
DBA_CONSTRAINTS 

Information about all table columns that have constraints on them: 
DBA_CONS_COLUMNS 

Information about all columns that have indexes on them in the database:
DBA_IND_COLUMNS 

Information about all columns in all the tables in the database: 
DBA_TAB_COLUMNS 

Information about all the roles in the database: 
DBA_ROLES 

Information about all object privileges in the database: 
DBA_TAB_PRIVS 

Information about all system privileges granted to all users in the database: 
DBA_SYS_PRIVS 

Displays all PL/SQL source code in the database: 
DBA_SOURCE 

Information about all triggers in the database: 
DBA_TRIGGERS 

Information about object privileges granted to roles 
ROLE_TAB_PRIVS 

Information about system privileges granted to roles 
ROLE_SYS_PRIVS 

Information about roles granted to roles 
ROLE_ROLE_PRIVS 

Information about all tablespaces in the database: 
DBA_TABLESPACES 

Information about all profiles in the database: 
DBA_PROFILES 

For all parameters? 
V$PARAMETER 

General information about the database mounted to your instance: 
V$DATABASE 

Most information about the performance of the database is kept here: 
V$SYSSTAT 

Most information about the performance for individual user sessions is stored here: 
V$SESSION , V$SESSTAT 

Information about online redo logs (2) 
V$LOG, V$LOGFILE 

Information about datafiles 
V$DATAFILE 

Basic information about control files, and the two columns it has: 
V$CONTROLFILE. STATUS / NAME 

An object you can query to obtain a listing of all data dictionary objects (4) 
CATALOG, CAT, DICTIONARY, DICT. 

When the control file was created, Sequence Number, most recent SCN: 
V$DATABASE 

Information stored in different sections of the control file, Sequence Number: 
V$CONTROLFILE_RECORD_SECTION 

To see the names and locations of all control files in the db? (2) 
V$PARAMETER. V$CONTROLFILE 

Tablespace and Datafiles 
Temporary Segments: 
Name, tablespace location, and owner of temporary segments: 
DBA_SEGMENTS 

Size of temporary tablespaces, current number of extents allocated to sort segments, and sort segment high-water mark information. Space usage allocation for temporary segments: 
V$SORT_SEGMENT 

Types of sorts that are happening currently on the database 
V$SORT_USAGE 

To see the username corresponding with the session: 
V$SESSION 

Information about every datafile in the database associated with a temporary tablespace: 
DBA_TEMP_FILES 

Similar to DBA_TEMP_FILES, this performance view gives Information about every datafile in the database associated with a temporary tablespace: 
V$TEMPFILE 

Storage Structures 
A summary view, contains all types of segments and their storage parameters, space utilization settings: 
DBA_SEGMENTS 

Tablespace quotas assigned to users: 
DBA_TS_QUOTAS 

Segment name, type, owner, total bytes of extent, name of tablespace storing the extent: 
DBA_EXTENTS 

The location and amount of free space by tablespace name: 
DBA_FREE_SPACE 

The location of free space in the tablespace that has been coalesced: 
DBA_FREE_SPACE_COALESCED 

Information about datafiles for every tablespace 
DBA_DATAFILES 

Performance view for information for datafiles for every tablespace 
V$DATAFILE 

To see the total amount of space allocated to a table? 
DBA_EXTENTS 

Table creation timestamp, information about the object ID: 
DBA_OBJECTS 

High water mark, all storage settings for a table, and statistics collected as part of the analyze (for row migration) operation on that table 
DBA_TABLES 

Information about every column in every table: 
DBA_TAB_COLUMNS 

To determine how many columns are marked unused for later removal? 
DBA_UNUSED_COL_TABS 

To find the number of deleted index entries ? 
INDEX_STATS 

To determine the columns on a table that have been indexed: 
DBA_ID_COLUMNS 

The dynamic view to show whether the index is being used in a meaningful way? 
V$OBJECT_USAGE 

To see whether a constraint exists on a particular column? 
DBA_CONS_COLUMNS 

To see the constraints associated with a particular table: 
DBA_CONSTRAINTS 

To find the username, ID number, (encrypted) password, default and temporary tablespace information, user profile of a user, password expiry date: 
DBA_USERS 

To all objects, which objects belong to which users, how many objects a user has created? 
DBA_OBJECTS 

Resource-usage parameters for a particular profile: 
DBA_PROFILES 

Identifies all resources in the database and their corresponding cost: 
RESOURCE_COST 

Identifies system resource limits for individual users: 
USER_RESOURCE_LIMITS 

Shows all system privileges: 
DBA_SYS_PRIVS 

Show all object privileges: 
DBA_TAB_PRIVS 

Shows all privileges in this session available to you as the current user: 
SESSION_PRIVS 

Views for audits currently taking place are created by this script: 
cataudit.sql 

a list of audit entries generated by the exists option of the audit command: 
DBA_AUDIT_EXISTS 

A list of audit entries generated for object audits: 
DBA_AUDIT_OBJECT 

A list of audit entries generated by session connects and disconnects: 
DBA_AUDIT_SESSION 

A list of audit entries generated by statement options of the audit command: 
DBA_AUDIT_STATEMENT 

A list of all entries in the AUD$ table collected by the audit command: 
DBA_AUDIT_TRAIL 

To determine the roles available in the database, the names of all the roles on the database and if a password is required to use each role: 
DBA_ROLES 

Names of all users and the roles granted to them: 
DBA_ROLE_PRIVS 

All the roles and the roles that are granted to them: 
ROLE_ROLE_PRIVS 

Which system privileges have been granted to a role: 
DBA_SYS_PRIVS 

All the system privileges granted only to roles: 
ROLE_SYS_PRIVS 

All the object privileges granted only to roles: 
ROLE_TAB_PRIVS 

All the roles available in the current session: 
SESSION_ROLES 

Which object privilege has been granted to a role: 
DBA_TAB_PRIVS 

To display the value of the NLS_CHARACTERSET parameter: 
NLS_DATABASE_PARAMETERS