Oracle 9i Rel 2 Db Utilites

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