Table of Contents
Functions of Data Dictionary
The main functions of data dictionaries are
- It defines the data objects of each user in the database. By this way, it helps various users to know all the objects which exist in the database and who can access it. One cannot remember all the tables, views constraints etc in a huge database. If the user wants to search for one particular table, say something related to salary and he just remember that table name has ‘SALARY’ in it but does not know exact name, then he would fire query on data dictionary view to get all the table names with ‘salary’. From the list of result tables, he can identify which table that he was searching. The description of the tables also helps him in this case.
SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE ‘%SALARY%’;
- When any DDL is fired on the database objects, it searches the data dictionary for the object. Then underlying DBMS software modifies the object based on the DDL. This changes are reflected in the base tables and hence in the user views.
- It controls the access to different objects in the database by means of its views. It allows only the users who have proper rights to see the tables and views in the database. It masks other tables and views to which user does not have access.
- It provides the quick report on the data and the resources that the objects are using and hence making the data management easy.
Advantage and Disadvantages of Data Dictionary
Advantage of Data Dictionary
There is lot of advantages of data dictionary. Some of main points are
- It gives the well structured and clear information about the database. One can analyze the requirement, any redundancy like duplicate columns, tables etc. Since it provides a good documentation on each object, it helps to understand the requirement and design to the great extent.
- It is very helpful for the administrator or any new DBA to understand the database. Since it has all the information about the database, DBA can easily able to track any chaos in the database.
- Since database is a very huge, and will have lots of tables, views, constraints, indexes etc, it will be difficult for anyone to remember. Data dictionary helps user by providing all the details in it.
Disadvantages of Data Dictionary
- Creating a new data dictionary is a very big task. It will take years to create one.
- It should be well designed in advance to take all the advantages of it. Otherwise, it will create problems throughout its life.
- The cost of data dictionary will be bit high as it includes its initial build and hardware charges as well as cost of maintenance.
- Non technical users will not understand what the columns in the data dictionary views are. It meant only for technical users.