Oracle 9i Rel 2 Db Utilites

Description

Contents......Page 3 List of Examples......Page 28 List of Figures......Page 30 List of Tables......Page 31 Send Us Your Comments......Page 33 Audience......Page 35 Organization......Page 36 Related Documentation......Page 39 Conventions......Page 40 Oracle9i Utilities New Features for Release 9.2......Page 43 Oracle9i Utilities New Features for Release 9.0.1......Page 45 Oracle8i Utilities New Features......Page 48 Part I Export and Import......Page 53 1 Export......Page 55 What Is the Export Utility?......Page 56 Running catexp.sql or catalog.sql......Page 57 Verifying Access Privileges......Page 58 Command-Line Entries......Page 59 Parameter Files......Page 60 Invoking Export As SYSDBA......Page 61 Export Modes......Page 62 Partition-Level Export......Page 66 Export Parameters......Page 67 BUFFER......Page 70 COMPRESS......Page 71 CONSISTENT......Page 72 FILE......Page 74 FILESIZE......Page 75 FLASHBACK_TIME......Page 76 LOG......Page 77 QUERY......Page 78 Restrictions......Page 79 RESUMABLE_NAME......Page 80 STATISTICS......Page 81 TABLES......Page 82 Table Name Restrictions......Page 83 TABLESPACES......Page 84 USERID (username/password)......Page 85 Example Export Sessions......Page 86 Example Export Session in Full Database Mode......Page 87 Example Export Session in User Mode......Page 90 Example 1: DBA Exporting Tables for Two Users......Page 92 Example 2: User Exports Tables That He Owns......Page 93 Example 3: Using Pattern Matching to Export Various Tables......Page 94 Example 2: Exporting a Table with a Specified Partition......Page 95 Example 3: Exporting a Composite Partition......Page 96 Using the Interactive Method......Page 97 Warning Messages......Page 102 Exit Codes for Inspection and Display......Page 103 Conventional Path Export Versus Direct Path Export......Page 104 Security Considerations for Direct Path Exports......Page 106 Exporting and Importing with Oracle Net......Page 107 Effect of Character Set Sorting Order on Conversions......Page 108 Instance Affinity and Export......Page 109 Exporting Offline Bitmapped Tablespaces......Page 110 Exporting Object Type Definitions......Page 111 Exporting Synonyms......Page 112 Transportable Tablespaces......Page 113 Using Export and Import to Partition a Database Migration......Page 114 Using Different Releases and Versions of Export......Page 115 Restrictions When Using Different Releases and Versions of Export and Import......Page 116 Examples of Using Different Releases of Export and Import......Page 117 Creating Oracle Release 8.0 Export Files from an Oracle9i Database......Page 118 EXP-37......Page 119 2 Import......Page 121 What Is the Import Utility?......Page 122 Table Objects: Order of Import......Page 123 Before Using Import......Page 124 Verifying Access Privileges......Page 125 Importing Objects into Your Own Schema......Page 126 Importing System Objects......Page 127 Disabling Referential Constraints......Page 128 Effect of Schema and Database Triggers on Import Operations......Page 129 Interactive Import Prompts......Page 130 Parameter Files......Page 131 Invoking Import As SYSDBA......Page 132 Import Modes......Page 133 Import Parameters......Page 134 CHARSET......Page 138 COMPILE......Page 139 FEEDBACK......Page 140 FILESIZE......Page 141 GRANTS......Page 142 IGNORE......Page 143 INDEXFILE......Page 144 RECORDLENGTH......Page 145 RESUMABLE_TIMEOUT......Page 146 STATISTICS......Page 147 STREAMS_INSTANTIATION......Page 148 TABLES......Page 149 Table Name Restrictions......Page 150 TOID_NOVALIDATE......Page 151 TOUSER......Page 152 USERID (username/password)......Page 153 Example Import Sessions......Page 154 Example Import of Selected Tables for a Specific User......Page 155 Example Import of Tables Exported by Another User......Page 156 Example Import of Tables from One User to Another......Page 157 Example 1: A Partition-Level Import......Page 158 Example 2: A Partition-Level Import of a Composite Partitioned Table......Page 159 Example 3: Repartitioning a Table on a Different Column......Page 160 Example Import of Using Pattern Matching to Import Various Tables......Page 163 Using the Interactive Method......Page 164 Warning, Error, and Completion Messages......Page 165 Completion Messages......Page 166 Row Errors......Page 167 Object Already Exists......Page 168 Table-Level and Partition-Level Import......Page 169 Guidelines for Using Partition-Level Import......Page 170 Migrating Data Across Partitions and Tables......Page 171 Index Creation and Maintenance Controls......Page 172 Reducing Database Fragmentation......Page 173 Character Set and Globalization Support Considerations......Page 174 Import and Single-Byte Character Sets......Page 175 Importing Object Identifiers......Page 176 Importing Nested Tables......Page 178 Importing BFILE Columns and Directory Aliases......Page 179 Importing External Tables......Page 180 Importing Views......Page 181 Support for Fine-Grained Access Control......Page 182 Snapshot Log......Page 183 Transportable Tablespaces......Page 184 Storage Parameters......Page 185 Read-Only Tablespaces......Page 186 Reorganizing Tablespaces......Page 187 Importing Statistics......Page 188 How to Use Export and Import to Partition a Database Migration......Page 189 Check Constraints on DATE Columns......Page 190 Length of Default Column Values......Page 191 Restrictions When Using Different Releases and Versions of Export and Import......Page 192 The CHARSET Parameter......Page 193 Part II SQL*Loader......Page 195 SQL*Loader Features......Page 197 SQL*Loader Control File......Page 199 Fixed Record Format......Page 200 Variable Record Format......Page 201 Stream Record Format......Page 202 Logical Records......Page 203 Data Fields......Page 204 Data Conversion and Datatype Specification......Page 205 Oracle Rejects......Page 206 Conventional Path Loads, Direct Path Loads, and External Table Loads......Page 207 Parallel Direct Path......Page 208 column-objects......Page 209 Supported LOB Types......Page 210 Application Development: Direct Path Load API......Page 211 Invoking SQL*Loader......Page 213 BAD (bad file)......Page 215 CONTROL (control file)......Page 216 DATE_CACHE......Page 217 ERRORS (errors to allow)......Page 218 EXTERNAL_TABLE......Page 219 Restrictions When Using EXTERNAL_TABLE......Page 220 MULTITHREADING......Page 221 READSIZE (read buffer size)......Page 222 RESUMABLE......Page 223 SILENT (feedback mode)......Page 224 SKIP (records to skip)......Page 225 SKIP_UNUSABLE_INDEXES......Page 226 USERID (username/password)......Page 227 Exit Codes for Inspection and Display......Page 228 5 SQL*Loader Control File Reference......Page 231 Control File Contents......Page 232 OPTIONS Clause......Page 234 Operating System Considerations......Page 235 Nonportable Strings......Page 236 Specifying Datafiles......Page 237 Specifying Multiple Datafiles......Page 239 Identifying Data in the Control File with BEGINDATA......Page 240 Specifying the Bad File......Page 241 Criteria for Rejected Records......Page 243 Specifying the Discard File in the Control File......Page 244 Criteria for Discarded Records......Page 245 Handling Different Character Encoding Schemes......Page 246 Unicode Character Sets......Page 247 Datafile Character Sets......Page 248 CHARACTERSET Parameter......Page 249 Control File Character Set......Page 251 Character-Length Semantics......Page 252 Load Discontinued Because of Space Errors......Page 254 Load Discontinued Because a Control+C Was Issued......Page 255 Continuing Single-Table Loads......Page 256 Using CONTINUEIF to Assemble Logical Records......Page 257 Loading Logical Records into Tables......Page 261 Table-Specific Loading Method......Page 262 Loading Data into Nonempty Tables......Page 263 Table-Specific OPTIONS Parameter......Page 264 Using the WHEN Clause with LOBFILEs and SDFs......Page 265 enclosure_spec......Page 266 TRAILING NULLCOLS Clause......Page 267 SINGLEROW Option......Page 268 Extracting Multiple Logical Records......Page 269 Distinguishing Different Input Record Formats......Page 270 Distinguishing Different Input Row Object Subtypes......Page 271 Summary......Page 273 Performance Implications of Bind Arrays......Page 274 Calculations to Determine Bind Array Size......Page 275 Calculating the Size of Field Buffers......Page 277 Minimizing Memory Requirements for Bind Arrays......Page 279 Calculating Bind Array Size for Multiple INTO TABLE Clauses......Page 280 Field List Contents......Page 281 Specifying the Position of a Data Field......Page 283 Examples of Using POSITION......Page 284 Specifying Columns and Fields......Page 285 Specifying Filler Fields......Page 286 SQL*Loader Datatypes......Page 287 INTEGER(n)......Page 288 FLOAT......Page 289 ZONED......Page 290 DECIMAL......Page 291 VARGRAPHIC......Page 292 VARCHAR......Page 293 Portable Datatypes......Page 294 CHAR......Page 295 Datetime and Interval Datatypes......Page 296 GRAPHIC......Page 298 Numeric EXTERNAL......Page 299 VARCHARC......Page 300 Conflicting Native Datatype Field Lengths......Page 301 Datatype Conversions......Page 302 Datatype Conversions for Datetime and Interval Datatypes......Page 303 Specifying Delimiters......Page 304 ENCLOSED Fields......Page 305 Delimiter Marks in the Data......Page 306 Loading Trailing Blanks with Delimiters......Page 307 Delimited Fields......Page 308 Specifying Field Conditions......Page 309 Comparing Fields to BLANKS......Page 311 Using the WHEN, NULLIF, and DEFAULTIF Clauses......Page 312 Loading Data Across Different Platforms......Page 316 Byte Ordering......Page 317 Specifying Byte Order......Page 318 Using Byte Order Marks (BOMs)......Page 319 Loading All-Blank Fields......Page 321 Trimming Whitespace......Page 322 Datatypes for Which Whitespace Can Be Trimmed......Page 324 Delimited Fields......Page 325 Previous Field Terminated by a Delimiter......Page 326 Previous Field Terminated by Whitespace......Page 327 Optional Enclosure Delimiters......Page 328 PRESERVE BLANKS Option......Page 329 Applying SQL Operators to Fields......Page 330 Referencing Fields......Page 332 Using SQL Strings with a Date Mask......Page 333 Using SQL*Loader to Generate Data for Input......Page 334 CONSTANT Parameter......Page 335 SYSDATE Parameter......Page 336 SEQUENCE Parameter......Page 337 Example: Generating Different Sequence Numbers for Each Insert......Page 338 Loading Column Objects......Page 341 Loading Column Objects in Stream Record Format......Page 342 Loading Column Objects in Variable Record Format......Page 343 Loading Column Objects with a Derived Subtype......Page 344 Specifying Attribute Nulls......Page 346 Specifying Atomic Nulls......Page 347 Loading Column Objects with User-Defined Constructors......Page 348 Loading Object Tables......Page 352 Loading Object Tables with a Subtype......Page 353 Real REF Columns......Page 355 Unscoped REF Columns That Allow Primary Keys......Page 356 Loading LOBs......Page 358 LOB Data in Predetermined Size Fields......Page 359 LOB Data in Delimited Fields......Page 360 LOB Data in Length-Value Pair Fields......Page 361 Loading LOB Data from an External LOBFILE (BFILE)......Page 362 Loading LOB Data from LOBFILEs......Page 363 Examples of Loading LOB Data from LOBFILEs......Page 364 Considerations When Loading LOBs from LOBFILEs......Page 368 Loading Collections (Nested Tables and VARRAYs)......Page 369 Restrictions in Nested Tables and VARRAYs......Page 370 Secondary Datafiles (SDFs)......Page 371 Loading a Parent Table Separately from Its Child Table......Page 373 Memory Issues When Loading VARRAY Columns......Page 374 Header Information......Page 377 Table Information......Page 378 Position......Page 379 Datafile Information......Page 380 Summary Statistics......Page 381 Oracle Statistics That Are Logged......Page 382 Additional Summary Statistics for Direct Path Loads and Multithreading......Page 383 Log File Created When EXTERNAL_TABLE=GENERATE_ONLY......Page 384 Data Loading Methods......Page 387 When to Use a Conventional Path Load......Page 390 Direct Path Load......Page 391 Direct Path Load of a Partitioned or Subpartitioned Table......Page 392 Direct Path Load of a Single Partition or Subpartition......Page 393 Restrictions on Using Direct Path Loads......Page 394 When to Use a Direct Path Load......Page 395 Setting Up for Direct Path Loads......Page 396 Improving Performance......Page 397 Indexes Left in an Unusable State......Page 398 Using Data Saves to Protect Against Data Loss......Page 399 Data Save Versus Commit......Page 400 Instance Recovery and Direct Path Loads......Page 401 Loading Data As PIECED......Page 402 Preallocating Storage for Faster Loading......Page 403 Unsorted Data......Page 404 Choosing the Best Sort Order......Page 405 Specifying the UNRECOVERABLE Parameter......Page 406 Specifying the Number of Column Array Rows and Size of Stream Buffers......Page 407 Specifying a Value for the Date Cache......Page 408 Optimizing Direct Path Loads on Multiple-CPU Systems......Page 409 Avoiding Index Maintenance......Page 410 Disabled Constraints......Page 411 Reenable Constraints......Page 412 When Automatic Constraints Cannot Be Used......Page 413 Duplicating the Effects of Exception Conditions......Page 414 Using a Stored Procedure......Page 415 Parallel Data Loading Models......Page 416 Intrasegment Concurrency with Direct Path......Page 417 Initiating Multiple SQL*Loader Sessions......Page 418 Specifying Temporary Segments......Page 419 Enabling Constraints After a Parallel Direct Path Load......Page 420 General Performance Improvement Hints......Page 421 10 SQL*Loader Case Studies......Page 423 The Case Studies......Page 424 Case Study Files......Page 425 Checking the Results of a Load......Page 426 Control File for Case Study 1......Page 427 Running Case Study 1......Page 428 Log File for Case Study 1......Page 429 Control File for Case Study 2......Page 430 Running Case Study 2......Page 431 Log File for Case Study 2......Page 432 Control File for Case Study 3......Page 433 Log File for Case Study 3......Page 435 Case Study 4: Loading Combined Physical Records......Page 436 Control File for Case Study 4......Page 437 Running Case Study 4......Page 438 Log File for Case Study 4......Page 439 Case Study 5: Loading Data into Multiple Tables......Page 440 Control File for Case Study 5......Page 441 Running Case Study 5......Page 442 Log File for Case Study 5......Page 443 Loaded Tables for Case Study 5......Page 445 Case Study 6: Loading Data Using the Direct Path Load Method......Page 446 Datafile for Case Study 6......Page 447 Log File for Case Study 6......Page 448 Creating a BEFORE INSERT Trigger......Page 450 Control File for Case Study 7......Page 451 Running Case Study 7......Page 453 Log File for Case Study 7......Page 454 Control File for Case Study 8......Page 456 Datafile for Case Study 8......Page 457 Running Case Study 8......Page 458 Log File for Case Study 8......Page 459 Case Study 9: Loading LOBFILEs (CLOBs)......Page 460 Control File for Case Study 9......Page 461 Datafiles for Case Study 9......Page 462 Running Case Study 9......Page 463 Log File for Case Study 9......Page 464 Control File for Case Study 10......Page 465 Log File for Case Study 10......Page 467 Case Study 11: Loading Data in the Unicode Character Set......Page 469 Control File for Case Study 11......Page 470 Running Case Study 11......Page 471 Log File for Case Study 11......Page 472 Loaded Tables for Case Study 11......Page 474 Part III External Tables......Page 475 11 External Tables Concepts......Page 477 The Access Driver......Page 478 Location of Datafiles and Output Files......Page 479 Using External Tables to Load Data......Page 481 Performance Hints When Using External Tables......Page 482 Multiple Primary Input Datafiles......Page 483 Default Character Sets and Date Masks......Page 484 12 External Tables Access Parameters......Page 485 access_parameters Clause......Page 486 record_format_info Clause......Page 487 FIXED length......Page 488 VARIABLE size......Page 489 DELIMITED BY......Page 490 DATA IS...ENDIAN......Page 491 LOAD WHEN......Page 492 DISCARDFILE | NODISCARDFILE......Page 493 READSIZE......Page 494 string......Page 495 [directory object name:] filename......Page 496 condition......Page 497 range start : range end......Page 498 field_definitions Clause......Page 499 delim_spec......Page 500 Example: External Table with Enclosure and Terminator Delimiters......Page 502 trim_spec......Page 503 MISSING FIELD VALUES ARE NULL......Page 504 field_list......Page 505 pos_spec Clause......Page 506 length......Page 507 datatype_spec Clause......Page 508 ORACLE_NUMBER......Page 510 CHAR......Page 511 date_format_spec......Page 512 VARCHAR and VARRAW......Page 513 VARCHARC and VARRAWC......Page 514 init_spec Clause......Page 515 Part IV Other Utilities......Page 517 13 DBVERIFY: Offline Database Verification Utility......Page 519 Parameters......Page 520 Sample DBVERIFY Output......Page 521 Using DBVERIFY to Validate a Segment......Page 522 Parameters......Page 523 Command-Line Interface......Page 524 What Is the DBNEWID Utility?......Page 525 Changing the DBID and Database Name......Page 526 Changing Only the Database Name......Page 528 Troubleshooting a DBID Change Operation......Page 529 Troubleshooting a Database Name Change Operation......Page 530 Parameters......Page 531 Restrictions and Usage Notes......Page 532 Changing Only the Database Name......Page 533 15 Using the Metadata API......Page 535 Metadata API Components......Page 536 Internet Computing......Page 537 DBMS_METADATA Programmatic Interface......Page 538 Using the DBMS_METADATA.FETCH_XML Procedure......Page 540 Using the DBMS_METADATA.FETCH_DDL Procedure......Page 541 Performance Tips for the Programmatic Interface of the Metadata API......Page 543 DBMS_METADATA Browsing Interface......Page 544 mddemo.sql......Page 545 PAYROLL_DEMO Output......Page 551 Part V Appendixes......Page 555 A SQL*Loader Syntax Diagrams......Page 557 Using the DB2 RESUME Option......Page 569 SORTDEVT and SORTNUM Statements......Page 570 PART Statement......Page 571 SQL*Loader Syntax with DB2-Compatible Statements......Page 572 C Backus-Naur Form Syntax......Page 575 Index......Page 577