Tài liệu Expert one-on-one Oracle

Content
Introduction .18
What this Book is About .18
Who Should Use this Book? .19
How This Book is Structured .20
Understanding the Database 21
Database Structures and Utilities 22
Performance 23
Advanced SQL Features 23
Extensibility 24
Security 25
Appendices 26
Conventions 26
Source Code and Updates .27
Setting Up 28
Overview 28
The SQL*PLUS Environment 28
Setting up AUTOTRACE in SQL*PLUS 30
C Compilers .32
Coding Conventions 32
Other Issues .33
Chapter 1: Developing Successful Oracle Applications .35
Overview 35
My Approach 36
The Black Box Approach 37
How (and how not) to Develop Database Applications .41
Understanding Oracle Architecture 41
Understanding Concurrency Control 48
Database Independence? . 57
How Do I Make it Run Faster? 71
The DBA‐Developer Relationship . 73
Summary 74
Chapter 2: Architecture .76
Overview 76
The Server 76
The Files .84
Parameter Files . 84
Data Files 87
Temp Files . 91
Control Files 91
Redo Log Files 92
Expert one-on-one Oracle
4
Files Wrap‐Up 96
The Memory Structures 97
PGA and UGA 97
SGA 103
Memory Structures Wrap‐Up 115
The Processes 115
Server Processes . 116
Background Processes 122
Slave Processes 130
Summary 132
Chapter 3: Locking and Concurrency .133
Overview 133
What are Locks? 133
Locking Issues 136
Lost Updates 136
Blocking 140
Deadlocks . 141
Lock Escalation 146
Types of Lock 147
DML Locks 147
DDL Locks 155
Latches and Internal Locks (Enqueues) 159
Manual Locking and User‐Defined Locks 160
What is Concurrency Control? .161
Transaction Isolation Levels . 162
READ UNCOMMITTED 163
READ COMMITTED . 165
REPEATABLE READ . 167
SERIALIZABLE 170
Read‐Only Transactions . 172
Summary 173
Chapter 4: Transactions .175
Overview 175
Transaction Control Statements 175
Integrity Constraints and Transactions .182
Bad Transaction Habits .184
Distributed Transactions 191
Redo and Rollback .194
Summary 198
Chapter 5: Redo and Rollback .200
Overview 200
Redo 200
What Does a COMMIT Do? 201
Expert one-on-one Oracle
5
What Does a ROLLBACK Do? . 208
How Much Redo Am I Generating? . 209
Can I Turn Off Redo Log Generation? . 221
Cannot Allocate a New Log? . 224
Block Cleanout . 226
Log Contention 230
Temporary Tables and Redo/Rollback . 232
Analyzing Redo 235
Rollback .236
What Generates the Most/Least Undo? 236
SET TRANSACTION . 236
ʹORA‐01555: snapshot too oldʹ . 237
Summary 250
Chapter 6: Database Tables 252
Overview 252
Types of Tables 252
Terminology 254
High Water Mark 254
FREELISTS 255
PCTFREE and PCTUSED 258
INITIAL, NEXT, and PCTINCREASE . 265
MINEXTENTS and MAXEXTENTS . 265
LOGGING and NOLOGGING . 266
INITRANS and MAXTRANS 266
Heap Organized Table 266
Index Organized Tables 271
Index Organized Tables Wrap‐up 286
Index Clustered Tables .286
Index Clustered Tables Wrap‐up 295
Hash Cluster Tables .295
Hash Clusters Wrap‐up .306
Nested Tables .306
Nested Tables Syntax . 307
Nested Table Storage . 317
Nested Tables Wrap‐up 320
Temporary Tables 321
Temporary Table Wrap‐up 329
Object Tables 330
Object Table Wrap‐up 339
Summary 339
Chapter 7: Indexes 341
Overview 341
An Overview of Oracle Indexes 342
Expert one-on-one Oracle
6
B*Tree Indexes 343
Reverse Key Indexes 348
Descending Indexes 350
When should you use a B*Tree Index? 351
B*Trees Wrap‐up 360
Bitmap Indexes .361
When Should you use a Bitmap Index? 362
Bitmap Indexes Wrap‐up 364
Function‐Based Indexes 364
Important Implementation Details 365
Function‐Based Index Example 365
Caveat 375
Function‐Based Index Wrap‐up .376
Application Domain Indexes .376
Application Domain Indexes Wrap‐up 378
Frequently Asked Questions About Indexes .378
Do Indexes Work On Views? 378
Indexes and Nulls 378
Indexes on Foreign Keys 382
Why isnʹt my Index Getting Used? 383
Are my Indexes Being Used? . 389
Myth: Space is Never Reused in an Index 390
Myth: Most Discriminating Elements Should be First . 394
Summary 398
Chapter 8: Import and Export 400
Overview 400
A Quick Example .400
Why You Might Use IMP and EXP .402
Detecting Corruption . 402
Extracting DDL 403
Cloning Schemas . 403
Transporting Tablespaces . 403
Rebuilding Instances 403
Copying Data between Platforms . 404
How They Work .404
The Options . 404
Large Exports 409
Subsetting Data 414
Transporting Data 415
Getting the DDL . 421
Backup and Recovery . 429
IMP/EXP is not a Reorganization Tool (Any More) 429
Importing into Different Structures . 430
Expert one-on-one Oracle
7
Direct Path Exports . 435
Caveats and Errors .436
Cloning . 436
Using IMP/EXP Across Versions . 445
Where did my Indexes go? 446
Named versus Default‐Named Constraints 449
National Language Support (NLS) Issues 453
Tables Spanning Multiple Tablespaces 455
Summary 461
Chapter 9: Data Loading .462
Overview 462
An Introduction to SQL*LOADER .462
How to . 469
Load Delimited Data . 469
Load Fixed Format Data . 473
Load Dates 476
Load Data Using Sequences and Other Functions . 477
Update Existing Rows and Insert New Rows . 483
Load Report‐Style Input Data 486
Load a File into a LONG RAW or LONG Field . 489
Load Data with Embedded Newlines . 490
Unload Data . 502
Load LOBs 514
Load VARRAYS/Nested Tables with SQLLDR . 526
Call SQLLDR from a Stored Procedure 529
Caveats .535
You Cannot Pick a Rollback Segment to Use . 535
TRUNCATE Appears to Work Differently . 535
SQLLDR Defaults to CHAR(255) . 535
Command Line Overrides Control File 536
Summary 536
Chapter 10: Tuning Strategies and Tools 537
Overview 537
Identifying the Problem .537
My Approach 539
Tuning is a Constant thing . 540
Bind Variables and Parsing (Again) .545
Am I Using Bind Variables? . 562
Bind Variables and Parsing Wrap‐Up 565
SQL_TRACE, TIMED_STATISTICS, and TKPROF .565
Setting Up Tracing . 566
Using and Interpreting TKPROF Output 569
Using and Interpreting Raw Trace Files . 580
Expert one-on-one Oracle
8
SQL_TRACE, TIMED_STATISTICS, and TKPROF Wrap‐Up .593
DBMS_PROFILER .594
Instrumentation 594
StatsPack 597
Setting up StatsPack 597
StatsPack Wrap‐Up . 618
V$ Tables .619
V$EVENT_NAME 619
V$FILESTAT and V$TEMPSTAT . 620
V$LOCK 620
V$MYSTAT . 620
V$OPEN_CURSOR . 622
V$PARAMETER . 623
V$SESSION . 623
V$SESSION_EVENT 626
V$SESSION_LONGOPS . 627
V$SESSION_WAIT . 627
V$SESSTAT . 627
V$SESS_IO 627
V$SQL, V$SQLAREA . 627
V$STATNAME 628
V$SYSSTAT . 628
V$SYSTEM_EVENT 628
Summary 628
Chapter 11: Optimizer Plan Stability .630
Overview 630
An Overview of the Feature .630
Uses of Optimizer Plan Stability 634
A Method to Implement Tuning 634
A Development Tool . 640
To See the Indexes Used . 642
To See what SQL is Executed by an Application 642
How Optimizer Plan Stability Works 643
OUTLINES and OUTLINE_HINTS . 643
Creating Stored Outlines 646
Privileges Needed for Stored Outlines 646
Using DDL 647
Using ALTER SESSION . 648
The OUTLN User .649
Moving Outlines from Database to Database 650
Getting Just the Right Outline 651
Managing Outlines 654
Via DDL 654
Expert one-on-one Oracle
9
The OUTLN_PKG Package 657
Caveats .661
Outline Names and Case 661
ALTER SESSION Issue 663
DROP USER does not Drop Outlines 663
ʹCURSOR_SHARING = FORCEʹ and Outlines 664
Outlines Use Simple Text Matching . 665
Outlines by Default are in the SYSTEM Tablespace . 666
OR‐Expansion . 666
Performance . 667
The Namespace of Outlines is Global . 672
Errors you Might Encounter .673
ORA‐18001 ʺno options specified for ALTER OUTLINEʺ 673
ORA‐18002 ʺthe specified outline does not existʺ . 674
ORA‐18003 ʺan outline already exists with this signatureʺ . 674
ORA‐18004 ʺoutline already existsʺ . 674
ORA‐18005‐18007 674
Summary 675
Chapter 12: Analytic Functions .676
Overview 676
An Example .676
How Analytic Functions Work 681
The Syntax 681
The Functions 698
Examples 702
The TOP‐N Query 702
Pivot Query . 714
Accessing Rows Around Your Current Row . 723
Caveats .728
PL/SQL and Analytic functions 728
Analytic Functions in the Where Clause . 730
NULLS and Sorting . 731
Performance . 733
Summary 734
Chapter 13: Materialized Views 735
Overview 735
A Brief History 735
What youʹll need to run the Examples .737
An Example .737
Uses of Materialized Views .745
How Materialized Views Work .745
Setting Up . 746
Internal Mechanics . 747
Expert one-on-one Oracle
10
Making sure your View gets used 750
Constraints 750
Dimensions 756
DBMS_OLAP 767
Estimating Size 767
Dimension Validation . 769
Recommending Materialized Views 772
Caveats .774
Materialized Views are Not Designed for OLTP Systems 774
Query Rewrite Integrity . 774
Summary 775
Chapter 14: Partitioning 777
Overview 777
The Uses of Partitioning .777
Increased Availability . 777
Reduced Administrative Burden . 780
Enhanced DML and Query Performance 781
How Partitioning Works .783
Table Partitioning Schemes 784
Partitioning Indexes 789
Summary 813
Chapter 15:Autonomous Transactions .814
Overview 814
An Example .814
Why Use Autonomous Transactions? 817
Auditing that Can Not be Rolled Back 817
A Method to Avoid a Mutating Table . 821
Performing DDL in Triggers . 822
Writing to the Database . 828
To Develop More Modular Code . 839
How They Work .839
Transactional Control . 840
Scope . 842
Ending an Autonomous Transaction 849
Savepoints 850
Caveats .853
No Distributed Transactions . 853
PL/SQL Only 853
The Entire Transaction Rolls Back 853
Transaction‐Level Temporary Tables . 855
Mutating Tables 857
Errors You Might Encounter 860
ORA‐06519 ʺactive autonomous transaction detected and rolled backʺ . 860
Expert one-on-one Oracle
11
ORA‐14450 ʺattempt to access a transactional temp table already in useʺ . 860
ORA‐00060 ʺdeadlock detected while waiting for resourceʺ 861
Summary 861
Chapter 16: Dynamic SQL 862
Overview 862
Dynamic SQL versus Static SQL .862
Why Use Dynamic SQL? 865
How to Use Dynamic SQL .866
DBMS_SQL 866
Native Dynamic SQL . 874
DBMS_SQL versus Native Dynamic SQL 880
Caveats .906
It Breaks the Dependency Chain 907
The Code is More Fragile 908
It is Harder to Tune . 908
Summary 908
Chapter 17: interMedia 910
Overview 910
A Brief History 910
Uses of interMedia Text 911
Searching for Text 912
Managing a Variety of Documents 914
Indexing Text from Many Data Sources . 915
Itʹs an Oracle Database, After All . 919
Generating Themes . 920
Searching XML Applications . 922
How interMedia Text Works .923
interMedia Text Indexing . 927
About ABOUT . 931
Section Searching 932
Caveats .940
It is NOT Document Management 940
Index Synchronization 940
Indexing Information Outside the Database 941
Document Services . 942
The Catalog Index 943
Errors You May Encounter .945
Index Out of Date 946
External Procedure Errors . 946
The Road Ahead .947
Summary 947
Chapter 18: C‐Based External Procedures 949
Overview 949
Expert one-on-one Oracle
12
When Are They Used? .949
How Are They Implemented? .951
Configuring Your Server 953
Verify the extproc Program 956
Verify the Database Environment 956
Verify the Listener 958
The First Test .959
Compile extproc.c Code . 959
Set Up the SCOTT/TIGER Account . 960
Create the demolib Library 961
Installing and Running 962
Our First External Procedure 963
The Wrapper 964
The C Code 976
Building the extproc . 1004
Installing and Running . 1008
LOB to File External Procedure (LOB_IO) 1009
The LOB_IO Call Specification 1010
The LOB_IO Pro*C Code . 1012
Building the extproc . 1017
Installing and Using LOB_IO . 1019
Errors You May Encounter .1025
ORA‐28575 ʺunable to open RPC connection to external procedure agentʺ 1025
ORA‐28576 ʺlost RPC connection to external procedure agentʺ 1026
ORA‐28577 ʺargument %s of external procedure %s has unsupported datatype
%sʺ 1027
ORA‐28578 ʺprotocol error during callback from an external procedureʺ 1027
ORA‐28579 ʺnetwork error during callback from external procedure agentʺ . 1028
ORA‐28580 ʺrecursive external procedures are not supportedʺ 1028
ORA‐28582 ʺa direct connection to this agent is not allowedʺ 1029
ORA‐06520 ʺPL/SQL: Error loading external libraryʺ . 1029
ORA‐06521 ʺPL/SQL: Error mapping functionʺ 1030
ORA‐06523 ʺMaximum number of arguments exceededʺ . 1031
ORA‐06525 ʺLength Mismatch for CHAR or RAW dataʺ 1032
ORA‐06526 ʺUnable to load PL/SQL libraryʺ 1032
ORA‐06527 ʺExternal procedure SQLLIB error: %sʺ 1033
Summary 1033
Chapter 19: Java Stored Procedures 1035
Overview 1035
Why Use Java Stored Procedures? 1035
How They Work .1037
Passing Data 1042
Useful Examples 1053
Expert one-on-one Oracle
13
Possible Errors 1061
ORA‐29549 Java Session State Cleared . 1061
Permissions Errors 1061
ORA‐29531 no method X in class Y 1062
Summary 1063
Chapter 20: Using Object Relational Features 1064
Overview 1064
Reasons for Using These Features 1065
How Object Relational Features Work 1065
Adding Data Types to your System 1066
Adding Data Types Wrap‐Up 1082
Using Types to Extend PL/SQL .1083
Creating a New PL/SQL Data Type 1083
Unique Uses for Collections 1095
Using Types to Extend PL/SQL Wrap‐Up .1101
Object Relational Views .1102
The Types 1102
The O‐R View 1103
Summary 1118
Chapter 21: Fine Grained Access Control 1120
Overview 1120
An Example .1120
Why Use this Feature? .1121
Ease of Maintenance . 1121
Performed in the Server 1122
Avoids Shared User Accounts 1124
Supports Shared User Accounts . 1124
Hosting an Application as an ASP . 1124
How it Works 1125
Example 1: Implementing a Security Policy . 1127
Example 2: Using Application Contexts 1132
Caveats .1154
Referential Integrity . 1154
Cursor Caching . 1160
Export/Import 1168
Debugging . 1172
Errors You Might Encounter 1173
ORA‐28110: policy function or package <function name> has error. 1173
ORA‐28112: failed to execute policy function. . 1175
ORA‐28113: policy predicate has error. . 1176
ORA‐28106: input value for argument #2 is not valid. 1178
Summary 1178
Chapter 22: n‐Tier Authentication 1180
Expert one-on-one Oracle
14
Overview 1180
Why Use n‐Tier Authentication? 1180
The Mechanics of n‐Tier Authentication 1183
Granting the Privilege . 1194
Auditing Proxy Accounts 1195
Caveats .1196
Summary 1198
Chapter 23: Invoker and Definer Rights .1199
Overview 1199
An Example .1199
When to Use Invoker Rights 1203
Developing Generic Utilities 1203
Data Dictionary Applications . 1208
Generic Object Types 1211
Implementing your own Access Control 1212
When to Use Definer Rights 1215
Performance and Scalability 1215
Security 1216
How they Work .1217
Definer Rights 1217
Definer Rights and Roles . 1221
Invoker Rights 1222
Caveats .1235
Invoker Rights and Shared Pool Utilization . 1235
Performance 1239
Code must be more Robust in Handling Errors 1242
Side Effects of Using SELECT * 1244
Beware of the ʹHiddenʹ Columns 1245
Java and Invoker Rights 1247
Errors You Might Encounter 1254
Summary 1254
Appendix AA: Necessary Supplied Packages 1256
Overview 1256
Why Use the Supplied Packages? .1257
About The Supplied Packages 1257
Appendix AB: DBMS_ALERT and DBMS_PIPE .1260
Overview 1260
Why You Might Use Them . 1260
Set Up . 1261
DBMS_ALERT 1261
Concurrent Signals by More than One Session 1264
Repeated Calls to Signal by a Session 1266
Many Calls to Signal by Many Sessions before a Wait Routine is Called 1267
Expert one-on-one Oracle
15
Summary 1268
DBMS_PIPE 1268
Pipe Servers versus External Routines 1271
Online Example . 1273
Summary 1273
Appendix AC: DBMS_APPLICATION_INFO .1274
Overview 1274
Using the Client Info .1275
Using V$SESSION_LONGOPS 1278
Summary 1283
Appendix AD: DBMS_JAVA .1284
Overview 1284
LONGNAME and SHORTNAME 1284
Setting Compiler Options 1285
SET_OUTPUT .1289
loadjava and dropjava .1290
Permission Procedures 1291
Summary 1293
Appendix AE: DBMS_JOB .1294
Overview 1294
Running a Job Once .1298
Ongoing Jobs 1302
Custom Scheduling 1305
Monitoring the Jobs and Finding the Errors .1307
Summary 1310
Appendix AF: DBMS_LOB 1311
Overview 1311
How do I Load LOBs? .1312
substr 1312
SELECT FOR UPDATE and Java 1313
Conversions .1316
From BLOB to VARCHAR2 and Back Again 1316
Converting From LONG/LONG RAW to a LOB . 1321
Performing a Mass One‐Time Conversion Illustration 1323
Performing an ʹon the flyʹ Conversion 1327
How to Write a BLOB/CLOB to Disk .1330
Displaying a LOB on the Web Using PL/SQL 1331
Summary 1333
Appendix AG: DBMS_LOCK 1334
Overview 1334
Summary 1338
Appendix AH: DBMS_LOGMNR 1339
Overview 1339
Expert one-on-one Oracle
16
Overview 1341
Step 1: Creating the Data Dictionary . 1341
Step 2: Using Log Miner 1345
Options and Usage .1352
Using Log Miner to Find Out When .1355
PGA Usage .1357
Log Miner Limits 1359
Oracle Object Types . 1359
Chained or Migrated Rows . 1363
Other limits 1366
V$LOGMNR_CONTENTS 1367
Summary 1370
Appendix AI: DBMS_OBFUSCATION_TOOLKIT 1372
Overview 1372
The Wrapper .1374
Caveats .1392
Key Management .1394
The Client Application Manages and Stores Keys 1394
Store the Keys in the Same Database . 1395
Store the Keys in the File System with the Database 1396
Summary 1397
Appendix AJ: DBMS_OUTPUT 1398
Overview 1398
How DBMS_OUTPUT Works .1399
DBMS_OUTPUT and Other Environments 1404
Getting Around the Limits .1409
Using A Small Wrapper Function or Another Package . 1409
Creating DBMS_OUTPUT Functionality 1411
Summary 1418
Appendix AK: DBMS_PROFILER 1419
Overview 1419
Caveats .1431
Summary 1432
Appendix AL: DBMS_UTILITY 1433
Overview 1433
COMPILE_SCHEMA 1433
ANALYZE_SCHEMA 1438
ANALYZE_SCHEMA with a Changing Schema . 1440
ANALYZE_SCHEMA does not Analyze Everything . 1440
ANALYZE_DATABASE .1442
FORMAT_ERROR_STACK .1442
FORMAT_CALL_STACK 1444
GEIME .1448
Expert one-on-one Oracle
17
GET_PARAMETER_VALUE .1449
NAME_RESOLVE 1450
NAME_TOKENIZE .1453
COMMA_TO_TABLE, TABLE_TO_COMMA 1457
DB_VERSION and PORT_STRING 1459
GET_HASH_VALUE .1460
Summary 1466
Appendix AM: UTL_FILE 1467
Overview 1467
The UTL_FILE_DIR init.ora parameter .1467
Accessing Mapped Windows Drives .1469
Handling Exceptions .1471
Dumping a Web Page to Disk .1472
1023 Byte Limit .1473
Reading A Directory 1474
Summary 1476
Appendix AN: UTL_HTTP .1477
Overview 1477
UTL_HTTP Functionality .1477
Adding SSL to UTL_HTTP 1480
Really Using UTL_HTTP 1487
A Better UTL_HTTP 1490
Summary 1501
Appendix AO: UTL_RAW 1503
Overview 1503
Appendix AP: UTL_SMTP and Sending Mail .1506
Overview 1506
UTL_SMTP ‐ a larger example .1506
Loading and using the JavaMail API .1512
Summary 1521
Appendix AQ: UTL_TCP 1522
Overview 1522
The SocketType 1523
Summary 1537
Appendix B: Support, Errata and p2p.wrox.com 1539
Overview 1539
The Online Forums at p2p.wrox.com .1539
How To Enroll For Support . 1539
Why This System Offers The Best Support 1540
Checking the Errata Online at http://www.wrox.com/ .1541
Finding an Erratum on the Web Site . 1541
Add an Erratum 1541
How to Tell Us Exactly What You Think 1543