5 Using Data Dictionary and Dynamic Performance Views

Objectives After completing this lesson, you should be able to do the following: •

Built-In Database Objects Other objects created with the database: • Data dictionary • Performance

Data Dictionary • • Central to every Oracle database Describes the database and its

Base Tables and Data Dictionary Views The data dictionary contains two parts: • Base

Creating Data Dictionary Views Script Purpose catalog. sql Creates commonly used data dictionary views

Data Dictionary Contents The data dictionary provides information about: • Logical and physical database

How the Data Dictionary Is Used Primary uses: • Oracle server uses it to

Data Dictionary View Categories • Three sets of static views • Distinguished by their

Data Dictionary Examples • General overview: DICTIONARY, DICT_COLUMNS • Schema objects: DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS,

Dynamic Performance Tables • Virtual tables • Record current database activity • Continually updated

Dynamic Performance Examples • • • 5 -13 V$CONTROLFILE V$DATABASE V$DATAFILE V$INSTANCE V$PARAMETER V$SESSION

Administrative Script Naming Conventions 5 -15 Convention Description cat*. sql Catalog and data dictionary

5 -16 Copyright © Oracle Corporation, 2002. All rights reserved.

Summary In this lesson, you should have learned how to: • Identify built-in database

Practice 5 Overview This practice covers the following topics: • Identifying the components and

Slides: 16

Download presentation

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 5 -2 Copyright © Oracle Corporation, 2002. All rights reserved.

Built-In Database Objects Other objects created with the database: • Data dictionary • Performance tables • PL/SQL packages • Database event triggers 5 -3 Copyright © Oracle Corporation, 2002. All rights reserved.

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 Redo files Log files Database Data Dictionary tables 5 -4 Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -5 Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -6 Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -7 Copyright © Oracle Corporation, 2002. All rights reserved.

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. 5 -8 Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -9 Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -11 Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -12 Copyright © Oracle Corporation, 2002. All rights reserved.

Dynamic Performance Examples • • • 5 -13 V$CONTROLFILE V$DATABASE V$DATAFILE V$INSTANCE V$PARAMETER V$SESSION V$SGA $SPPARAMETER V$TABLESPACE V$THREAD V$VERSION Copyright © Oracle Corporation, 2002. All rights reserved.

Administrative Script Naming Conventions 5 -15 Convention Description cat*. sql Catalog and data dictionary information dbms*. sql Database package specifications prvt*. plb Wrapped database package code utl*. sql Views and tables for database utilities Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -17 Copyright © Oracle Corporation, 2002. All rights reserved.

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 5 -18 Copyright © Oracle Corporation, 2002. All rights reserved.