v
3 Tablespaces and Datafiles
Databases, Tablespaces, and Datafiles 3-2
Allocating More Space for a Database 3-3
Tablespaces 3-6
The SYSTEM Tablespace 3-6
Using Multiple Tablespaces 3-7
Space Management in Tablespaces 3-7
Online and Offline Tablespaces 3-9
Read-Only Tablespaces 3-10
Temporary Tablespaces 3-12
Transporting Tablespaces between Databases 3-13
Datafiles 3-16
Datafile Contents 3-16
Size of Datafiles 3-16
Offline Datafiles 3-17
Temporary Datafiles 3-17
4 Data Blocks, Extents, and Segments
The Relationships Among Data Blocks, Extents, and Segments 4-2
Data Blocks 4-3
Data Block Format 4-3
An Introduction to PCTFREE, PCTUSED, and Row Chaining 4-5
Extents 4-11
When Extents Are Allocated 4-11
Determining the Number and Size of Extents 4-11
How Extents Are Allocated 4-12
When Extents Are Deallocated 4-14
Segments 4-16
Data Segments 4-16
Index Segments 4-17
Temporary Segments 4-17
Rollback Segments 4-19
vi
Part III The Oracle Instance
5 Database and Instance Startup and Shutdown
Overview of an Oracle Instance 5-2
The Instance and the Database 5-2
Connecting with Administrator Privileges 5-3
Parameter Files 5-4
Instance and Database Startup 5-5
Starting an Instance 5-5
Mounting a Database 5-6
Opening a Database 5-7
Database and Instance Shutdown 5-9
Closing a Database 5-10
Dismounting a Database 5-10
Shutting Down an Instance 5-10
6 Distributed Processing
Oracle Client/Server Architecture 6-2
Distributed Processing 6-2
Net8 6-5
How Net8 Works 6-5
The Network Listener 6-6
Multi-Tier Architecture 6-7
Clients 6-8
Application Servers 6-8
Database Servers 6-8
7 Memory Architecture
Introduction to Oracle Memory Structures 7-2
System Global Area (SGA) 7-2
The Database Buffer Cache 7-3
The Redo Log Buffer 7-6
The Shared Pool 7-6
The Large Pool 7-12
vii
Size of the SGA 7-12
Controlling the SGA’s Use of Memory 7-13
Program Global Areas (PGA) 7-14
Contents of a PGA 7-14
Size of a PGA 7-15
Sort Areas 7-16
Virtual Memory 7-17
Software Code Areas 7-17
8 Process Architecture
Introduction to Processes 8-2
Multiple-Process Oracle Systems 8-2
Types of Processes 8-2
User Processes 8-4
Connections and Sessions 8-4
Oracle Processes 8-5
Server Processes 8-5
Background Processes 8-5
Trace Files and the ALERT File 8-14
Multi-Threaded Server Configuration 8-16
Dispatcher Request and Response Queues 8-17
Shared Server Processes 8-19
Artificial Deadlocks 8-19
Restricted Operations of the Multi-Threaded Server 8-20
An Example of Oracle Using the Multi-Threaded Server 8-20
Dedicated Server Configuration 8-22
An Example of Oracle Using Dedicated Server Processes 8-24
The Program Interface 8-25
Program Interface Structure 8-25
The Program Interface Drivers 8-26
Operating System Communications Software 8-26
9 Database Resource Management
Introduction to the Database Resource Manager 9-2
Resource Consumer Groups and Resource Plans 9-3
viii
What Are Resource Consumer Groups? 9-3
What Are Resource Plans? 9-4
Resource Allocation Methods 9-6
CPU Resource Allocation Method: Emphasis 9-6
Maximum Degree of Parallelism Resource Allocation Method: Absolute 9-7
Resource Plan Directives 9-7
Examples 9-7
Using Resource Consumer Groups and Resource Plans 9-8
Using Subplans 9-9
Using Multi-Level Resource Plans 9-10
Using the Parallel Degree Limit Resource Directive 9-10
Summary 9-11
Using the Database Resource Manager 9-11
Part IV The Object-Relational DBMS
10 Schema Objects
Overview of Schema Objects 10-2
Tables 10-3
How Table Data Is Stored 10-4
Nulls 10-7
Default Values for Columns 10-8
Nested Tables 10-9
Temporary Tables 10-10
Views 10-11
Storage for Views 10-12
How Views Are Used 10-13
The Mechanics of Views 10-14
Dependencies and Views 10-15
Updatable Join Views 10-15
Object Views 10-16
Inline Views 10-16
Materialized Views 10-17
Refreshing Materialized Views 10-18
Materialized View Logs 10-18
ix
Dimensions 10-18
The Sequence Generator 10-19
Synonyms 10-20
Indexes 10-21
Unique and Nonunique Indexes 10-22
Composite Indexes 10-22
Indexes and Keys 10-23
Indexes and Nulls 10-24
Function-Based Indexes 10-24
How Indexes Are Stored 10-26
Key Compression 10-29
Reverse Key Indexes 10-31
Bitmap Indexes 10-32
Index-Organized Tables 10-36
Benefits of Index-Organized Tables 10-38
Index-Organized Tables with Row Overflow Area 10-38
Secondary Indexes on Index-Organized Tables 10-39
Additional Features of Index-Organized Tables 10-39
Applications of Interest for Index-Organized Tables 10-40
Application Domain Indexes 10-42
Indextypes 10-43
Domain Indexes 10-43
User-Defined Operators 10-44
Clusters 10-46
Performance Considerations 10-48
Format of Clustered Data Blocks 10-49
The Cluster Key 10-49
The Cluster Index 10-50
Hash Clusters 10-50
How Data Is Stored in a Hash Cluster 10-51
Hash Key Values 10-53
Hash Functions 10-54
Allocation of Space for a Hash Cluster 10-55
Single Table Hash Clusters 10-57
x
11 Partitioned Tables and Indexes
Introduction to Partitioning 11-2
What Is Partitioning? 11-2
Advantages of Partitioning 11-5
Manual Partitioning with Partition Views 11-11
Basic Partitioning Model 11-13
Range Partitioning 11-15
Hash Partitioning 11-16
Composite Partitioning 11-17
Partition and Subpartition Names 11-18
Partitioning and Subpartitioning Columns and Keys 11-19
Partition Bounds for Range Partitioning 11-20
Equipartitioning 11-24
Rules for Partitioning Tables and Indexes 11-27
Table Partitioning 11-27
Index Partitioning 11-29
Partitioning of Tables with LOB Columns 11-38
Partitioning Index-Organized Tables and Their Secondary Indexes 11-42
DML Partition Locks and Subpartition Locks 11-45
DML Partition Locks 11-46
DML Subpartition Locks 11-46
Performance Considerations for Oracle Parallel Server 11-47
Maintenance Operations 11-48
Partition Maintenance Operations 11-48
Managing Indexes 11-59
Privileges for Partitioned Tables and Indexes 11-62
Auditing for Partitioned Tables and Indexes 11-63
Partition-Extended and Subpartition-Extended Table Names 11-63
PARTITION and SUBPARTITION Specifications 11-63
Viewing Partitions or Subpartitions as Tables 11-64
Using Partition- and Subpartition-Extended Table Names 11-64
12 Built-In Datatypes
Overview of Oracle Datatypes 12-2
Character Datatypes 12-5
xi
CHAR Datatype 12-5
VARCHAR2 and VARCHAR Datatypes 12-5
Column Lengths for Character Datatypes and NLS Character Sets 12-6
NCHAR and NVARCHAR2 Datatypes 12-6
LOB Character Datatypes 12-7
LONG Datatype 12-7
NUMBER Datatype 12-8
Internal Numeric Format 12-9
DATE Datatype 12-10
Using Julian Dates 12-11
Date Arithmetic 12-11
Centuries and the Year 2000 12-12
LOB Datatypes 12-12
BLOB Datatype 12-13
CLOB and NCLOB Datatypes 12-13
BFILE Datatype 12-14
RAW and LONG RAW Datatypes 12-14
ROWID and UROWID Datatypes 12-15
The ROWID Pseudocolumn 12-15
Physical Rowids 12-16
Logical Rowids 12-20
Rowids in Non-Oracle Databases 12-22
ANSI, DB2, and SQL/DS Datatypes 12-22
Data Conversion 12-23
13 User-Defined Datatypes
Introduction 13-2
Complex Data Models 13-2
Multimedia Datatypes 13-3
User-Defined Datatypes 13-3
Object Types 13-4
Collection Types 13-10
Application Interfaces 13-13
SQL 13-13
PL/SQL 13-13
xii
Pro*C/C++ 13-14
OCI 13-14
OTT 13-15
JPublisher 13-15
JDBC 13-16
SQLJ 13-16
14 Using User-Defined Datatypes
Introduction 14-2
Object Types and References 14-3
Properties of Object Attributes 14-3
Object References 14-7
Name Resolution 14-8
Collections 14-10
Querying Collections 14-10
Collection Unnesting 14-10
Nested Table Locators 14-11
DML on Collections 14-12
Privileges on User-Defined Types and Their Methods 14-13
System Privileges 14-13
Schema Object Privileges 14-13
Using Types in New Types or Tables 14-13
Example 14-14
Privileges on Type Access and Object Access 14-15
Dependencies and Incomplete Types 14-16
Completing Incomplete Types 14-17
Type Dependencies of Tables 14-17
Storage of User-Defined Types 14-18
Leaf-Level Attributes 14-18
Row Objects 14-18
Column Objects 14-19
REFs 14-19
Nested Tables 14-19
VARRAYs 14-20
Utilities 14-20
xiii
Import/Export of User-Defined Types 14-20
Loading User-defined Types 14-20
15 Object Views
Introduction 15-2
Advantages of Object Views 15-2
Defining Object Views 15-3
Using Object Views 15-4
Updating Object Views 15-5
Updating Nested Table Columns in Views 15-5
Part V Data Access
16 SQL and PL/SQL
Structured Query Language (SQL) 16-2
SQL Statements 16-3
Identifying Nonstandard SQL 16-6
Recursive SQL 16-6
Cursors 16-6
Shared SQL 16-7
Parsing 16-7
SQL Processing 16-8
Overview of SQL Statement Execution 16-8
DML Statement Processing 16-10
DDL Statement Processing 16-14
Controlling Transactions 16-14
PL/SQL 16-15
How PL/SQL Executes 16-15
Language Constructs for PL/SQL 16-17
Stored Procedures 16-18
External Procedures 16-20
17 Transaction Management
Introduction to Transactions 17-2
xiv
Statement Execution and Transaction Control 17-3
Statement-Level Rollback 17-4
Oracle and Transaction Management 17-4
Committing Transactions 17-5
Rolling Back Transactions 17-6
Savepoints 17-7
The Two-Phase Commit Mechanism 17-7
Discrete Transaction Management 17-8
Autonomous Transactions 17-9
Autonomous PL/SQL Blocks 17-9
Transaction Control Statements in Autonomous Blocks 17-10
18 Procedures and Packages
An Introduction to Stored Procedures and Packages 18-2
Stored Procedures and Functions 18-2
Packages 18-4
Procedures and Functions 18-6
Procedure Guidelines 18-7
Benefits of Procedures 18-7
Anonymous PL/SQL Blocks versus Stored Procedures 18-9
Standalone Procedures 18-9
Definer Rights and Invoker Rights 18-9
Dependency Tracking for Stored Procedures 18-11
External Procedures 18-11
Packages 18-11
Benefits of Packages 18-15
Dependency Tracking for Packages 18-16
Oracle Supplied Packages 18-16
How Oracle Stores Procedures and Packages 18-17
Compiling Procedures and Packages 18-17
Storing the Compiled Code in Memory 18-17
Storing Procedures or Packages in Database 18-17
How Oracle Executes Procedures and Packages 18-18
Verifying User Access 18-18
Verifying Procedure Validity 18-18
Không có nhận xét nào:
Đăng nhận xét