Oracle SQL*Plus: The Definitive Guide

Oracle’s Data Dictionary Views

Oracle has to keep track of all the tables, views, constraints, indexes, triggers, and other objects you create. In order to do that, Oracle needs a place to store the information. This repository of information about your database is referred to as the data dictionary. Whenever you create a new object, such as a table, Oracle stores all the information about that object in the data dictionary. Modify the object, and Oracle modifies the data dictionary. It follows, then, that if you want to know anything about your database, the data dictionary is the place to go.

The data dictionary is a set of tables owned by the user SYS. The structure of these tables ends up being fairly complex, and much of the information is not stored in a very user-friendly form. You probably do not want to query these tables directly, and unless you have been given access to log in as user SYS, you won’t be able to see them anyway. To help you out, Oracle provides a set of data dictionary views. These views have names that are easy to remember. The column names used in the views are also easy to remember and use a consistent naming convention. There are data dictionary views for each different type of schema object, and they present information in an easy-to-understand form. For example, if you are looking at a date column, the DBA_TAB_COLUMNS view will tell you it is of type `DATE’. The underlying data dictionary table, which happens to be SYS.COL$, will simply tell you ...