dba-05-Data Dictionary

• Data dictionary tables • Dynamic performance tables
Data files
Control files Redo log files
Database
Data Dictionary Tables Dynamic Performance Tables
5-3
Dynamic performance views record current database activity.
• • • • •
Views are continually updated while the database is operational Information is accessed from:
Data Dictionary
The data dictionary is a set of read-only tables and views that record, verify, and provide information about its associated database.
5-6
Data Dictionary View Categories
The data dictionary consists of three main sets of static views distinguished from each other by their scope: • DBA: What is in all the schemas • ALL: What the user can access • USER: What is in the user's schema
• •
Describes the database and its objects Includes two types of objects:
– Base tables
– Store description of database – Created with CREATE DATABASE
–
Data Dictionary views
•
•
The Oracle server modifies it when a DDL statement is executed. Users and DBAs can use it as a read-only reference for information about the database.
Data dictionary and dynamic performance views can be queried for information.
• • •
A listing of views available can be retrieved by querying the DICTIONARY view. A listing of the columns and its contents can be accessed using DESCRIBE and SELECT. Column comments are available to retrieve more insight into what a column content means within a particular view.
5-5
How the Data Dictionary Is Used
The data dictionary has three primary uses:
•
The Oracle server uses it to find information about:
–
– –
Users
Schema objects Storage structures
5-13
5-16
5-10
Data Dictionary Examples
• • • •
General Overview
– DICTIONARY, DICT_COLUMNS
Schema objects
– DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS, DBA_CONSTRAINTS
– – Memory Control file
DBA uses dynamic views to monitor and tune the database Dynamic views are owned by SYS user DML is not allowed
5-9
Querying the Data Dictionary and Dynamic Performance Views
• •
Use the data dictionary views to get information about the database and instance Obtain information about data dictionary views from DICTIONARY and DICT_COLUMNS
Space allocation
– DBA_SEGMENTS, DBA_EXTENTS
Database structure
– DBA_TABLESPACES, DBA_DATA_FILES
5-11Biblioteka SummaryIn this lesson, you should have learned how to:
dictionary
•
Query the data dictionary
5-2
Data Dictionary
During database creation, the Oracle server creates additional object structures within the data files.
– Summarize base table information – Created using catalog.sql script
5-4
Data Dictionary Contents
The data dictionary provides information about:
• Logical and physical database structure • Definitions and space allocations of objects • Integrity constraints • Users • Roles • Privileges • Auditing
5-12
Practice 5 Overview
This practice covers the following topics:
•
•
Identify the components and contents of the data dictionary
Query the data dictionary
Data Dictionary Contents and Usage
Objectives
After completing this lesson, you should be able to do the following:
• Identify key data dictionary components • Identify the contents and uses of the data
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-7
Dynamic Performance Tables