System Catalog

Data Dictionary and System Catalog

As we create lots of tables, views, constraints, indexes etc in a database, it is difficult for any developer or user to remember all of them. If there is any change request to the table structure or if there is any issue with the existing design then the developer might not remember which table is for which purpose or which table contains which data, what is the domain of the columns or if there is any specific meaning to the value that has been entered to the column etc. Also, if there is any new designer handling the database, then it is necessary for him to understand the system thoroughly.

Hence, every database stores every information about its objects. These informations can be it structure, definition, purpose, storage, number of columns and records, dependencies, access rights, owner etc. Basically these are the useful information about the data in the database. They are also called as metadata. These metadata are also stored as rows and columns of a table. Collection of these metadata is stored in the data dictionary or system catalog. Usually, system catalogs are accessed by the DBMS to perform various transactions and data dictionary has the user accessible views that are accessed by the developers/ designers/ users.

It is a database about the database objects. It can exist in the same database or it can be completely a separate database. If it is a separate database, then there should be a process to update the table in system catalog as when object of the database changes. It should be in sync with database objects. This helps the developer/ user to get the quick information about the database objects.

System catalog also contains various tables and views. Since it contains the information about the database objects, users are not allowed to modify the details in it. It is automatically inserted/ updated/ deleted by the RDBMS. User has no access rights on them. But users/developer will need to see the details in them to understand the objects. Sometimes developer wants to know the exact column name and the table in which the records that he is fetching exist. In these cases, he has to fetch in the data dictionary or system catalog. For this purpose views on tables in the data dictionary are created and given SELECT rights to the users. Data dictionary tables are created in the SYS schema and do not have any access privileges for the user. Only the RDBMS access it. The views on these tables are created in SYSTEM schema and users are given only SELCT rights. They cannot modify these views.

Depending on the access rights of the user we have 3 types of views – USER, ALL and DBA.

The user view lists only those tables and views which are created by the current user/schema. It does not list the tables and views of other schemas nor the ones to which it has access. It displays only those objects that are created by it.

The ALL view lists all the tables and views that are owned by the current user as well as those tables and views to which it has access. That means it lists the tables and views that are created by it, and those tables and views of other user/schema to which it has access.

DBA view will have access to all the tables and views of all the users/schema. But these views will be accessible by only those who have DBA privileges.

One of the view – DICTIONARY in the SYSTEM schema provides the details of all the objects in the database system. It has two columns – TABLE_NAME and COMMENTS. TABLE_NAME gives the name of the objects in the database and COMMENTS gives details about each of them.

SELECT * FROM DICTIONARY;

There are lots of views in SYSTEM schema with table details, column details, constraints, index, etc. Each view has its set of columns and details.

Some of the views of data dictionary are listed below.