Математика

Физика

Химия

Биология

Техника и    технологии

Expert Oracle - Thomas Kyte 2005 str 1256


Expert Oracle - Thomas Kyte 2005 str 1256



Table of Contents
Introduction 1
What This Book Is About 1
Who Should Use This Book? 2
How This Book Is Structured 3
Understanding the Database 3
Database Structures and Utilities 4
Performance 5
Advanced SQL Features 5
Extensibility 6
Security 7
Appendices 7
Conventions 7
Source Code and Updates 8
Setting Up 11
Setting up the SCOTT/TIGER Schema 11
The SQL*PLUS Environment 12
Setting up AUTOTRACE in SQL*PLUS 14
Controlling the Execution Plan Report 15
Interpreting the Execution Plan 15
C Compilers 15
Coding Conventions 16
Other Issues 16
Chapter 1: Developing Successful Oracle Applications 19
My Approach 20
The Black Box Approach 21
How (and how not) to Develop Database Applications 24
Understanding Oracle Architecture 25
Do not run Long Transactions Under MTS 25
Use Bind Variables 27
Download at Boykma.Com
Table of Contents
ii
Understanding Concurrency Control 30
Implementing Locking 30
Multi-Versioning 33
Database Independence? 37
The Impact of Standards 40
Features and Functions 43
Solving Problems Simply 44
Openness 46
How Do I Make it Run Faster? 47
The DBA-Developer Relationship 49
Summary 50
Chapter 2: Architecture 53
The Server 54
The Files 60
Parameter Files 60
Data Files 62
Temp Files 65
Control Files 66
Redo Log Files 66
Online Redo Log 67
Archived Redo Log 69
Files Wrap-Up 70
The Memory Structures 70
PGA and UGA 70
SGA 74
Fixed SGA 77
Redo Buffer 77
Block Buffer Cache 78
Shared Pool 80
Large Pool 82
Java Pool 83
Memory Structures Wrap-Up 84
The Processes 84
Server Processes 85
Dedicated Server versus Shared Server 87
Background Processes 90
Focused Background Processes 90
Utility Background Processes 95
Slave Processes 97
I/O Slaves 97
Parallel Query Slaves 97
Summary 98
Chapter 3: Locking and Concurrency 101
What are Locks? 102
Download at Boykma.Com
Table of Contents
iii
Locking Issues 104
Lost Updates 104
Pessimistic Locking 105
Optimistic Locking 106
Blocking 107
Blocked Inserts 107
Blocked Updates and Deletes 107
Deadlocks 108
Lock Escalation 111
Types of Lock 112
DML Locks 113
TX – (Transaction) Locks 113
TM – (DML Enqueue) Locks 118
DDL Locks 119
Latches and Internal Locks (Enqueues) 122
Manual Locking and User-Defined Locks 122
Manual Locking 123
Creating your own Locks 123
What is Concurrency Control? 123
Transaction Isolation Levels 124
READ UNCOMMITTED 125
READ COMMITTED 126
REPEATABLE READ 128
Getting a Consistent Answer 128
Lost Update Prevention 129
SERIALIZABLE 130
Read-Only Transactions 132
Summary 132
Chapter 4: Transactions 135
Transaction Control Statements 136
Integrity Constraints and Transactions 140
Bad Transaction Habits 143
Distributed Transactions 148
Redo and Rollback 150
Summary 154
Chapter 5: Redo and Rollback 157
Redo 157
What Does a COMMIT Do? 158
What Does a ROLLBACK Do? 163
How Much Redo Am I Generating? 164
Can I Turn Off Redo Log Generation? 173
Cannot Allocate a New Log? 176
Download at Boykma.Com
Table of Contents
iv
Block Cleanout 177
Log Contention 180
Temporary Tables and Redo/Rollback 182
Analyzing Redo 184
Rollback 184
What Generates the Most/Least Undo? 185
SET TRANSACTION 185
'ORA-01555: snapshot too old' 185
Rollback Segments Are in Fact Too Small 186
You Fetch Across COMMITs 190
Delayed Block Cleanout 191
Summary 195
Chapter 6: Database Tables 197
Types of Tables 197
Terminology 199
High Water Mark 199
FREELISTS 200
PCTFREE and PCTUSED 202
Row Migration 202
Setting PCTFREE and PCTUSED values 204
INITIAL, NEXT, and PCTINCREASE 208
MINEXTENTS and MAXEXTENTS 208
LOGGING and NOLOGGING 208
INITRANS and MAXTRANS 209
Heap Organized Table 209
Index Organized Tables 212
Index Organized Tables Wrap-up 224
Index Clustered Tables 224
Index Clustered Tables Wrap-up 231
Hash Cluster Tables 231
Hash Clusters Wrap-up 239
Nested Tables 240
Nested Tables Syntax 240
Nested Table Storage 248
Nested Tables Wrap-up 250
Temporary Tables 251
Temporary Table Wrap-up 258
Object Tables 258
Object Table Wrap-up 265
Summary 265
Download at Boykma.Com
Table of Contents
v
Chapter 7: Indexes 269
An Overview of Oracle Indexes 270
B*Tree Indexes 271
Reverse Key Indexes 275
Descending Indexes 276
When should you use a B*Tree Index? 277
B*Trees Wrap-up 285
Bitmap Indexes 285
When Should you use a Bitmap Index? 286
Bitmap Indexes Wrap-up 288
Function-Based Indexes 288
Important Implementation Details 288
Function-Based Index Example 289
Caveat 296
Function-Based Index Wrap-up 297
Application Domain Indexes 297
Application Domain Indexes Wrap-up 298
Frequently Asked Questions About Indexes 299
Do Indexes Work On Views? 299
Indexes and Nulls 299
Indexes on Foreign Keys 302
Why isn't my Index Getting Used? 303
Case 1 303
Case 2 303
Case 3 303
Case 4 303
Case 5 305
Case 6 307
Are my Indexes Being Used? 308
Myth: Space is Never Reused in an Index 308
Myth: Most Discriminating Elements Should be First 311
Summary 314
Chapter 8: Import and Export 317
A Quick Example 318
Why You Might Use IMP and EXP 319
Detecting Corruption 319
Extracting DDL 319
Cloning Schemas 319
Transporting Tablespaces 320
Rebuilding Instances 320
Copying Data between Platforms 320
Download at Boykma.Com
Table of Contents
vi
How They Work 320
The Options 320
EXP Parameters 321
IMP Parameters 323
Large Exports 325
Use the FILESIZE Parameter 325
Export Smaller Pieces 326
Export to an OS Pipe 327
Export to a Device that does not Support Seeking 328
Subsetting Data 328
Transporting Data 329
Getting the DDL 334
Getting around the limitations with scripts 337
Backup and Recovery 340
IMP/EXP is not a Reorganization Tool (Any More) 340
Importing into Different Structures 341
Direct Path Exports 345
Caveats and Errors 345
Cloning 346
Using IMP/EXP Across Versions 353
Where did my Indexes go? 353
Named versus Default-Named Constraints 356
National Language Support (NLS) Issues 359
Tables Spanning Multiple Tablespaces 360
Summary 365
Chapter 9: Data Loading 367
An Introduction to SQL*LOADER 368
How to ... 374
Load Delimited Data 374
Load Fixed Format Data 377
Load Dates 379
Load Data Using Sequences and Other Functions 380
Update Existing Rows and Insert New Rows 385
Load Report-Style Input Data 387
Load a File into a LONG RAW or LONG Field 389
Load Data with Embedded Newlines 391
Use a Character Other than a Newline 391
Use the FIX Attribute 392
Use the VAR Attribute 397
Use the STR Attribute 398
Embedded Newlines Wrap-Up 399
Unload Data 399
Load LOBs 409
Load a LOB via PL/SQL 409
Load LOB Data via SQLLDR 412
Load VARRAYS/Nested Tables with SQLLDR 418
Call SQLLDR from a Stored Procedure 421
Caveats 426
You Cannot Pick a Rollback Segment to Use 426
TRUNCATE Appears to Work Differently 426
SQLLDR Defaults to CHAR(255) 426
Command Line Overrides Control File 426
Summary 427
Download at Boykma.Com
Table of Contents
vii
Chapter 10: Tuning Strategies and Tools 429
Identifying the Problem 430
My Approach 431
Tuning is a Constant thing 432
Design for Performance 432
Try many Approaches 433
Program Defensively 434
Benchmark 434
Bind Variables and Parsing (Again) 436
Am I Using Bind Variables? 449
Bind Variables and Parsing Wrap-Up 451
SQL_TRACE, TIMED_STATISTICS, and TKPROF 451
Setting Up Tracing 452
Using and Interpreting TKPROF Output 454
Using and Interpreting Raw Trace Files 464
SQL_TRACE, TIMED_STATISTICS, and TKPROF Wrap-Up 474
DBMS_PROFILER 475
Instrumentation 475
StatsPack 477
Setting up StatsPack 477
StatsPack Wrap-Up 494
V$ Tables 494
V$EVENT_NAME 494
V$FILESTAT and V$TEMPSTAT 495
V$LOCK 495
V$MYSTAT 495
V$OPEN_CURSOR 496
V$PARAMETER 498
V$SESSION 498
V$SESSION_EVENT 500
V$SESSION_LONGOPS 500
V$SESSION_WAIT 500
V$SESSTAT 500
V$SESS_IO 501
V$SQL, V$SQLAREA 501
V$STATNAME 501
V$SYSSTAT 501
V$SYSTEM_EVENT 501
Summary 502
Chapter 11: Optimizer Plan Stability 505
An Overview of the Feature 506
Download at Boykma.Com
Table of Contents
viii
Uses of Optimizer Plan Stability 509
A Method to Implement Tuning 509
A Development Tool 514
To See the Indexes Used 515
To See what SQL is Executed by an Application 515
How Optimizer Plan Stability Works 516
OUTLINES and OUTLINE_HINTS 516
The _OUTLINES Views 516
The _OUTLINE_HINTS Views 517
Creating Stored Outlines 518
Privileges Needed for Stored Outlines 519
Using DDL 519
Using ALTER SESSION 520
The OUTLN User 521
Moving Outlines from Database to Database 522
Getting Just the Right Outline 522
Managing Outlines 525
Via DDL 525
ALTER OUTLINE 525
DROP OUTLINE 527
The OUTLN_PKG Package 528
OUTLN_PKG.DROP_UNUSED 528
OUTLN_PKG.DROP_BY_CAT 529
OUTLN_PKG.UPDATE_BY_CAT 529
Caveats 531
Outline Names and Case 531
ALTER SESSION Issue 533
DROP USER does not Drop Outlines 533
‘CURSOR_SHARING = FORCE’ and Outlines 533
Outlines Use Simple Text Matching 534
Outlines by Default are in the SYSTEM Tablespace 535
OR-Expansion 535
Performance 536
The Namespace of Outlines is Global 539
Errors you Might Encounter 540
Summary 542
Chapter 12: Analytic Functions 545
An Example 546
How Analytic Functions Work 549
The Syntax 549
The Function Clause 550
The Partition Clause 551
The Order By Clause 551
The Windowing Clause 553
Range Windows 555
Row Windows 558
Specifying Windows 560
The Functions 562
Download at Boykma.Com
Table of Contents
ix
Examples 566
The TOP-N Query 566
Pivot Query 576
Accessing Rows Around Your Current Row 582
Caveats 586
PL/SQL and Analytic functions 586
Analytic Functions in the Where Clause 588
NULLS and Sorting 588
Performance 590
Summary 591
Chapter 13: Materialized Views 593
A Brief History 594
What you’ll need to run the Examples 595
An Example 595
Uses of Materialized Views 601
How Materialized Views Work 601
Setting Up 602
Internal Mechanics 603
Query Rewrite 603
Making sure your View gets used 605
Constraints 605
Dimensions 609
DBMS_OLAP 618
Estimating Size 618
Dimension Validation 620
Recommending Materialized Views 622
Caveats 624
Materialized Views are Not Designed for OLTP Systems 624
Query Rewrite Integrity 624
Summary 625
Chapter 14: Partitioning 627
The Uses of Partitioning 627
Increased Availability 628
Reduced Administrative Burden 629
Enhanced DML and Query Performance 630
How Partitioning Works 632
Table Partitioning Schemes 632
Partitioning Indexes 637
Local Indexes 638
Global Indexes 645
Summary 655
Download at Boykma.Com
Table of Contents
x
Chapter 15: Autonomous Transactions 659
An Example 660
Why Use Autonomous Transactions? 662
Auditing that Can Not be Rolled Back 662
A Method to Avoid a Mutating Table 665
Performing DDL in Triggers 666
Writing to the Database 670
Really Strict Auditing 671
When the Environment Only Allows SELECTs 675
To Develop More Modular Code 678
How They Work 679
Transactional Control 679
Scope 681
Packaged Variables 681
Session Settings/Parameters 682
Database Changes 682
Locks 685
Ending an Autonomous Transaction 686
Savepoints 687
Caveats 689
No Distributed Transactions 689
PL/SQL Only 689
The Entire Transaction Rolls Back 689
Transaction-Level Temporary Tables 691
Mutating Tables 693
Errors You Might Encounter 695
Summary 696
Chapter 16: Dynamic SQL 699
Dynamic SQL versus Static SQL 700
Why Use Dynamic SQL? 702
How to Use Dynamic SQL 703
DBMS_SQL 703
Native Dynamic SQL 709
DBMS_SQL versus Native Dynamic SQL 713
Bind Variables 714
Number of Outputs Unknown at Compile-Time 718
Executing the Same Statement Dynamically Many Times 725
Caveats 734
It Breaks the Dependency Chain 734
The Code is More Fragile 735
It is Harder to Tune 735
Summary 736
Download at Boykma.Com
Table of Contents
xi
Chapter 17: interMedia 739
A Brief History 739
Uses of interMedia Text 740
Searching for Text 741
Managing a Variety of Documents 743
Indexing Text from Many Data Sources 743
It’s an Oracle Database, After All 746
Generating Themes 747
Searching XML Applications 749
How interMedia Text Works 749
interMedia Text Indexing 753
About ABOUT 756
Section Searching 756
Caveats 762
It is NOT Document Management 762
Index Synchronization 763
Indexing Information Outside the Database 764
Document Services 764
The Catalog Index 765
Errors You May Encounter 767
Index Out of Date 767
External Procedure Errors 767
The Road Ahead 768
Summary 769
Chapter 18: C-Based External Procedures 771
When Are They Used? 772
How Are They Implemented? 773
Configuring Your Server 775
Verify the extproc Program 777
Verify the Database Environment 777
Verify the Listener 779
The First Test 779
Compile extproc.c Code 780
Set Up the SCOTT/TIGER Account 780
Create the demolib LIBRARY 781
Installing and Running 782
Our First External Procedure 783
The Wrapper 784
The C Code 794
Building the extproc 816
Installing and Running 819
Download at Boykma.Com
Table of Contents
xii
LOB to File External Procedure (LOB_IO) 821
The LOB_IO Call Specification 821
The LOB_IO Pro*C Code 823
Building the extproc 827
Installing and Using LOB_IO 829
Errors You May Encounter 833
Summary 840
Chapter 19: Java Stored Procedures 843
Why Use Java Stored Procedures? 843
How They Work 845
Passing Data 849
Useful Examples 857
Getting a Directory Listing 858
Running an OS Command 860
Getting Time Down to the Milliseconds 863
Possible Errors 863
ORA-29549 Java Session State Cleared 863
Permissions Errors 864
ORA-29531 no method X in class Y 864
Summary 865
Chapter 20: Using Object Relational Features 867
Reasons For Using These Features 868
How Object Relational Features Work 868
Adding Data Types to your System 869
Adding Data Types Wrap-Up 882
Using Types to Extend PL/SQL 882
Creating a New PL/SQL Data Type 883
Unique Uses for Collections 892
SELECT * from PLSQL_FUNCTION 892
Bulk Fetching into RECORD Types 895
Inserting Using a RECORD Type 896
Using Types to Extend PL/SQL Wrap-Up 897
Object Relational Views 897
The Types 898
The O-R View 898
Summary 910
Download at Boykma.Com
Table of Contents
xiii
Chapter 21: Fine Grained Access Control 913
An Example 914
Why Use this Feature? 914
Ease of Maintenance 915
Performed in the Server 915
Easier Application Development 916
Evolutionary Application Development 916
Avoids Shared User Accounts 916
Supports Shared User Accounts 917
Hosting an Application as an ASP 917
How it Works 918
Example 1: Implementing a Security Policy 919
Example 2: Using Application Contexts 923
Caveats 940
Referential Integrity 941
The Covert Channel 941
Deleting Rows 942
Updating Rows 943
Cursor Caching 945
Export/Import 951
Export Issues 952
Import Issues 954
Debugging 955
Errors You Might Encounter 955
Summary 959
Chapter 22: n-Tier Authentication 963
Why Use n-Tier Authentication? 964
The Mechanics of n-Tier Authentication 966
Granting the Privilege 975
Auditing Proxy Accounts 976
Caveats 977
Summary 978
Chapter 23: Invoker and Definer Rights 981
An Example 982
When to Use Invoker Rights 984
Developing Generic Utilities 985
Data Dictionary Applications 988
Generic Object Types 991
Implementing your own Access Control 991
Download at Boykma.Com
Table of Contents
xiv
When to Use Definer Rights 994
Performance and Scalability 994
Security 995
How they Work 995
Definer Rights 995
Invoker Rights 1000
Resolving References and Conveying Privileges 1001
Compiling an Invoker Rights Procedure 1006
Using Template Objects 1006
Caveats 1010
Invoker Rights and Shared Pool Utilization 1010
Performance 1013
Code must be more Robust in Handling Errors 1015
Side Effects of Using SELECT * 1017
Beware of the 'Hidden' Columns 1018
Java and Invoker Rights 1020
Errors You Might Encounter 1025
Summary 1025
Appendix A: Necessary Supplied Packages 1027
Why Use the Supplied Packages? 1028
About The Supplied Packages 1028
DBMS_ALERT and DBMS_PIPE 1031
Why You Might Use Them 1032
Set Up 1032
DBMS_ALERT 1032
Concurrent Signals by More than One Session 1034
Repeated Calls to Signal by a Session 1036
Many Calls to Signal by Many Sessions before a Wait Routine is Called 1037
Summary 1037
DBMS_PIPE 1038
Pipe Servers versus External Routines 1040
Online Example 1041
Summary 1041
DBMS_APPLICATION_INFO 1042
Using the Client Info 1043
Using V$SESSION_LONGOPS 1045
Summary 1049
Download at Boykma.Com
Table of Contents
xv
DBMS_JAVA 1050
LONGNAME and SHORTNAME 1050
Setting Compiler Options 1051
SET_OUTPUT 1055
loadjava and dropjava 1055
Permission Procedures 1056
Summary 1058
DBMS_JOB 1059
Running a Job Once 1063
Ongoing Jobs 1066
Custom Scheduling 1068
Monitoring the Jobs and Finding the Errors 1070
Summary 1072
DBMS_LOB 1073
How do I Load LOBs? 1074
substr 1074
SELECT FOR UPDATE and Java 1075
Conversions 1077
From BLOB to VARCHAR2 and Back Again 1077
Converting From LONG/LONG RAW to a LOB 1081
Performing a Mass One-Time Conversion Illustration 1083
Performing an 'on the fly' Conversion 1086
How to Write a BLOB/CLOB to Disk 1088
Displaying a LOB on the Web Using PL/SQL 1089
Summary 1091
DBMS_LOCK 1092
DBMS_LOGMNR 1097
Overview 1099
Step 1: Creating the Data Dictionary 1099
Step 2: Using Log Miner 1102
Options and Usage 1107
Download at Boykma.Com
Table of Contents
xvi
Using Log Miner to Find Out When... 1110
PGA Usage 1111
Log Miner Limits 1113
Oracle Object Types 1113
Chained or Migrated Rows 1116
Other limits 1118
V$LOGMNR_CONTENTS 1119
Summary 1122
DBMS_OBFUSCATION_TOOLKIT 1123
The Wrapper 1125
Caveats 1139
Key Management 1140
The Client Application Manages and Stores Keys 1141
Store the Keys in the Same Database 1141
Store the Keys in the File System with the Database 1142
Summary 1143
DBMS_OUTPUT 1144
How DBMS_OUTPUT Works 1145
DBMS_OUTPUT and Other Environments 1149
Getting Around the Limits 1153
Using A Small Wrapper Function or Another Package 1153
Creating DBMS_OUTPUT Functionality 1154
Summary 1160
DBMS_PROFILER 1161
Caveats 1171
Summary 1171
DBMS_UTILITY 1172
COMPILE_SCHEMA 1172
ANALYZE_SCHEMA 1176
ANALYZE_SCHEMA with a Changing Schema 1178
ANALYZE_SCHEMA does not Analyze Everything 1178
ANALYZE_DATABASE 1179
Download at Boykma.Com
Table of Contents
xvii
FORMAT_ERROR_STACK 1179
FORMAT_CALL_STACK 1181
GET_TIME 1184
GET_PARAMETER_VALUE 1185
NAME_RESOLVE 1186
NAME_TOKENIZE 1188
COMMA_TO_TABLE, TABLE_TO_COMMA 1191
DB_VERSION and PORT_STRING 1193
GET_HASH_VALUE 1193
Summary 1198
UTL_FILE 1199
The UTL_FILE_DIR init.ora parameter 1200
Accessing Mapped Windows Drives 1201
Handling Exceptions 1202
Dumping a Web Page to Disk 1203
1023 Byte Limit 1205
Reading A Directory 1205
Summary 1207
UTL_HTTP 1208
UTL_HTTP Functionality 1209
Adding SSL to UTL_HTTP 1210
Really Using UTL_HTTP 1217
A Better UTL_HTTP 1219
Summary 1228
UTL_RAW 1229
UTL_SMTP and Sending Mail 1231
UTL_SMTP – a larger example 1231
Loading and using the JavaMail API 1236
Summary 1243
Download at Boykma.Com
Table of Contents
xviii
UTL_TCP 1244
The SocketType 1245
Summary 1256

Цена: 200руб.

Назад

Заказ

На главную страницу