Oracles Data Dictionary Views

Oracle s Data Dictionary Views

Oracle has to keep track of all the tables, views, constraints, indexes, triggers, and other objects you create. 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.

10.2.1 What Is the Data Dictionary?

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 isn't stored in a 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. Data dictionary views exist 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 tell you the type is 12.

Oracle has a large number of data dictionary views. This chapter concentrates on the views used to return information about the structure of a table, its constraints, indexes, columns , triggers, and security. This is the most common type of information needed by application developers and other database users. I encourage you to dig deeper. If you want, or need, to know more, then the Oracle Database Reference manual is a good place to start. Look for the section titled " Static Data Dictionary Views ," which gives a definitive description of all the views available, and their columns. Another, perhaps handier reference is Dave Kreines's Oracle Data Dictionary Pocket Reference (O'Reilly).

10.2.2 The View Types: user, all, and dba

You need to be aware of three different types of data dictionary views. These control the scope of the information you can look at:

user

The user views show you information only about objects that you own. There is a user_tables view, for example, that lists only your tables.

all

The all views show you information about all objects you are able to access. Anything you own is included in an all view, as well as anything owned by other users but to which you have been granted access.

dba

The dba views show you information about all objects. Usually, only DBAs have access to these views, and they can be considered a superset of the all views. dba_tables , for example, will list every single table that exists.

Generally, for any given object type, one view of each type will exist. It's up to you to choose the view you want to look at. Table 10-1 shows how this works in terms of the views discussed in this chapter.

Table 10-1. Correspondence between user, all, and dba views

As you delve more deeply into Oracle's data dictionary, you will occasionally find instances when corresponding views don't exist in all three categories. When a view is omitted, it's for security reasons, because it doesn't make sense in the context of a particular object, or because it would be redundant. The dba_scheduler_windows and all_scheduler_windows (new in Oracle Database 10 g ) views provide a good example of this. DBMS_SCHEDULER windows aren't "owned" by users, so a user view doesn't apply in that context.

Which view should you use? The user views limit you to seeing information about objects that you own. If I'm working interactively, I'll frequently use the user views to save myself some typing because I don't need to enter a WHERE clause to restrict the results to my own objects. When writing scripts, I want to use the all views to make the scripts more flexible. It's common, for example, to need to see the definition for a table owned by another user. The all views allow this. I save the DBA views for DBA- related tasks .

The following sections show you how to get information about various types of schema objects. First, I'll show how to list the tables you own and how to look at the column definitions for those tables. Next, you will see how to look at the constraints, indexes, triggers, synonyms, and security for a table. You'll learn how to leverage the data dictionary to automate DBA tasks. Finally, I'll hand you the data dictionary's master key .