Data dictionary views in Oracle

Oct 21, 2024

—

1. Introduction

The data dictionary refers to the core collection of read-only reference tables and views within every Oracle database. The dynamic performance views are special views that are continuously updated while a database is open and in use. Since Oracle Database stores the data dictionary information in tables, similar to other data, users can access it through SQL queries. For instance, users can execute SELECT statements to check their privileges, identify the tables in their schema, view the columns within those tables, verify whether indexes are applied to those columns, and more.

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.

2. Overview of the Data Dictionary

Data dictionary 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.

Much of the data dictionary information is in the data dictionary cache because the database constantly requires the information to validate user access and verify the state of schema objects.

3. Contents of the Data Dictionary

The data dictionary consists of base tables and views.

Base tables: These tables hold critical information about the database, and only Oracle Database should handle reading from and writing to them. Users typically don’t interact with the base tables directly, as they are highly normalized, and much of the data is stored in a complex, hard-to-read format.
Views: These views interpret the base table data into meaningful information, like user or table names, by utilizing joins and WHERE clauses to make the data easier to understand. They provide details about all objects in the data dictionary, including names and descriptions. While some views are available to all users, others are restricted for use by administrators only.

Data dictionary views are usually organized into sets. Often, a set includes three views that provide similar information, but are differentiated by their prefixes, as illustrated in the table below. By querying the specific views, you can retrieve only the information that is most relevant to your needs.

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.

4. DICTIONARY view

The system-supplied DICTIONARY view contains the names and abbreviated descriptions of all data dictionary views. Following is the sample output of the query:

SELECT * FROM DICTIONARY;

5. Views prefixed with dba_

Includes all the objects of all users. Only DBAs and users who have the privilege can access. Views with the prefix DBA_ show all relevant information in the entire database. DBA_ views are intended only for administrators.

The following sample query shows information about all objects in the database:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS;

6. Views with the Prefix ALL_

Includes all the objects in user’s schema and the objects that the user can access. Views with the prefix ALL_ refer to the user’s overall perspective of the database. These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns.

For example, the following query returns information about all the objects to which you have access:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 
FROM   ALL_OBJECTS
ORDER BY OWNER, OBJECT_NAME;

These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns.

7. Views with the Prefix USER_

Includes all the objects in user’s schema. These views:

Refer to the user’s private environment in the database, including metadata about schema objects created by the user, grants made by the user, and so on.
Display only rows pertinent to the user, returning a subset of the information in the ALL_ views.
Has columns identical to the other views, except that the column OWNER is implied.
Can have abbreviated PUBLIC synonyms for convenience.

For example, the following query returns all the objects contained in your schema:

SELECT OBJECT_NAME, OBJECT_TYPE 
FROM   USER_OBJECTS
ORDER BY OBJECT_NAME;

8. The DUAL Table

DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result.

The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.

The DUAL table has one column called DUMMY and one row containing the value X. The following example queries DUAL to perform an arithmetical operation:

SELECT (1+2)/3 FROM DUAL;

9. Storage of the Data Dictionary

The data dictionary base tables are the first objects created in any Oracle database.

All data dictionary tables and views for a database are stored in the SYSTEM tablespace. Because the SYSTEM tablespace is always online when the database is open, the data dictionary is always available when the database is open.

10. How Oracle Database Uses the Data Dictionary?

The Oracle Database user account SYS owns all base tables and user-accessible views of the data dictionary.

During database operation, Oracle Database reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle Database updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.

For example, if user hr creates a table named interns, then the database adds new rows to the data dictionary that reflect the new table, columns, segment, extents, and the privileges that hr has on the table. This new information is visible the next time the dictionary views are queried.

Data in the base tables of the data dictionary is necessary for Oracle Database to function. Only Oracle Database should write or change data dictionary information. No Oracle Database user should ever alter rows or schema objects contained in the SYS schema because such activity can compromise data integrity. The security administrator must keep strict control of this central account.

11. Overview of the 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

The dynamic performance views have the following primary uses:

Oracle Enterprise Manager uses the views to obtain information about the database.
Administrators can use the views for performance monitoring and debugging.

12. Contents of the Dynamic Performance Views

Dynamic performance views are called fixed views because they cannot be altered or removed by a database administrator. However, database administrators can query and create views on the tables and grant access to these views to other users.

SYS owns the dynamic performance tables, whose names begin with V$. Views are created on these tables, and then public synonyms prefixed with V$. For example, the V$DATAFILE view contains information about data files. The V$FIXED_TABLE view contains information about all of the dynamic performance tables and views.

For almost every V$ view, a corresponding GV$ view exists. In Oracle Real Application Clusters (Oracle RAC), querying a GV$ view retrieves the V$ view information from all qualified database instances.

13. USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS views

13.1 USER_OBJECTS view

We can query USER_OBJECTS view to see objects we own. Following are the important information in the view:

Column name	Description
OBJECT_NAME	Name of the object
OBJECT_TYPE	Type of the object (table, view etc.)
CREATED	Time of the creation of the object
LAST_DDL_TIME	Last modification time of the object.
STATUS	Status of the object(valid, invalid etc)
GENERATED	Is the object system-generated or not (Y/N)

13.2 ALL_OBJECTS View

We can query ALL_OBJECTS view to see all objects that we own and have the privileges to access.

select * from all_objects;

13.3 DBA_OBJECTS view

We can query DBA_OBJECTS view to see all objects of all the users including system objects.

select * from DBA_OBJECTS;

13.4 USER_TABLES view

We can query USER_TABLES view to see all the tables that we own.

select * from USER_TABLES;

TABS is the synonym for the USER_TABLES.

select * from TABS;

In Oracle, the views USER_TABLES, ALL_TABLES, and DBA_TABLES provide information about tables in the database, but they differ in scope and who can access them.

13.5 USER_TABLES

Shows details about the tables owned by the current user.
Accessible to any user.
Contains metadata such as table name, number of columns, and storage information for tables owned by the user.

13.6 ALL_TABLES

Displays information about all tables that the current user has access to, including those owned by other users.
Useful for seeing tables where the user has privileges, even if they are not the owner.

13.7 DBA_TABLES

Provides details about all tables in the entire database.
Only accessible to users with administrative privileges, like those with the DBA role.
Includes information on all tables, regardless of ownership or access rights.
Each view serves different levels of visibility based on the user’s privileges.

13.8 USER_TAB_COLUMNS

USER_TAB_COLUMNS is a data dictionary view in Oracle that provides information about the columns of tables owned by the current user. Each row in this view represents a column in one of the user’s tables.

Key information available in USER_TAB_COLUMNS:

TABLE_NAME: The name of the table to which the column belongs.
COLUMN_NAME: The name of the column.
DATA_TYPE: The datatype of the column (e.g., VARCHAR2, NUMBER, DATE).
DATA_LENGTH: The length or size of the column for character data types.
DATA_PRECISION and DATA_SCALE: For numeric columns, these fields provide information about the precision and scale.
NULLABLE: Indicates whether the column can contain NULL values (Y or N).
COLUMN_ID: The position of the column within the table.

This view is particularly useful for querying metadata about the structure of the columns in tables owned by the current user.

select * from USER_TAB_COLUMNS;

COLS is the synonym for the USER_TAB_COLUMNS.

select * from COLS;

Example:

select column_name, data_type, data_length, nullable, data_default, num_nulls, avg_col_len, num_distinct 
from USER_TAB_COLS where table_name='EMPLOYEE';

Like we have ALL_OBJECTS, DBA_OBJECTS, USER_OBJECTS we have ALL_TAB_COLUMNS, DBA_TAB_COLUMNS, USER_TAB_COLUMNS.

13.9 USER_CONSTRAINTS view

USER_CONSTRAINTS is a data dictionary view in Oracle that provides information about the constraints defined on tables owned by the current user. Each row represents a constraint on a table, offering details about various types of constraints such as primary keys, foreign keys, unique constraints, and check constraints.

select * from USER_CONSTRAINTS;

Key information available in USER_CONSTRAINTS:

CONSTRAINT_NAME: The name of the constraint.
TABLE_NAME: The table on which the constraint is defined.
CONSTRAINT_TYPE: The type of constraint (e.g., P for primary key, R for foreign key, U for unique, C for check).
SEARCH_CONDITION: For check constraints, this column contains the condition that must be satisfied.
STATUS: Indicates whether the constraint is enabled (ENABLED) or disabled (DISABLED).
DEFERRABLE: Shows if the constraint is deferrable, meaning it can be deferred until the transaction commits.
INDEX_NAME: The index used by the constraint.

This view is useful for managing and inspecting the integrity rules (constraints) that apply to the user’s tables.

In the USER_CONSTRAINTS view, the CONSTRAINT_TYPE column provides a single character value that represents the type of constraint on a table. The possible values for CONSTRAINT_TYPE are:

C: Check constraint – ensures that a column value meets a specific condition.
P: Primary key – uniquely identifies each row in a table and does not allow NULL values.
U: Unique constraint – ensures that all values in a column or set of columns are unique, but allows NULL values.
R: Referential (foreign key) constraint – enforces a relationship between two tables by requiring that a value in one table matches a value in another.
V: View constraint – applies constraints to views.
O: Read-only constraint for views.

These codes provide an easy way to identify the type of constraint applied to a table in Oracle.