Friday , July 21 2017
Home / Oracle DBA / Data Dictionary and Dynamic Performance Views

Data Dictionary and Dynamic Performance Views

Data Dictionary and Dynamic Performance Views

Overview of the Data Dictionary
An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following:
■The definitions of every schema object in the database, including default values for columns and integrity constraint information
■The amount of space allocated for and currently used by the schema objects
■The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users
The data dictionary is a central part of data management for every Oracle database.
Contents of the Data Dictionary
The data dictionary consists of the following types of objects:
■Base tables
These underlying tables store information about the database. Only Oracle Database should write to and read these tables. Users rarely access the base tables directly because they are normalized and most data is stored in a cryptic format.
■Views
These views decode the base table data into useful information, such as user or table names, using joins and 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.
Views with the Prefix DBA_
Views with the prefix DBA_ show all relevant information in the entire database. DBA_ views are intended only for administrators.
For example, the following query shows information about all objects in the database:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
ORDER BY OWNER, OBJECT_NAME;
Views with the Prefix ALL_
Views with the prefix 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.
For example, the following query returns information about all the objects to which you have access:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
ORDER BY OWNER, OBJECT_NAME;
Views with the Prefix USER_
The views most likely to be of interest to typical database users are those with the prefix USER_. These views:
■Refer to the user’s private environment in the database, including metadata about schema objects created by the user, grants made by the user, and so on
■Display only rows pertinent to the user, returning a subset of the information in the ALL_ views
■Has columns identical to the other views, except that the column OWNER is implied
■Can have abbreviated PUBLIC synonyms for convenience
For example, the following query returns all the objects contained in your schema:
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
ORDER BY OBJECT_NAME;
Storage of the Data Dictionary
The data dictionary base tables are the first objects created in any Oracle database. All data dictionary tables and views for a database are stored in the SYSTEM table space. Because the SYSTEM table space is always online when the database is open, the data dictionary is always available when the database is open.
View More:
ORACLE Fail over – Standby Becomes Primary
Add Standby Redo Log Groups to Primary Database
Additional commands for Dataguard

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *