Table of Contents
Introduction
At this point, everyone would have an idea of what is data dictionary. It is a dictionary about the data that we store in the database. It contains all the information about the data objects. It is like storing all up-to-date information about the objects like tables, columns, index, constraints, functions etc. Why do we need all these information? It makes us easily identify access and understand the factors about the object. One can imagine data dictionary as storing information about house like house name, address, how many live in the house, who is the eldest/youngest person, responsibilities of each member in the house etc. or a personal details of an employee in the company.
In the case of a table, data dictionary provides information about
- Its name
- Security information like who is the owner of the table, when was it created, and when it was last accessed.
- Physical information like where is the data stored for this table
- Structural information like its attribute names and its datatypes, constraints and indexes.
Below is the sample data dictionary view of the tables. (Only few of the columns are shown below to get an idea of data dictionary). Since it is from USER view, it will list only those tables which are created by current user. Hence no owner column is displayed below.
From above example it is clear that any data dictionary would contain
- The definitions of all database objects like tables, views, constraints, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers etc
- It stores the information about how much space is allocated for each object and how much space has been used by them
- Any default values that a column can have are stored
- Database user names – schemas
- Access rights for schemas on each of the objects
- Last updated and last accessed information about the object
- Any other database information
All these informations are stored in the form of tables in the data dictionary.
Types of Data Dictionary
There are two types of data dictionary – Active and Passive.
Active Data Dictionary
Any changes to the database object structure via DDLs will have to be reflected in the data dictionary. But updating the data dictionary tables for the changes are responsibility of database in which the data dictionary exists. If the data dictionary is created in the same database, then the DBMS software will automatically update the data dictionary. Hence there will not be any mismatch between the actual structure and the data dictionary details. Such data dictionary is called active data dictionary.
Passive Data Dictionary
In some of the databases, data dictionary is created separately from the current database as entirely new database to store only data dictionary informations. Sometimes it is stored as xml, excels or in any other file format. In such case, an effort is required to keep data dictionary in sync with the database objects. This kind of data dictionary is called passive data dictionary. In this case, there is a chance of mismatch with the database objects and the data dictionary. This kind of DD has to be handled with utmost care.
Note: – User can change the structure of database objects by using DDLs. But users can not change the structure/content of data dictionary tables/views. All the data dictionary tables/views are controlled and managed by DBMS. Users do not have any modification rights on them.