data dictionary is a collection oftables and related views that enable you to see the inner workingsand structure of the Oracle database. By querying these tables andviews, you can obtain information about every object and every userof the database. For example, you can determine the amount of I/O toeach datafile, the values of the INIT.ORAparameters, and much more. All of the Oracle monitoring tools look atthe information available in the data dictionary and present it in aneasy-to-use format.
Traditionally, the data dictionary has consisted of a series of viewsowned by SYS. These views, known as static data dictionaryviews, present information contained in tablesthat are updated when Oracle processes a DDL statement. The SYStables and views, as well as a set of public synonyms for the views,are all created by the catalog.sql script. Inaddition, the installation of some Oracle features creates tables andviews in the SYSTEM schema. In general, tables and views owned bySYSTEM exist to support functionality provided by PL/SQL storedprocedures rather than fundamental Oracle functionality.
An additional set of views is composed of the dynamicperformance data dictionaryviews,commonly referred to as the V$ views(or, mistakenly, as the V$ tables). TheseV$ views are based on a set of internal memory structures maintainedby Oracle as virtual tables, which all begin with an "X$"prefix. Just as the static data dictionary views provide informationabout the database, the V$ views and the underlyingX$ tables provide information about theinstance.
Table 14.1 distinguishes between the types ofinformation you will find in the static data dictionary views and inthe dynamic performance data dictionary views.
| If You Need to Find Information About... | Use These Data Dictionary Views |
|---|---|
| Database objects | Static data dictionary views |
| Instance objects | Dynamic performance data dictionary views |
| Database data files | Static data dictionary views |
| Archive log files | Dynamic performance data dictionary views |
| Users allowed to access the database | Static data dictionary views |
| Users currently connected to the database | Dynamic performance data dictionary views |
The static data dictionary views haveexisted in their current format since Oracle Version 6. These areviews owned by SYS that are built upon tables owned by SYS and giveyou the ability to find information about database objects. Table 14.2 shows which static data dictionary viewsshould be used to find specific types of information.
Most of the data dictionary isconstructed in a matrix fashion. The first way to categorize datadictionary views is by the breadth of information they cover. Viewscan be divided into four groups:
Views that allow you to see objects you own. Most of these views begin with USER_.
Views that allow you to see objects that you own or that were granted to you. Most of these views begin with ALL_.
Views that allow you to see all objects in the database. These are primarily for use by the DBA. Most of these views begin with DBA_.
A handful of other views that provide information of general interest about the database.
The second way to categorize data dictionary views is by content.Many of the USER_, ALL_, and DBA_ views are grouped in families,according to how their view names end (e.g., TABLES, COLUMNS, and soon). Groups of views provide information about various topics,including:
Tables
Storage
Columns
Views
Objects
Networking objects
As this is a book for DBAs, in this chapter we'll concentrateon the DBA_ views and the other views of interest to DBAs. The ALL_views have the same structure as the DBA_ views. The USER_ views havethe same structure as the DBA_ views with the exception that they donot include the OWNER column.
This section summarizes the static data dictionary views you'llcommonly use. Views are divided into functional categories andarranged alphabetically by category.
These views provide information about themessage queues:
Lists the tables used to hold the queues defined as part of theAdvanced Queuing facility.
Lists the queues defined as part of the Advanced Queuing facility.
These views (and some tables)provide information about the status of auditing and the actual audittrail. For further information, see Chapter 7.
Lists the default auditing options in effect for new objects.
Lists the audit codes and descriptions.
Contains audit trail information generated by AUDIT EXISTS and AUDITNOEXISTS.
Contains audit trail information for object auditing.
Contains audit trail information for all connects and disconnectsfrom the instance.
Contains audit trail information for all audited statements.
Contains all audit trail information. The other DBA_AUDIT_ views aresubsets of this view.
Lists the valid SQL statements that can be specified for statementauditing.
Lists the valid system privileges that can be specified for systemprivilege auditing.
Lists the valid object audit options that can be specified for schemaobject auditing.
These views provide information aboutconstraints and columns included in the constraints:
Shows which columns are affected by each constraint.
Lists all constraints defined in the database.
These views provide informationabout the objects in the data dictionary:
Lists all tables, views, sequences, and synonyms in the database.
Lists dependencies between database objects. Used to determine whichobjects become invalid after other objects are altered or dropped.
Lists all objects in the database. Note that this name predates theObjects Option and is not restricted to objects created using theObjects Option.
These views provide information aboutindexes and indexed columns:
Lists all indexed columns.
Lists all indexes.
Contains information about the distribution of index keys within thetable. Populated for one index at a time by the ANALYZE INDEX ...VALIDATE STRUCTURE command.
Contains information about the structure of an index. Populated forone index at a time by the ANALYZE INDEX ... VALIDATE STRUCTUREcommand.
These views provide information about thejob queues managed by the Oracle built-in DBMS_ JOBS package. These jobqueues are used by the replication facilities and by OracleEnterprise Manager, but are available for use by any application.
These views provide information aboutlarge objects (LOBs):
These views provide information about thecurrent status of locks in the database:
Lists all sessions holding locks for whose release others are waiting.
Lists all KGL (library cache) locks in the database.
Contains internal information for each lock defined in DBA_LOCKS.
Lists all sessions that are waiting on a lock held by another session.
Shows which locks the current user has allocated.
These views provide information about thestatus of Net8 and remote databases. See Chapter 5, for more details on the use andimplementation of Net8.
Contains information about the commit point for distributedtransactions listed in DBA_2PC_PENDING.
Lists information about distributed transactions requiring recovery.
Lists all database links.
Contains further information used by XA for distributed transactionslisted in DBA_2PC_PENDING.
Shows the value of the global name. Can be used to determine whichdatabase the application is connected to.
These views provideinformation relating to objects created using Oracle's ObjectsOption:
Lists collection types created.
Lists all parameters for methods defined in DBA_TYPE_METHODS.
Lists all method results for methods defined in DBA_TYPE_METHODS.
Lists all nested tables created using features from the ObjectsOption.
Lists all tables created using features from the Objects Option.
Lists the REF columns and attributes for objects.
Lists attributes of all types.
Lists methods created to support each type defined in DBA_TYPES.
Lists all types created.
These views provide information aboutpartitioned tables and indexes:
Lists all index partitions. There is one row for each index partition.
Contains distribution information about partitioned columns that havebeen analyzed; comparable to DBA_TAB_COL_STATISTICS for partitionedtables.
Contains information about histograms created on individualpartitions.
Lists all partitioned indexes. There is one row for each partitionedindex.
Lists all partitioned tables. There is one row for each partitionedtable.
Lists all table partitions. There is one row for each table partition.
These views provide information about PL/SQL functions, procedures,packages, and triggers:
Lists all valid arguments for stored procedures and functions.
Shows all errors from compiling objects.
Lists the external libraries that can be called from PL/SQL packages,procedures, and functions.
Shows the size of the compiled code for each PL/SQL package,procedure, function, and trigger.
Shows PL/SQL source for packages, procedures, and functions.
Lists columns that are referenced in triggers.
Shows PL/SQL code for database triggers.
Lists dependencies using only object numbers.
These views provide informationabout users and grants. Please refer to Chapter 6,for more details on security.
Lists all column grants made in the database.
Lists all defined profiles.
Lists all roles.
Shows which system privileges have been assigned to which users.
Shows all object privileges. Includes not only tables but also views,sequences, packages, procedures, and functions.
Shows the assigned cost of each resource for composite limits.
Lists roles granted to other roles. A subset of DBA_ROLE_PRIVS.
Lists system privileges granted to roles. A subset of DBA_SYS_PRIVS.
Lists table grants granted to roles. A subset of DBA_TAB_PRIVS.
Shows which system privileges are active for the current session.
Shows the password limits in effect for the current session. There isno corresponding DBA_PASSWORD_LIMITS.
This view provides information about sequences:
These views provideinformation about the current status of the database:
Shows the National Language Support (NLS) parameters in effect at thedatabase level.
Shows the current release level of all installed Oracle options.
These views provide information aboutinternal storage in the database, including datafiles, tablespaces,free extents, used extents, and segments:
Lists all data files in use by the database.
Lists every allocated extent for every segment.
Lists every free extent. With DBA_EXTENTS, should account for allstorage in DBA_DATA_FILES.
Lists every extent that is at the start of a block of free extents.
Lists all rollback segments.
Lists all segments.
Lists all tablespaces.
Shows the granted quota and used storage in tablespaces by user.
This view provides information about synonyms:
These views provide information abouttables, clusters, and views:
Lists all object and relational tables.
Lists all cluster keys.
Lists the hash values used for the optional cluster hash indexes.
Lists all clusters in the database.
Shows comments on all table and view columns.
Contains column information about analyzed columns. This is a subsetof the information available in DBA_TAB_COLUMNS.
Shows all relational tables.
Shows all views.
These other views and tables are used by individual users:
Populated by the ANALYZE TABLE command to show all chained rows in atable. Created using the utlchain.sql script.
Contains a list of all rows that have a constraint violation.Populated when attempting to create or enable a constraint. Createdusing the utlexcpt.sql script.
Used by the EXPLAIN_PLAN process to show theexecution plan for a SQL statement. Created using theutlxplan.sql script.
The following views show important information about the structure ofthe database, but are normally not referenced by DBAs directly. Theyare listed here for completeness.
These views provide information used by Oracle'sadvancedreplication facilities. Oracle currently recommends using theReplication Manager to obtain the information in these views.
These views provide information to the Export and Import utilities. Pleaserefer to Chapter 4, for more information on theseviews.
These views provide information needed to support foreign datasources (FDSs) or data gateways:
These views provide information about the status of the OracleParallel Server environment:
These views provide information about the status of remote procedurecalls (RPCs):
These views provide information about snapshots:
These views provide information used by theSQL*Loader direct pathoption:
These views provide information required for tablespacepoint-in-time recovery. See Chapter 4, for more information on these views.
This view provides information required by theTrusted Oracle product:
联系客服
