Data Dictionaries in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Data Dictionaries in Oracle with Examples

In this article, I am going to discuss Data Dictionaries in Oracle with Examples. Please read our previous article where we discussed Referential Integrity Constraints in Oracle with Examples.

Data Dictionaries (or) Read-Only Tables in Oracle:

Whenever we are installing oracle s/w internally oracle server is creating some pre-define tables are called “DATA DICTIONARIES”. These data dictionaries are used to store the information about database objects such as Tables, Indexes, Views, Synonyms, etc.

These data dictionaries are supporting the “SELECT” and “DESC” command only. So that data dictionaries are also called “READ ONLY TABLES” in the oracle database. If we want to view all data dictionaries in the oracle database then we need to use the following syntax

SELECT * FROM DICT;

Example to understand Data Dictionaries in Oracle:

Please create the Department and Employees table by executing the following SQL Script. If you notice in the below two tables, in the Department table we created Id as the Primary key. In the Employees table we created the Id column as the Primary Key, we also applied NOT NULL Constraints on the Name and Age column. Further, if you notice we applied UNIQUE Constraint on the Email column and CHECK constraint on the Age column.

CREATE TABLE Department( 
    Id   INT PRIMARY KEY, 
    Name VARCHAR2(10) 
);

CREATE TABLE Employees(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  Email VARCHAR2(20) UNIQUE,
  Age INT NOT NULL CHECK (AGE >= 18),
  DepartmentID INT REFERENCES Department(Id)
);

Note1:

If we want to view all constraints information of a particular table then we need to use the “USER_CONSTRAINTS” data dictionary.

DESC USER_CONSTRAINTS;

Once you execute the above query, you will get the following output.

Now, let us verify all the constraints names and types of the Employees table by executing the below query.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEES';

Once you execute the above query, you will get the following output. Here, C means CHECK constraints, P means Primary Key Constraints, U means unique Key Constraints and R means Referential Integrity constraints.

Note2:

If you want to view the constraint name along with the column name of a particular table then you need to use the “USER_CONS_COLUMNS ” data dictionary.

DESC USER_CONS_COLUMNS;

When you execute the above query, you will get the following output.

Now, let us verify all the constraints name and column names of the Employees table by executing the below query.

SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='EMPLOYEES';

When you execute the above SELECT query, you will get the following output.

Note3:

To view a logical condition of check constraint or NOT NULL constraint, we need to call the “SEARCH_CONDITION” column from the “USER_CONSTRAINTS” data dictionary.

DESC USER_CONSTRAINTS;
SELECT SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEES';

When you execute the above SELECT statement, you will get the following output.

Note4:

To view all columns information of a particular table we need to use the “USER_TAB_COLUMNS” data dictionary.

DESC USER_TAB_COLUMNS;

When you execute the above query, you should get the following output.

Now, let us find all the column information of the Employees table by executing the below query.

SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMPLOYEES';

When you execute the above query, you will get the following output.

How to find the number of rows in a table?

SELECT COUNT(*) NoOfRows FROM EMPLOYEES;

As we don’t have any record in the Employees table, when we execute the above query, we will get the following output.

How to find the number of columns in a table?

SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMPLOYEES';

When we execute the above query, we will get the following output.

In the next article, I am going to discuss How to Create Constraints with user-defined names in Oracle with Examples. Here, in this article, I try to explain Data Dictionaries in Oracle with Examples and I hope you enjoy this Data Dictionaries in Oracle article.