Математика	Физика	Химия	Биология	Техника и технологии
	Expert one-on-one Oracle for 7.3-8.1.7 - Thomas Kyte
Expert one-on-one Oracle for 7.3-8.1.7 - Thomas Kyte 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 SQLPLUS Environment ..............................................................................................28 Setting up AUTOTRACE in SQLPLUS .................................. 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 BTree Indexes......................................................................................................................343 Reverse Key Indexes.................................................. 348 Descending Indexes .................................................. 350 When should you use a BTree Index? .................................. 351 BTrees 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 SQLLOADER...................................................................................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 ProC 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 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 GET_TIME...........................................................................................................................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 Цена: 200руб.
Назад	Заказ	На главную страницу