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 the type is 12.
Oracle has a large number of data dictionary views. This chapter concentrates only on the views that are 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 Server Reference manual would be a good place to start. The Oracle Server Reference manual contains a chapter titled Static Data Dictionary Views, which gives a comprehensive list of all the views available.
There are three different types of data dictionary views you need to be aware of. These control the scope of the information you can look at. The three view types are:
- USER
The USER views show you information only about objects that you own. There is a USER_TABLES view, for example, that will list only tables you own.
- ALL
The ALL views show you information about all objects you are able to access. Anything you own will be 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 — period. Usually only database administrators will have access to these views, and they could be considered a superset of the ALL views. DBA_TABLES, for example, will list every single table that exists. The typical way to gain access to the DBA views is to be granted the DBA role. However, the SELECT ANY TABLE privilege will also let you see them.
Generally speaking, for any given object type, one view of each type will exist. It’s up to you to choose the one you want to look at. Table 6.1 shows how this works in terms of the views discussed in this chapter.
Table 6-1. Correspondence Between USER, ALL, and DBA Views
|
USER View Name |
ALL View Name |
DBA View Name |
|---|---|---|
|
USER_TABLES |
ALL_TABLES |
DBA_TABLES |
|
USER_TAB_COLUMNS |
ALL_TAB_COLUMNS |
DBA_TAB_COLUMNS |
|
USER_CONSTRAINTS |
ALL_CONSTRAINTS |
DBA_CONSTRAINTS |
|
USER_CONS_COLUMNS |
ALL_CONS_COLUMNS |
DBA_CONS_COLUMNS |
|
USER_INDEXES |
ALL_INDEXES |
DBA_INDEXES |
|
USER_IND_COLUMNS |
ALL_IND_COLUMNS |
DBA_IND_COLUMNS |
|
USER_SYNONYMS |
ALL_SYNONYMS |
DBA_SYNONYMS |
|
USER_TRIGGERS |
ALL_TRIGGERS |
DBA_TRIGGERS |
|
USER_TAB_PRIVS |
ALL_TAB_PRIVS |
DBA_TAB_PRIVS |
As you delve more deeply into Oracle’s data dictionary, you will occasionally find instances when corresponding views do not exist in all three categories. When a view is omitted it’s either for security reasons, because it doesn’t make sense in the context of a particular object, or because it would be redundant. The USER_JOBS and DBA_JOBS views provide a good example of this. A user can own a job, hence the USER_JOBS view. The database administrator needs to see everything, hence the DBA_JOBS view. There is no ALL_JOBS view, because there is no mechanism to grant another user access to your jobs. ALL_JOBS would be redundant with USER_JOBS, because the only jobs you can ever see are your own anyway.
Tip
When you write scripts, avoid the DBA views if at all possible. Using the DBA views limits your ability to share your scripts, because most users don’t have access to them.
Which view should you use? The USER views limit you to seeing information only 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 objects. When writing scripts, I try to use the ALL views in order to make the scripts more flexible. It’s not unusual, 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 database administration-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. Finally, you will see how to write one script that will give you a pretty complete dump of all this information just by issuing one command.
A number of data dictionary views are referenced by the examples and scripts shown in this chapter. For your convenience, they are all shown together in this section. As you scan through these, it’s not necessary to understand every single column of each view. Many of the view columns, particularly in ALL_TABLES and ALL_INDEXES, relate to the physical implementation of the objects, and aren’t discussed in this chapter. The more experienced you become with Oracle, the more you will come to understand the information these columns contain.
The ALL_TABLES view contains one record for every table to which you have access. Table 6.2 lists the columns in this view and explains what information they contain. Many of the columns contain information about the physical implementation of the table, and if you are not the DBA, you may not be too interested in them. Some columns, beginning with NUM_ROWS and going through NUM_FREELIST_BLOCKS, are set only when you use the ANALYZE TABLE command to compute statistics for the table.
Table 6-2. Columns in the ALL_Tables View
|
Column Name |
Contents |
|---|---|
|
Username of the table’s owner. | |
|
Name of the table. | |
|
Name of the tablespace where the table is stored. This is a foreign key to the DBA_TABLESPACES view. | |
|
A cluster name, if the table belongs to a cluster. This is a foreign key to the ALL_CLUSTERS view. | |
|
An index-organized table name. | |
|
Minimum percentage of free space in a block. When free space drops to this level, Oracle stops adding new rows to the block. | |
|
Minimum percentage of used space in a block. When used space drops to this level, Oracle will again add new rows to the block. | |
|
Initial number of transactions that can concurrently touch this block. | |
|
Maximum number of transactions that can concurrently touch this block. | |
|
The size, in bytes, of the initial extent for the table. This is how much space is allocated when the table is first created. | |
|
The size, in bytes, of the next extent for the table. The next time the table needs to be expanded, this is how much space will be allocated. | |
|
The minimum number of extents to be allocated for the table. This value affects the amount of space that is initially allocated for the table. See INITIAL_EXTENT and NEXT_EXTENT. | |
|
The maximum number of extents allowed for the table. When these are used up, you won’t be able to insert any more data into the table. | |
|
A percentage that controls growth of the NEXT_EXTENT value. Each time an extent is allocated, NEXT_EXTENT is recomputed to be NEXT_EXTENT + (NEXT_EXTENT * (PCT_INCREASE/100)). This value is then rounded up to the blocksize, or a multiple of the blocksize. | |
|
The number of freelists allocated to the table. | |
|
The number of freelist groups allocated to the table. | |
|
The logging attribute for the table. | |
|
A Y/N flag that indicates whether or not the table has been backed up since the last change. | |
|
The number of rows in the table.[a] | |
|
The number of data blocks used by the table.1 | |
|
The number of data blocks allocated to the table that have never been used.1 | |
|
The average amount of free space in all blocks allocated to this table. This value is in bytes. | |
|
The number of rows in the table that have had to be chained across more than one block, possibly because the row has been expanded. This also includes rows that have been moved entirely out of the block in which they were originally created. | |
|
The average length, in bytes, of the rows in the table. | |
|
The average freespace in all table blocks that are currently in the freelist. | |
|
The number of blocks allocated to the table that still contain space for more rows. | |
|
The number of threads per instance for scanning the table. | |
|
The number of instances allowed to scan the table at one time. | |
|
A Y/N flag indicating whether or not the table should be cached in the buffer. | |
|
Either “ENABLED” or “DISABLED”, indicating whether or not users are allowed to lock the entire table. | |
|
The sample size used when analyzing the table and computing statistics. | |
|
The date on which the table was most recently analyzed. | |
|
Either “YES” or “NO”, indicating whether or not the table is partitioned. | |
|
Indicates whether or not the table is an index-only table. A null value indicates a normal table. A value of “IOT” or “IOT_OVERFLOW” is used for index-only tables. | |
|
Either “Y” or “N”, indicating whether or not the table is a temporary table. | |
|
Either “YES” or “NO”, indicating whether or not the table is a nested table. | |
|
The name of the default buffer pool for the table. | |
[a] Not maintained automatically by Oracle. This column is set when you use the ANALYZE TABLE command with the COMPUTE STATISTICS option. | |
The ALL_TAB_COLUMNS view contains one record for every column in a table. This is the view used by the DESCRIBE command to get column names, lengths, and datatypes. Table 6.3 lists the columns in this view and explains the information they contain.
Table 6-3. Columns in the ALL_TAB_COLUMNS View
|
Column Name |
Contents |
|---|---|
|
Username of the table’s owner. | |
|
Name of the table. | |
|
The column name. | |
|
The column’s datatype (e.g., VARCHAR2, NUMBER, etc). | |
|
The datatype modifier for the column. | |
|
DATA_TYPE_OWNER |
The datatype owner. This is used in Oracle8 and above when you create a column based on an object type owned by another user. |
|
The length of the column in bytes. | |
|
The decimal precision, or the number of significant digits, the column will hold, if the column is type NUMBER. If the column is type FLOAT, then this is the binary precision, or the number of significant bits. For all other datatypes, this will be NULL. | |
|
The number of digits allowed to the right of the decimal point in a NUMBER column. Remember, NUMBER columns are defined as NUMBER (precision, scale). | |
|
Y = the column may contain null values. N = the column may not contain null values. This may be because of a NOT NULL constraint, or because the column is part of the table’s primary key. | |
|
The sequence number, indicating the column’s position within each record. When you create a table, the first column in the list will have a COLUMN_ID of 1. The next column will have a COLUMN_ID of 2, and so on. | |
|
The length of the default value for the column, if there is a default value. | |
|
The default value for the column. | |
|
The number of distinct values in the column.[a] | |
|
The lowest value, up to the first 32 bytes of that value, in the column.2 | |
|
The highest value, up to the first 32 bytes of that value, in the column.2 | |
|
The density of the column.2 | |
|
The number of records with a null value for this column.[b] | |
|
The number of buckets in the column’s histogram.3 | |
|
The date on which the data in the column was last analyzed with an ANALYZE TABLE COMPUTE STATISTICS command.3 | |
|
The number of rows that were sampled when statistics were last computed for this column.3 | |
|
For character columns, this indicates which character set is stored by the column. This value will be either NCHAR_CS or CHAR_CS. NCHAR_CS indicates that data in the column is stored using the national character set. CHAR_CS indicates that the database character set is used. | |
[a] Not maintained automatically by Oracle; beginning with Oracle8, you should use the ALL_TAB_COL_STATISTICS view to retrieve this value. This column is set when you use the ANALYZE TABLE command with the COMPUTE STATISTICS option. [b] Not maintained automatically by Oracle. This column is set when you use the ANALYZE TABLE command with the COMPUTE STATISTICS option. | |
The ALL_CONSTRAINS view returns one row for each constraint you can see. Oracle will let you see all constraints on tables you own, as well as all constraints on tables to which you have been granted any type of access. Table 6.4 lists the columns in this view.
Table 6-4. Columns in the ALL_TAB_COLUMNS View
|
Column Name |
Contents |
|---|---|
|
The owner of the constraint. This is usually the same as the owner of the table on which the constraint is defined. | |
|
The name of the constraint. | |
|
Indicates the type of constraint,. Will be one of these values: P = PRIMARY KEY R = FOREIGN KEY C = CHECK U = UNIQUE KEY | |
|
The table on which the constraint is defined. | |
|
If the constraint is a check constraint, this is the condition that must be satisfied for each row stored in the table. For other constraint types, this will be null. | |
|
For foreign key constraints, this is the owner of the related primary key or unique constraint. | |
|
For foreign key constraints, this is the related primary key or unique constraint. | |
|
This has meaning only for foreign key constraints, and has these two values: CASCADE: Deleting a record from the parent table will cause matching records in this table to be deleted automatically. NO ACTION: Child records are not automatically deleted. You will not be able to delete a parent unless you first delete the children. | |
|
Will be either ENABLED or DISABLED, depending on whether or not the constraint is currently being enforced. | |
|
Will be either DEFERRABLE or NOT DEFERRABLE. | |
|
Will be either DEFERRED or IMMEDIATE, depending on whether not the constraint is deferred by default. | |
|
Will be either VALIDATED or NOT VALIDATED, depending on whether or not all rows in the table have been validated against the constraint. | |
|
Will be either GENERATED NAME or USER NAME depending on whether the constraint name was system-generated or specified by the user who created it. | |
|
Usually this is null. If this is “BAD”, you have used a date constant that does not include the century, and you need to recreate the constraint prior to the year 2000. | |
|
The date when the constraint was most recently changed. Since the only change you can make is to enable or disable a constraint, this ends up being when that was most recently done. |
The ALL_CONS_COLUMNS view contains one row for each column specified in a primary key, foreign key, or unique constraint. Table 6.5 lists the columns in this view.
Table 6-5. Columns in the ALL_CONS_COLUMNS View
|
Column Name |
Contents |
|---|---|
|
The owner of the constraint, which is the same as the owner of the table on which the constraint is defined. | |
|
The name of the constraint. | |
|
The table on which the constraint is defined. | |
|
The name of a column that makes up the constraint. | |
|
The position of the column in the constraint definition. |
The ALL_INDEXES view returns one record for each index you can see. You should be able to see indexes for any table to which you have been granted access. The information in ALL_INDEXES pertains to the index as a whole. To get the list of columns in an index, you need to look at the ALL_IND_COLUMNS view. Table 6.6 describes the columns in ALL_INDEXES.
Table 6-6. Columns in the ALL_INDEXES View
|
Column Name |
Contents |
|---|---|
|
The username of the index’s owner, which may not be the same as the owner of the underlying table. | |
|
The index name. | |
|
The index type, one of the following values: NORMAL, BITMAP, CLUSTER, IOT-TOP, IOT-NESTED, SECONDARY, ANSI, or LOB. | |
|
The username of the table’s owner. | |
|
The name of the table on which the index was created. | |
|
The type of table which is indexed. | |
|
Indicates whether or not the index is a unique index. Values are UNIQUE, NONUNIQUE, or UNDEFINED. | |
|
The name of the tablespace in which the index is stored. | |
|
The initial number of transactions. | |
|
The maximum number of transactions. | |
|
The size, in bytes, of the first extent allocated for the index. | |
|
The size, in bytes, of the next extent that will be allocated for the index. | |
|
The minimum number of extents for the index. This number of extents is allocated when the index is first created. | |
|
The maximum number of extents allowed for the index. When these fill up, any attempts to index new rows will result in an error. | |
|
Each time a new extent is allocated, the NEXT_EXTENT value is increased by this percentage. | |
|
The percentage of block space allowed for each index entry. | |
|
Applies to indexes on index-organized table, and is the column ID number of the last column to be included in the top-level index blocks. | |
|
The number of freelists allocated to this index. | |
|
The number of freelist groups allocated to this index. | |
|
Minimum percentage of free space in a block. When free space drops to this level, Oracle stops adding new rows to the block. | |
|
The logging attribute for the index. | |
|
For B-Tree indexes, this is the number of different levels between the root (top) and the leaf (bottom) blocks.[a] | |
|
The number of leaf blocks contained in the index.4 | |
|
The number of distinct index values. With a UNIQUE index, every value is a distinct value.4 | |
|
Each distinct indexed value appears in one or more leaf blocks. This is the average of the number of leaf blocks for each entry. For a UNIQUE index, this will always be 1.4 | |
|
Each distinct index entry references one or more data blocks in the table. This is the average number of data blocks referenced by each entry.4 | |
|
This indicates how well-ordered the table is in terms of the index. This value can range from the number of blocks in the table to the number of rows in the table. The more this value approaches the number of blocks in the table, the more closely the order of the rows in the table match the order in which they are indexed. As the rows are more randomized, this value will approach the row count for the table.4 | |
|
The status of the index; it has one of these values: DIRECT LOAD or VALID. A status of VALID indicates that the index is up to date and can be used. | |
|
The number of rows indexed by this index.4 | |
|
The sample size used when the index was last analyzed.4 | |
|
The date on which the index was last analyzed.4 | |
|
The number of threads used by each instance scanning the index. This applies only to partitioned indexes, and will be null if the index is not partitioned. | |
|
Similar to DEGREE, but refers to the number of instances that can simultaneously scan the index. If the index is not partitioned, this will be null. | |
|
A YES/NO flag indicating whether or not the index is a partitioned index. | |
|
A Y/N flag indicating whether or not the index is a temporary index. If the index is temporary, users will only see entries for rows they have inserted. | |
|
A Y/N flag indicating whether or not the index name was system-generated. A value of Y is used for system-generated names, which look like this: SYS_C001194. | |
|
The name of the default buffer pool to be used for the index. | |
[a] Not maintained automatically by Oracle. This column is set when you use the ANALYZE TABLE command with the COMPUTE STATISTICS option. | |
Indexes are made up of one or more columns. To find out what columns are included in an index, you need to use the ALL_IND_COLUMNS view. Table 6.7 describes this view.
Table 6-7. Columns in the ALL_IND_COLUMNS View
|
Column Name |
Contents |
|---|---|
|
The owner of the index. | |
|
The name of the index. | |
|
The owner of the table that is indexed. | |
|
The name of the table that is indexed. | |
|
The name of a column in the index. | |
|
The relative position of the column in the index. The first column will be number 1, the second column will be number 2, etc. | |
|
The number of bytes of the column that form part of the index entry. |
The ALL_TRIGGERS view returns one row for each trigger on tables you own or to which you have access. Table 6.8 describes the columns in this view.
Table 6-8. Columns in the ALL_TRIGGERS View
|
Column |
Contents |
|---|---|
|
The owner of the trigger. | |
|
The trigger name. | |
|
This indicates when the trigger fires and whether or not it fires for each row. The type will be one of these values: BEFORE EACH ROW, AFTER EACH ROW, BEFORE STATEMENT, or AFTER STATEMENT. | |
|
The type of statement that fires the trigger, causing it to be executed. This will be one or more of the following: INSERT, UPDATE, DELETE. | |
|
The owner of the table on which the trigger is defined. Usually this is the same as the trigger owner, but it doesn’t have to be. | |
|
The name of the table on which the trigger is defined. | |
|
Tells you what correlation names are used in the trigger to reference columns in the table. If the defaults were taken when the trigger was created, the value for this column will be “REFERENCING NEW AS NEW OLD AS OLD”. | |
|
The WHEN clause, if any, that was specified when the trigger was created. This will actually be the part of the WHEN clause between parentheses. Thus, if the CREATE TRIGGER statement specifies “WHEN (new.hire_date = sysdate)”, this column will return only the “new.hire_date = sysdate” portion. A WHEN clause can only be specified for a row-level trigger, and the trigger only fires if this condition is true. | |
|
This will be either ENABLED or DISABLED, and indicates whether or not the trigger will fire at all. | |
|
This contains part of the original statement that was used to create the trigger. Everything following “CREATE TRIGGER” up until the keyword “REFERENCING” is stored here. This includes the trigger name, the table name, the trigger type, the triggering event, and the list of update columns. Using this field provides a more convenient way to get this information than selecting all the other columns separately. This is especially true if you are trying to recreate the original CREATE TRIGGER statement. | |
|
This is a LONG column containing the PL/SQL code that makes up the body of the trigger. |
The ALL_TRIGGER_COLS view returns one row for each database column referenced or modified in triggers on tables you own or to which you have access. I rarely use this view, but if there is ever any doubt about whether or not a trigger uses a particular database column, this view can provide a quick answer. Table 6.9 describes the columns in this view.
Table 6-9. Columns in the ALL_TRIGGERCOLS View
|
Column |
Contents |
|---|---|
|
The owner of the trigger. | |
|
The trigger name. | |
|
The owner of the table on which the trigger is defined. Usually this is the same as the trigger owner, but it doesn’t have to be. | |
|
The name of the table on which the trigger is defined. | |
|
The name of a column in the table that is used within the trigger, either within the trigger’s specification, or within the PL/SQL block executed by the trigger. | |
|
Either YES or NO, depending on whether or not the firing of the trigger is dependent on this column being updated. This is only applicable to UPDATE triggers. A value of YES indicates that the column forms part of the column list following the keyword UPDATE in the CREATE TRIGGER statement. | |
|
This tells you how the column is used within the trigger. It will contain some combination of the following strings, separated by one space: NEW: The new version of the column is referenced. OLD: The old version of the column is referenced. IN: The column is referenced, but not updated. OUT: The column’s value is set, but never read. IN OUT: The column’s value is both referenced and updated. For example, a value of “NEW IN OUT” tells you that the trigger both sets the value of the column (OUT) and reads the value of the column (IN). It also tells you that it is the new version (NEW) of the column that is used. |
The ALL_SYNONYMS view returns one row for each synonym you own and one for each public synonym. Table 6.10 describes the columns in this view.
Table 6-10. Columns in the ALL_SYNONYMS View
|
Column |
Contents |
|---|---|
|
The owner of the synonym. The owner name will be “PUBLIC” for public synonyms, which are accessible by all database users. | |
|
The name of the synonym. | |
|
The name of the table’s owner. | |
|
The table name to which the synonym points. | |
|
If the synonym points to a table in another database instance, this is the database link name that points to that instance. |
The ALL_TAB_PRIVS view shows privileges granted to others on objects you own. It also shows privileges granted to you on objects owned by others. Table 6.11 describes this view.
Table 6-11. Columns in the ALL_TAB_PRIVS View
|
Column |
Contents |
|---|---|
|
The username of the person who granted the privilege. | |
|
The username or rolename to which access was granted. | |
|
The owner of the object. | |
|
The object’s name. | |
|
The privilege that was granted on the object (e.g., INSERT or DELETE). | |
|
This will be either YES or NO, depending on whether or not the privilege was granted with the administrative option. |
This view only comes into play when you have granted UPDATE privileges to a user, and when you have limited those update privileges to a specific set of columns. This view returns that list of updateable columns, and is described in Table 6.12.
Table 6-12. Columns in the ALL_COL_PRIVS_MADE View