What is the data dictionary?

The scope of this article is to clarify what exactly is the data dictionary. Probably all of you have heard of it, and you know it’s a bunch of metadata that Oracle uses to work, but if you want to know more about this Data Dictionary, then I hope this article teaches you something new.

The data dictionary is a repository of metadata (Information about information), about all the information inside the database. This repository is owned by SYS, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace (in Oracle 10+).

The data dictionary is composed of tables and views. Some of these tables are inside of the Oracle kernel, so you would never work directly with them unless you are working for Oracle support or performing a disaster recovery scenario. But instead you can access to the views in order to know the “information about the information”.

For example, a possible usage of this data dictionary would be to know all the tables owned by a single user, or the list or relationships between all the tables of the database.

The main view of the data dictionary is the view DICT (or DICTIONARY):

SQL> DESC DICT
Nombre                                    ?Nulo?   Tipo
—————————————– ——– —————————-
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

Through the DICT view, you can access to all the data dictionary views that could provide you the information that you need.

For example, if you are looking for information related to db_links, but you don’t know where to look for, then query the DICT view:

SQL>  SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE ‘%LINK%’;

TABLE_NAME
——————————
USER_DB_LINKS
ALL_DB_LINKS
DBA_DB_LINKS
V$DBLINK
GV$DBLINK

Now, you have just to query one of these views to find the data you are looking for. GV$ views are very useful when you are working with RAC, and V$ views are instance related.

Remember that the data dictionary provides critical information of the database, and it should be restricted to users. However, if a user really needs to query the data dictionary, you can use the following sentence:

GRANT SELECT_CATALOG_ROLE TO user_name;