Data Dictionary and its importance in Oracle Database #DOTC_Mdb

 

Data Dictionary and its importance in Oracle Database.

 

Today I will discuss about Data Dictionary table in Oracle Database. Data Dictionary table provide information needed by those who build systems and applications that support the data. If you know the data dictionary table names you can able to see the objects present in the database as well as also you can able to see the design , relations etc by using Data Dictionary table. Let understand about Data Dictionary first.

Data Dictionary: Most important parts of an Oracle database is its data dictionary, which is a read only set of tables that provides information about the database. In Oracle database data dictionary provide useful information about the database including schema, users, privileges etc. The values in these stored dictionaries are updated automatically by Oracle anytime a statement is executed on the server that modifies the data.

Note: Read-only data dictionaries can be read and queried just like any other table by using select statement.

Data Dictionary table can have below things:

Definitions of all schema objects in the database like tables, views, indexes, synonyms, sequences, procedures, functions, packages, triggers etc.

Through Data Dictionary table we can able to find how much space has been allocated for, and is currently used by, the schema objects.

Integrity constraint information : To understand Parent – child Relations

Oracle users Name : To see the user’s details in database

Through Data Dictionary table we can know privileges and roles each user has been granted .Auditing information, such as who has accessed or updated various schema objects

 

Note: Data Dictionary Table is an important tool for all users, from end users to application designers and database administrators (DBA). We can simply use SQL statements to access the data dictionary. Also data dictionary is read only table.

 

Few examples of Data dictionary table by using select statement to show the list of objects present in the database.

To find the list of all the tables owned by the current user, we can use data dictionary table ALL_TABLES by simply using select statement same as we write in query . Below is the simple query to list all the tables stored in the database for current user.

SELECT

  table_name, owner

FROM

  all_tables

ORDER BY

  owner, table_name;

This will return a list of all tables from current user is owner of, as specified in the owner column.

Output of above query is as below:

 

Here we can get multiple result , as we quering this in server to filter this we just need to make one condition in SQL query to get result according to schema name . Below is the query you can use to get this

SELECT

  table_name, owner

FROM

  all_tables

WHERE

  owner='SAKSHI'

ORDER BY

  owner, table_name;

 

In above query SAKSHI is schema name .

Output of above query is as below :

 

To see the relationship between the tables we can use data dictionary table USER_CONSTRAINTS . if you know the constraints name then you can simply use the below query to see what type of constraints is that and also you can able to find the relations . USER_CONSTRAINTS describes all constraint definitions on tables owned by the current user. Its columns are the same as those in ALL_CONSTRAINT table.

Query we can use as below :

Select * from USER_CONSTRAINTS where constraint_name='SYS_C007488';

Above query will show you about the constraint type and you can know about this constraint.

Output of above query is as below:

 

 

To see the dependencies of objects we use data dictionary table USER_DEPENDENCIES table that describes dependencies between procedures, packages, functions, package bodies, and triggers owned by the current user, including dependencies on views created without any database links.

Note : ALL_DEPENDENCIES also we can use , but  in USER_DEPENDENCIES all the columns are same as ALL_DEPENDENCIES.

 

To see all the objects in the database we use  ALL_OBJECT data dictionary table which describes all objects accessible to the current user and have two views which commonly used i.e. DBA_OBJECTS describes all objects in the database and USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column. We commonly used USER_OBJECTS view to see the object and can also filter with object_type. Below is the query to see all the procedures available in database for that particular user.

SELECT * FROM

USER_OBJECTS

WHERE OBJECT_NAME='PROCEDURE'; 

Above query will give all the procedures name and if we want to see procedures as well as Functions then we can use below query :

SELECT * FROM

USER_OBJECTS

WHERE OBJECT_NAME IN ('PROCEDURE','FUNCTION');

Similarly we can find for other objects like table, packages, index, trigger etc by simply using USER_OBJECTS data dictionary table.

 

There are lots of data dictionary table available in Oracle Database the above data dictionary tables we mentioned is commonly used . So to find the objects in a database , find the relations between the tables etc we can simply use data dictionary table to get the result . It’s very important if you are new to any projects and you want to see the objects and it’s relations Data Dictionary table play very important role for you  to get the overview of the project as you can find some relations between the tables as well as dependent objects like procedures, functions etc.

Note: In other database flavor like MySQL , Sybase,DB2 etc. Data dictionary table name can be different but works as the same to see the dependencies of the objects as well as to see the relations between the objects etc.

Please go through this blog carefully and let me know if you have any questions. Also would like to tell you that in Oracle lot’s of data dictionary tables available but I mentioned the commonly used data dictionary tables. Some questions can be asked in interview from Data Dictionary, but practically it is useful for you when you are working on any projects.

Thanks.