Using Data Dictionary and Dynamic Performance Views

Using Data Dictionaryand Dynamic Performance Views

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 • Describe how data dictionary views are created • Identify data dictionary view categories • Query the data dictionary and dynamic performance views • Describe administrative script naming conventions

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

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 Data files Control files Redo Log files Database Data Dictionarytables

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 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 • 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 • 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 • 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 DBA_xxx All of the objects in the database ALL_xxx Objects accessible by the current user USER_xxx Objects owned by the current user

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 • 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 Examples • V$CONTROLFILE • V$DATABASE • V$DATAFILE • V$INSTANCE • V$PARAMETER • V$SESSION • V$SGA • $SPPARAMETER • V$TABLESPACE • V$THREAD • V$VERSION

Administrative Script 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 • In this lesson, you should have learned how to: • Identify built-in database objects • Identify the contents and uses of the data dictionary • Describe how data dictionary views are created • Identify data dictionary view categories • Query the data dictionary and dynamic performance views • Describe administrative script naming conventions

Practice 5 Overview • This practice covers the following topics: • Identifying the components and contents of the data dictionary • Querying the data dictionary and dynamic performance views

Using Data Dictionary and Dynamic Performance Views