This chapter describes the central set of read-only reference tables and views of each Oracle database, known collectively as the data dictionary. Show
This chapter contains the following topics:
Introduction to the Data DictionaryOne of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:
The data
dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database's Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators. Use SQL statements to access the data dictionary. Because the data dictionary is read only, you can issue only queries ( Structure of the Data DictionaryThe data dictionary consists of the following: Base TablesThe underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format. User-Accessible ViewsThe views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and SYS, Owner of the Data DictionaryThe Oracle user Caution: Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database. How the Data Dictionary Is UsedThe data dictionary has three primary uses:
How Oracle Uses the Data DictionaryData in the base tables of the data dictionary is necessary for Oracle to function. Therefore, only Oracle should write or change data dictionary information. Oracle provides scripts to modify the data dictionary tables when a database is upgraded or downgraded. Caution: No data in any data dictionary table should be altered or deleted by any user. During database operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data. For example, if user Kathy creates a table named Public Synonyms for Data Dictionary ViewsOracle creates public synonyms for many data dictionary views so users can access them conveniently. The security administrator can also create additional public synonyms for schema objects that are used systemwide. Users should avoid naming their own schema objects with the same names as those used for public synonyms. Cache the Data Dictionary for Fast AccessMuch of the data dictionary information is kept in the SGA in the dictionary cache, because Oracle constantly accesses the data dictionary during database operation to validate user access and to verify the state of schema objects. All information is stored in memory using the least recently used (LRU) algorithm. Parsing
information is typically kept in the caches. The Other Programs and the Data DictionaryOther Oracle products can reference existing views and create additional data dictionary tables or views of their own. Application developers who write programs that refer to the data dictionary should refer to the public synonyms rather than the underlying tables: the synonyms are less likely to change between software releases. How to Use the Data DictionaryThe views of the data dictionary serve as a reference for all database users. Access the data dictionary views with SQL statements. Some views are accessible to all Oracle users, and others are intended for database administrators only. The data dictionary is always available when the database is
open. It resides in the The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes: Table 7-1 Data Dictionary View Prefixes
The set of columns is identical across views, with these exceptions:
Views with the Prefix USERThe views most
likely to be of interest to typical database users are those with the prefix
For example, the following query returns all the objects contained in your schema: SELECT object_name, object_type FROM USER_OBJECTS; Views with the Prefix ALLViews with the prefix SELECT owner, object_name, object_type FROM ALL_OBJECTS; Views with the Prefix DBAViews with the prefix SELECT owner, object_name, object_type FROM SYS.DBA_OBJECTS; Oracle recommends that you implement data dictionary protection to prevent users having the The DUAL TableThe table named Dynamic Performance TablesThroughout its operation, Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables. Dynamic performance tables are not true tables, and they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users. These views are sometimes called fixed views because they cannot be altered or removed by the database administrator.
Database Object MetadataThe
What contains details about date and some call it metadata?Often used in the context of ongoing research, administrative metadata includes such details as date created, file size and type, and archiving requirements. Descriptive metadata identifies specific characteristics of a piece of data, such as bibliographic data, keywords, song titles, volume numbers, etc.
What is another term used for data dictionary?noun. computing an index of data held in a database and used to assist in the access to dataAlso called: data directory.
What database term is used to describe a request for specific data from the database?A database query is either an action query or a select query. A select query is one that retrieves data from a database.
What is a data dictionary What does it contain and how is it used quizlet?Data dictionaries include standard definitions of data elements, their meanings, and allowable values. A data dictionary combines definitions of each primitive data element and indicates how those elements combine into composite data elements.
|