In your life as a DBA, many of the data dictionary views will become some of your most useful tools. They can tell you, at a glance, what is going on in your database, and who is doing what. You will likely (and you really should) create a library of useful and much-used and relied-upon scripts to provide information out of the data dictionary. This way, you can easily get the information that you need, and you don't have to think and rethink, check and recheck the contents of the views that you are accessing to get the information you are after. Table 5.2 provides many of the most commonly used data dictionary views, their purpose, and their meanings. In a simple, generic database, with no additional products installed, there are more than 1,200 entries in the DICTIONARY view, so an exhaustive list should be generated on your own to familiarize yourself with the kinds of information that you can glean from the data dictionary. Although Table 5.2 lists the DBA views because this exam is geared to database administrators, there are usually equivalent USER or ALL views accessible by anyone logging in to the database. Table 5.2. DICTIONARY View EntriesPurpose | View | Description |
---|
Provide general overview | DICTIONARY | Provides descriptions of data dictionary tables and views | | DICT_COLUMNS | Provides descriptions of columns in the data dictionary tables and views | Provide information relevant about user objects, such as tables, constraints, columns, and indexes | DBA_TABLES | Provides information on tables in the database | | DBA_OBJECTS | Provides information on stored objects in the database such as the owner, the object name and type, and often most relevant, its status (VALID or INVALID) | | DBA_LOBS | Provides description of all LOBs contained in all tables | | DBA_INDEXES | Provides description of all indexes in the database | | DBA_TAB_COLUMNS Also DBA_TAB_COLS | Provides information on columns of user tables, views, and clusters | | DBA_CONSTRAINTS | Provides information on all constraints in the database | Provide information about users, privileges, and roles | DBA_USERS | Provides information about all users in the database | | DBA_SYS_PRIVS | Provides information on all system privileges that have been granted to either users or to roles in the database | | DBA_ROLES | Provides information about all roles in the database | Space allocation for database objects | DBA_EXTENTS | Provides information concerning extents | | DBA_SEGMENTS | Provides information concerning segments | | DBA_FREE_SPACE | Provides information concerning free space | Provide information about database structures | DBA_ROLLBACK_SEGS | Provides description of all rollback segments | | DBA_DATA_FILES | Provides information about all database data files | | DBA_TABLESPACES | Provides a description of all tablespaces | Provide auditing information in the database | DBA_AUDIT_EXISTS | Provides a list of audit trail entries that have been produced by AUDIT EXISTS or AUDIT NOT EXISTS | | DBA_AUDIT_OBJECTS | Provides audit trail records for statements that concern objects such as tables, clusters, views, indexes, sequences, public database links, synonyms, and so on | Provide information about currently running processes | V$ views | Provides information about the current state of connections, sessions, SQL, and other active information in the database |
This is by no means an exhaustive list, and you should acquaint yourself with its components. The data dictionary is one of your most powerful tools as a DBA. |