Transcript Jerry Held - UML软件工程组织

使用数据库字典和动态性能视图
Copyright © Oracle Corporation, 2001. All rights reserved.
目标
学完这个课程，你应该可以掌握你下内容:
• 区别内置的数据库对象
• 区别数据字典的内容和用法
• 理解数据字典视图是如何创建的
• 区别数据字典视图的种类
• 查询数据字典和动态性能视图
• 理解administrative script 命名规则
5-2
Copyright © Oracle Corporation, 2001. All rights reserved.
内置的数据库对象
Other objects created with the database:
• Data Dictionary
• Performance tables
• PL/SQL packages
• Database event triggers
5-3
Copyright © Oracle Corporation, 2001. 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, 2001. 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, 2001. All rights reserved.
Creating Data Dictionary Views
5-6
Script
Purpose
catalog.sql
Creates commonly used data dictionary
views and synonyms
catproc.sql
Runs scripts required for server-side
PL/SQL
Copyright © Oracle Corporation, 2001. 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, 2001. 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, 2001. 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, 2001. 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, 2001. 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, 2001. All rights reserved.
Dynamic Performance Table 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, 2001. All rights reserved.
Administrative Scripts 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, 2001. 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
• 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
5-16
Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 5 Overview
This practice covers the following topics:
• Identify the components and contents of the data
dictionary
• Query the data dictionary
5-17
Copyright © Oracle Corporation, 2001. All rights reserved.