Using Data Dictionary and Dynamic Performance Views

1. Using Data Dictionary and Dynamic Performance Views

2. 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

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

4. 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

5. 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

6. 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

7. 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

8. 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.

9. 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

10. 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

11. 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

12. 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

13. 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 Administrative Scripts Naming Conventions

14. 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

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