Using Data Dictionary and Dynamic Performance Views

5

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

Data files

Control files

• 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

Redo Log files

Database

Data Dictionary tables

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

Description

Catalog and data dictionary information

cat*.sql

Database package specifications

dbms*.sql

Wrapped database package code

prvt*.plb

Views and tables for database utilities

utl*.sql

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

Using Data Dictionary and Dynamic Performance Views

5

Objectives

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

Data files

Control files

• 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

Redo Log files

Database

Data Dictionary tables

Base Tables and Data Dictionary Views

The data dictionary contains two parts: • Base tables

• 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

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

• 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_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

Description

Catalog and data dictionary information

cat*.sql

Database package specifications

dbms*.sql

Wrapped database package code

prvt*.plb

Views and tables for database utilities

utl*.sql

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

Có thể bạn quan tâm

Tài liêu mới

AI tóm tắt

Giới thiệu tài liệu

Đối tượng sử dụng

Từ khoá chính

Nội dung tóm tắt

Giới thiệu

Về chúng tôi

Việc làm

Quảng cáo

Liên hệ

Chính sách

Thoả thuận sử dụng