SELECT
statements to determine their privileges, which tables exist in their schema, which columns are in these tables, whether indexes are built on these columns, and so on.WHERE
clauses to simplify the information. These views contain the names and description of all objects in the data dictionary. Some views are accessible to all database users, whereas others are intended for administrators only.Prefix | User Access | Contents | Notes |
---|---|---|---|
DBA_ | Database administrators | All objects | Some DBA_ views have additional columns containing information useful to the administrator. |
ALL_ | All users | Objects to which user has privileges | Includes objects owned by user. These views obey the current set of enabled roles. |
USER_ | All users | Objects owned by user | Views with the prefix USER_ usually exclude the column OWNER . This column is implied in the USER_ views to be the user issuing the query. |
DBA_LOCK
view but no ALL_LOCK
view.DICTIONARY
view contains the names and abbreviated descriptions of all data dictionary views. The following query of this view includes partial sample output:DBA_
show all relevant information in the entire database. DBA_
views are intended only for administrators.ALL_
refer to the user's overall perspective of the database. These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns.ALL_
views obey the current set of enabled roles, query results depend on which roles are enabled, as shown in the following example:ALL_
views in a stored procedure, where roles are not enabled by default.USER_
. These views:ALL_
viewsOWNER
is impliedPUBLIC
synonyms for convenienceDUAL
is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL
.DUAL
table has one column called DUMMY
and one row containing the value X
. The following example queries DUAL
to perform an arithmetical operation:DUAL
tableSYSTEM
tablespace. Because the SYSTEM
tablespace is always online when the database is open, the data dictionary is always available when the database is open.SYSTEM
tablespaceSYS
owns all base tables and user-accessible views of the data dictionary. Data in the base tables of the data dictionary is necessary for Oracle Database to function. Therefore, only Oracle Database should write or change data dictionary information. No Oracle Database user should ever alter rows or schema objects contained in the SYS
schema because such activity can compromise data integrity. The security administrator must keep strict control of this central account.hr
creates a table named interns
, then new rows are added to the data dictionary that reflect the new table, columns, segment, extents, and the privileges that hr
has on the table. This new information is visible the next time the dictionary views are queried.COMMENTS
columns describing the tables and their columns are not cached in the dictionary cache, but may be cached in the database buffer cache.SYS
owns the dynamic performance tables, whose names begin with V_$
. Views are created on these tables, and then public synonyms prefixed with V$
. For example, the V$DATAFILE
view contains information about data files. The V$FIXED_TABLE
view contains information about all of the dynamic performance tables and views.V$
view, a corresponding GV$
view exists. In Oracle Real Application Clusters (Oracle RAC), querying a GV$
view retrieves the V$
view information from all qualified database instances (see 'Database Server Grid').catalog.sql
script, which contains definitions of the views and public synonyms for the dynamic performance views. You must run catalog.sql
to create these views and synonyms.catalog.sql
manuallyV$INSTANCE
and V$BGPROCESS
when the database is started but not mounted. However, you cannot query V$DATAFILE
until the database has been mounted.DBMS_METADATA
package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided: a flexible, sophisticated interface for programmatic control, and a simplified interface for ad hoc querying.DBMS_METADATA