Oracle Data Dictionary

We have already learned that the data dictionary is the place where Oracle stores metadata, or information about how the data is organized. That is, the dictionary stores information about various database objects such as tables, views, constraints, triggers, stored procedures, etc. that constitute the database. The Oracle data dictionary is also called the system catalogue and contains all configuration related information about the DBMS itself and may be used to obtain parameters related to database and user monitoring. The dictionary contains the following information about each table:

• Table name
• Creation date and time
• Date and time of last access
• The name of each column
• The sort of data (string, integer, date etc.) that each column contains and restraints on values.
• The table owner
• Users who have permission to read from or write to the table
• Location and name of corresponding datafiles.

Dictionary Structure

The Oracle data dictionary consists of a set of read-only tables and views. The dictionary is organized hierarchically in a tree structure. The main dictionary table is the root of the tree. This table has two columns - table_name and comments. The comments field describes the purpose of the dictionary table. Running the following sort of command within SQL Plus will return information about the corresponding dictionary table. Conventionally, all dictionary data is stored in uppercase.

Screenshot 4f: View Comments

Commands like the above may be written for any tables in the data dictionary. The dictionary structure is also accessible through the SYS -> VIEWS link in the OEM console. These are quite bewildering as there are hundreds of views here. It is better to know what you are trying to find. The second level of the dictionary, as you may have gathered from Fig. 4a, forks into four branches. The "User" tables describe objects owned by the current user and may not be viewed by other users. The "All" describe objects common to all users and may be viewed by any user. The "DBA" tables contain information about the DBMS that only the DBA may access. The tables with a V$ prefix contain important information about the internal state of the DBMS, performance related parameters etc. and are used by DBAs to monitor the system. These tables provide Dynamic Performance Views used to monitor the database.

Screenshot 4g: Data Dictionary Tables Through OEM Console