DBAI_les05_Rev1_1

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

Using Data Dictionary and Dynamic Performance Views:

Using Data Dictionary and Dynamic Performance Views

Objectives:

Objectives After completing this lesson, you should be able to do the following: Identify built-in database objects Identify the contents and uses of the data dictionary Understand how data dictionary views are created Identify data dictionary view categories Query the data dictionary and dynamic performance views Understand administrative script naming conventions

Built-In Database Objects:

Built-In Database Objects Other objects created with the database: Data Dictionary Performance tables PL/SQL packages Database event triggers

Data Dictionary:

Data Dictionary Central to every Oracle database Describes the database and its objects Contains read-only tables and views Stored in the SYSTEM tablespace Owned by the user SYS Maintained by the Oracle server Accessed with SELECT Control files Data files Redo Log files Database Data Dictionary tables

Base Tables and Data Dictionary Views:

Base Tables and Data Dictionary Views The data dictionary contains two parts: Base tables Stores description of the database Created with CREATE DATABASE Data dictionary views Used to simplify the base table information Accessed through public synonyms Created with the catalog.sql script

Creating Data Dictionary Views:

Creating Data Dictionary Views Script Purpose catalog.sql Creates commonly used data dictionary views and synonyms catproc.sql Runs scripts required for server-side PL/SQL

Data Dictionary Contents:

Data Dictionary Contents The data dictionary provides information about: Logical and physical database structures Definitions and space allocations of objects Integrity constraints Users Roles Privileges Auditing

How the Data Dictionary is Used:

How the Data Dictionary is Used Primary uses: Oracle server uses it to find information about: Users Schema objects Storage structures Oracle server modifies it when a DDL statement is executed. Users and DBAs use it as a read-only reference for information about the database.

Data Dictionary View Categories:

Data Dictionary View Categories Three sets of static views Distinguished by their scope: DBA : What is in all the schemas ALL : What the user can access USER : What is in the user’s schema USER_ xxx Objects owned by the current user ALL_ xxx Objects accessible by the current user DBA_ xxx All of the objects in the database

Data Dictionary Examples:

Data Dictionary Examples General Overview DICTIONARY , DICT_COLUMNS Schema objects DBA_TABLES , DBA_INDEXES , DBA_TAB_COLUMNS , DBA_CONSTRAINTS Space allocation DBA_SEGMENTS , DBA_EXTENTS Database structure DBA_TABLESPACES , DBA_DATA_FILES

Dynamic Performance Tables:

Dynamic Performance Tables Virtual tables Record current database activity Continually updated while the database is operational Information is accessed from memory and control file Used to monitor and tune the database Owned by SYS user Synonyms begin with V$ Listed in V$FIXED_TABLE

Dynamic Performance Table Examples:

Dynamic Performance Table Examples V$CONTROLFILE V$DATABASE V$DATAFILE V$INSTANCE V$PARAMETER V$SESSION V$SGA $SPPARAMETER V$TABLESPACE V$THREAD V$VERSION

Querying the Data Dictionary and Dynamic Performance Views :

Querying the Data Dictionary and Dynamic Performance Views

Administrative Scripts Naming Conventions:

Administrative Scripts Naming Conventions Convention cat*.sql dbms*.sql prvt*.plb utl*.sql Description Catalog and data dictionary information Database package specifications Wrapped database package code Views and tables for database utilities

Summary:

Summary In this lesson, you should have learned how to: Identify built-in database objects Identify the contents and uses of the data dictionary Understand how data dictionary views are created Identify data dictionary view categories Query the data dictionary and dynamic performance views Understand administrative script naming conventions

Practice 5 Overview:

Practice 5 Overview This practice covers the following topics: Identify the components and contents of the data dictionary Query the data dictionary

authorStream Live Help