The data dictionary
A database is designed to store information in a simple, readable format that is easy to query and maintain. It is only sensible, therefore, that the information about the tables and other objects in the database is stored in the same way. Because the information about schema objects constitutes a definitive reference for other database operations, it is known as the data dictionary. You have already seen this term used in a different sense in the context of the database design process, and the two uses of the term have no particular relationship with each other. You could say though, that the design data dictionary and the set of tables that store the schema details perform the same function at different stages in the database lifecycle: they both define the structure of the data model. You may also see the term system catalog used instead of data dictionary to refer to the schema tables.
The data dictionary tables are only updated by the DBMS where a DDL statement is processed, and they are not directly accessible to ordinary users. However, Oracle and other database platforms provide a series of views which allow users access to certain parts of the data dictionary. Although there are many such views, and a lot of the information they provide is about low-level storage and other administrative issues, there are some particularly useful ones that you should know about.
Oracle maintains a set of data dictionary views that start with the prefix USER_ to provide information about a user's own objects. The full list can be found in the Oracle documentation along with two other sets of views. Those starting with ALL_ provide information on all objects that the user has access to, while those starting DBA_ list all objects for all users. The DBA_ views may not be visible to ordinary users. Some particularly useful data dictionary objects are:
- USER_TABLES: Provides details about a user's own tables
- USER_CONSTRAINTS: Provides details about all constraints currently defined on the user's objects. This can be useful when checking that all constraints have been correctly created, or when you need to find the name of a constraint which needs to be dropped or altered
- USER_TRIGGERS: Provides details about triggers, including the trigger body
- ALL_USERS: Lists the user accounts in the database
As well as the views mentioned above that conform to the standard naming conventions, Oracle also provides another called TAB for ease of use. TAB lists the tables and views owner by the current user, and so provides a quick reminder of their names. This can be very useful when working with the command line, and can be used in conjunction with the DESCRIBE command which shows the columns in a particular table. The graphic below show the use of these two statements using the HR schema:
USER_TABLES
Tables