Oracle9i Database Utilities

This document describes how to use the Oracle9i database utilities for data transfer, data maintenance, and database administration.This document is for database administrators (DBAs), application programmers, security administrators, system operators, and other Oracle users who perform the following tasks:■ Archive data, back up an Oracle database, or move data between Oracle databases using the Export/Import utilities■ Load data into Oracle tables from operating system files using SQL*Loader■ Create and maintain user-defined character sets using Oracle’s globalization technology supportTo use this manual, you need a working knowledge of SQL and Oracle fundamentals, information that is contained in 0raclc9i Database Concepts. In addition, SQL*Loader requires that you know how to use the file management facilities of your operating system.

---

Table of contents :
Contents……Page 3
Send Us Your Comments……Page 27
Preface……Page 29
Organization……Page 30
Related Documentation……Page 32
Conventions……Page 33
Accessibility of Code Examples in Documentation……Page 36
Oracle9i Utilities New Features……Page 37
Oracle8i Utilities New Features……Page 40
Part I Export and Import……Page 43
1 Export……Page 45
What Is the Export Utility?……Page 46
Running catexp.sql or catalog.sql……Page 47
Verifying Access Privileges……Page 48
Interactive Export Prompts……Page 49
Parameter Files……Page 50
Invoking Export As SYSDBA……Page 51
Export Modes……Page 52
Getting Online Help……Page 56
Export Parameters……Page 57
Example: Calculating Buffer Size……Page 59
CONSISTENT……Page 60
FEEDBACK……Page 62
FILESIZE……Page 63
FLASHBACK_SCN……Page 64
LOG……Page 65
QUERY……Page 66
Restrictions……Page 67
RESUMABLE_NAME……Page 68
STATISTICS……Page 69
TABLES……Page 70
Table Name Restrictions……Page 71
TABLESPACES……Page 72
USERID (username/password)……Page 73
Example Export Sessions……Page 74
Example Export Session in Full Database Mode……Page 75
Example Export Session in User Mode……Page 78
Example Export Sessions in Table Mode……Page 79
Example 2: User Exports Tables That He Owns……Page 80
Example 3: Using Pattern Matching to Export Various Tables……Page 81
Example 1: Exporting a Table Without Specifying a Partition……Page 82
Example 2: Exporting a Table with a Specified Partition……Page 83
Example 3: Exporting a Composite Partition……Page 84
Using the Interactive Method……Page 85
Log File……Page 89
Exit Codes for Inspection and Display……Page 90
Conventional Path Export Versus Direct Path Export……Page 91
Security Considerations for Direct Path Exports……Page 93
Exporting and Importing with Oracle Net……Page 94
Effect of Character Set Sorting Order on Conversions……Page 95
Instance Affinity and Export……Page 96
Exporting Foreign Function Libraries……Page 97
Exporting Object Type Definitions……Page 98
Exporting Synonyms……Page 99
Transportable Tablespaces……Page 100
Advantages of Partitioning a Migration……Page 101
Using a Previous Version of Export……Page 102
Creating Oracle Release 8.0 Export Files from an Oracle9i Database……Page 103
2 Import……Page 105
What Is the Import Utility?……Page 106
Table Objects: Order of Import……Page 107
Before Using Import……Page 108
Importing Objects into Your Own Schema……Page 109
Importing System Objects……Page 111
Manually Creating Tables Before Importing Data……Page 112
Effect of Schema and Database Triggers on Import Operations……Page 113
Command-Line Entries……Page 114
Parameter Files……Page 115
Invoking Import As SYSDBA……Page 116
Import Modes……Page 117
Import Parameters……Page 118
CHARSET……Page 122
COMPILE……Page 123
FEEDBACK……Page 124
FILESIZE……Page 125
GRANTS……Page 126
IGNORE……Page 127
INDEXFILE……Page 128
RECORDLENGTH……Page 129
ROWS……Page 130
STATISTICS……Page 131
TABLES……Page 132
Table Name Restrictions……Page 133
TOID_NOVALIDATE……Page 135
TTS_OWNERS……Page 136
VOLSIZE……Page 137
Example Import of Selected Tables for a Specific User……Page 138
Example Import of Tables Exported by Another User……Page 139
Example Import of Tables from One User to Another……Page 140
Example 1: A Partition-Level Import……Page 141
Example 2: A Partition-Level Import of a Composite Partitioned Table……Page 142
Example 3: Repartitioning a Table on a Different Column……Page 143
Import Messages……Page 146
Using the Interactive Method……Page 147
Nonrecoverable Error Messages……Page 149
Error Handling During an Import……Page 150
Errors Importing Database Objects……Page 151
Domain Index Metadata……Page 152
Guidelines for Using Partition-Level Import……Page 153
Migrating Data Across Partitions and Tables……Page 154
Delaying Index Creation……Page 155
Example of Postponing Index Maintenance……Page 156
Exporting and Importing with Oracle Net……Page 157
Data Definition Language (DDL)……Page 158
Importing Object Identifiers……Page 159
Importing Nested Tables……Page 161
Importing BFILE Columns and Directory Aliases……Page 162
Importing Java Objects……Page 163
Importing Views……Page 164
Support for Fine-Grained Access Control……Page 165
Snapshot Log……Page 166
Importing a Snapshot into a Different Schema……Page 167
Storage Parameters……Page 168
The Export COMPRESS Parameter……Page 169
Reorganizing Tablespaces……Page 170
Importing Statistics……Page 171
Disadvantages of Partitioning a Migration……Page 172
Check Constraints on DATE Columns……Page 173
Length of Default Column Values……Page 174
The CHARSET Parameter……Page 175
Part II SQL*Loader……Page 177
SQL*Loader Features……Page 179
SQL*Loader Control File……Page 181
Fixed Record Format……Page 182
Variable Record Format……Page 183
Stream Record Format……Page 184
Data Fields……Page 186
LOBFILEs and Secondary Data Files (SDFs)……Page 187
Oracle Rejects……Page 188
Conventional Path Loads, Direct Path Loads, and External Table Loads……Page 189
Parallel Direct Path……Page 190
column-objects……Page 191
Supported LOB Types……Page 192
Application Development: Direct Path Load API……Page 193
Invoking SQL*Loader……Page 195
BINDSIZE (maximum size)……Page 197
DATA (datafile)……Page 198
ERRORS (errors to allow)……Page 199
EXTERNAL_TABLE……Page 200
LOG (log file)……Page 201
PARFILE (parameter file)……Page 202
RESUMABLE……Page 203
ROWS (rows per commit)……Page 204
SILENT (feedback mode)……Page 205
SKIP_UNUSABLE_INDEXES……Page 206
USERID (username/password)……Page 207
Exit Codes for Inspection and Display……Page 208
5 SQL*Loader Control File Reference……Page 211
Control File Contents……Page 212
OPTIONS Clause……Page 214
Specifying SQL Strings……Page 215
Nonportable Strings……Page 216
Specifying Datafiles……Page 217
Specifying Multiple Datafiles……Page 219
Identifying Data in the Control File with BEGINDATA……Page 220
Specifying the Bad File……Page 221
Criteria for Rejected Records……Page 223
Specifying the Discard File in the Control File……Page 224
Criteria for Discarded Records……Page 225
Handling Different Character Encoding Schemes……Page 226
Unicode Character Sets……Page 227
Datafile Character Sets……Page 228
CHARACTERSET Parameter……Page 229
Control File Character Set……Page 231
Character-Length Semantics……Page 232
Continuing Multiple-Table Conventional Loads……Page 234
Combining SKIP and CONTINUE_LOAD……Page 235
Using CONTINUEIF to Assemble Logical Records……Page 236
INTO TABLE Clause……Page 240
Loading Data into Nonempty Tables……Page 241
Loading Records Based on a Condition……Page 243
termination_spec……Page 244
Handling Short Records with Missing Data……Page 245
Index Options……Page 246
Benefits of Using Multiple INTO TABLE Clauses……Page 247
Relative Positioning Based on Delimiters……Page 248
Relative Positioning Based on the POSITION Parameter……Page 249
Distinguishing Different Input Row Object Subtypes……Page 250
Bind Arrays and Conventional Path Loads……Page 252
Specifying Number of Rows Versus Size of Bind Array……Page 253
Calculations to Determine Bind Array Size……Page 254
Determining the Size of the Length Indicator……Page 255
Calculating the Size of Field Buffers……Page 256
Minimizing Memory Requirements for Bind Arrays……Page 257
Calculating Bind Array Size for Multiple INTO TABLE Clauses……Page 258
Field List Contents……Page 259
Specifying the Position of a Data Field……Page 261
Using POSITION with Multiple Table Loads……Page 262
Specifying Columns and Fields……Page 263
Specifying Filler Fields……Page 264
SQL*Loader Datatypes……Page 265
INTEGER(n)……Page 266
FLOAT……Page 267
ZONED……Page 268
VARGRAPHIC……Page 269
VARCHAR……Page 271
Portable Datatypes……Page 272
Datetime and Interval Datatypes……Page 273
GRAPHIC EXTERNAL……Page 276
Numeric EXTERNAL……Page 277
VARCHARC……Page 278
Conflicting Native Datatype Field Lengths……Page 279
Datatype Conversions……Page 280
Specifying Delimiters……Page 281
ENCLOSED Fields……Page 282
Maximum Length of Delimited Data……Page 284
Predetermined Size Fields……Page 285
Specifying Field Conditions……Page 286
Comparing Fields to BLANKS……Page 288
Using the WHEN, NULLIF, and DEFAULTIF Clauses……Page 289
Loading Data Across Different Platforms……Page 293
Byte Ordering……Page 294
Specifying Byte Order……Page 295
Using Byte Order Marks (BOMs)……Page 296
Suppressing Checks for BOMs……Page 298
Trimming Whitespace……Page 299
Datatypes for Which Whitespace Can Be Trimmed……Page 301
Delimited Fields……Page 302
Previous Field Terminated by a Delimiter……Page 303
Previous Field Terminated by Whitespace……Page 304
Optional Enclosure Delimiters……Page 305
PRESERVE BLANKS Option……Page 306
Applying SQL Operators to Fields……Page 307
Referencing Fields……Page 309
Using SQL Strings with a Date Mask……Page 310
Using SQL*Loader to Generate Data for Input……Page 311
CONSTANT Parameter……Page 312
SYSDATE Parameter……Page 313
SEQUENCE Parameter……Page 314
Example: Generating Sequence Numbers……Page 315
Loading Column Objects……Page 317
Loading Column Objects in Stream Record Format……Page 318
Loading Column Objects in Variable Record Format……Page 319
Loading Column Objects with a Derived Subtype……Page 320
Specifying Attribute Nulls……Page 322
Specifying Atomic Nulls……Page 323
Loading Object Tables……Page 324
Loading Object Tables with a Subtype……Page 326
Real REF Columns……Page 327
Primary Key REF Columns……Page 328
Loading LOB Data from a Primary Datafile……Page 329
LOB Data in Delimited Fields……Page 330
LOB Data in Length-Value Pair Fields……Page 332
Loading LOB Data from an External LOBFILE (BFILE)……Page 333
Loading LOB Data from LOBFILEs……Page 334
Examples of Loading LOB Data from LOBFILEs……Page 335
Considerations When Loading LOBs from LOBFILEs……Page 339
Loading Collections (Nested Tables and VARRAYs)……Page 340
Restrictions in Nested Tables and VARRAYs……Page 341
Secondary Datafiles (SDFs)……Page 342
Loading a Parent Table Separately from Its Child Table……Page 344
Memory Issues When Loading VARRAY Columns……Page 345
Header Information……Page 347
Table Information……Page 348
Position……Page 349
Datafile Information……Page 350
Summary Statistics……Page 351
Information About Single-Partition Loads……Page 352
Log File Created When EXTERNAL_TABLE=GENERATE_ONLY……Page 353
Data Loading Methods……Page 357
Conventional Path Load of a Single Partition……Page 358
When to Use a Conventional Path Load……Page 359
Direct Path Load……Page 360
Advantages of a Direct Path Load……Page 362
Restrictions on Using Direct Path Loads……Page 363
Integrity Constraints……Page 364
Specifying a Direct Path Load……Page 365
Temporary Segment Storage Requirements……Page 366
Indexes Left in Unusable State……Page 367
Using the ROWS Parameter……Page 368
Media Recovery and Direct Path Loads……Page 369
Loading Data As PIECED……Page 370
Optimizing Performance of Direct Path Loads……Page 371
SORTED INDEXES Clause……Page 372
Choosing the Best Sort Order……Page 373
Disabling Archiving……Page 374
Specifying the Number of Column Array Rows and Size of Stream Buffers……Page 375
Optimizing Direct Path Loads on Multiple-CPU Systems……Page 376
Avoiding Index Maintenance……Page 377
Disabled Constraints……Page 378
Reenable Constraints……Page 379
When Automatic Constraints Cannot Be Used……Page 380
Duplicating the Effects of Exception Conditions……Page 381
Using a Stored Procedure……Page 382
Parallel Data Loading Models……Page 383
Intrasegment Concurrency with Direct Path……Page 384
Initiating Multiple SQL*Loader Sessions……Page 385
Specifying Temporary Segments……Page 386
Enabling Constraints After a Parallel Direct Path Load……Page 387
General Performance Improvement Hints……Page 388
10 SQL*Loader Case Studies……Page 391
The Case Studies……Page 392
Case Study Files……Page 393
Checking the Results of a Load……Page 394
Control File for Case Study 1……Page 395
Running Case Study 1……Page 396
Log File for Case Study 1……Page 397
Control File for Case Study 2……Page 398
Running Case Study 2……Page 399
Log File for Case Study 2……Page 400
Control File for Case Study 3……Page 401
Log File for Case Study 3……Page 403
Case Study 4: Loading Combined Physical Records……Page 404
Control File for Case Study 4……Page 405
Running Case Study 4……Page 406
Log File for Case Study 4……Page 407
Case Study 5: Loading Data into Multiple Tables……Page 408
Control File for Case Study 5……Page 409
Running Case Study 5……Page 410
Log File for Case Study 5……Page 411
Loaded Tables for Case Study 5……Page 413
Case Study 6: Loading Data Using the Direct Path Load Method……Page 414
Datafile for Case Study 6……Page 415
Log File for Case Study 6……Page 416
Creating a BEFORE INSERT Trigger……Page 418
Control File for Case Study 7……Page 419
Running Case Study 7……Page 421
Log File for Case Study 7……Page 422
Control File for Case Study 8……Page 424
Datafile for Case Study 8……Page 425
Running Case Study 8……Page 426
Log File for Case Study 8……Page 427
Case Study 9: Loading LOBFILEs (CLOBs)……Page 428
Control File for Case Study 9……Page 429
Datafiles for Case Study 9……Page 430
Running Case Study 9……Page 431
Log File for Case Study 9……Page 432
Control File for Case Study 10……Page 433
Log File for Case Study 10……Page 435
Case Study 11: Loading Data in the Unicode Character Set……Page 437
Control File for Case Study 11……Page 438
Running Case Study 11……Page 439
Log File for Case Study 11……Page 440
Loaded Tables for Case Study 11……Page 442
Part III External Tables……Page 443
The Access Driver……Page 445
Location of Datafiles and Output Files……Page 447
Using External Tables to Load Data……Page 448
Parallel Access to External Tables……Page 449
Performance Hints When Using External Tables……Page 450
12 External Tables Access Parameters……Page 451
record_format_info Clause……Page 452
FIXED length……Page 453
DELIMITED BY……Page 454
CHARACTERSET……Page 455
LOAD WHEN……Page 456
LOG FILE | NOLOGFILE……Page 457
condition_spec……Page 458
[directory object name:] filename……Page 459
range start : range end……Page 460
field_definitions Clause……Page 461
delim_spec……Page 462
trim_spec……Page 465
MISSING FIELD VALUES ARE NULL……Page 466
field_list……Page 467
pos_spec Clause……Page 468
length……Page 469
datatype_spec Clause……Page 470
[UNSIGNED] INTEGER [EXTERNAL] [(len)]……Page 471
DOUBLE [EXTERNAL]……Page 472
CHAR……Page 473
VARCHAR and VARRAW……Page 474
VARCHARC and VARRAWC……Page 475
dateformat_spec……Page 476
init_spec Clause……Page 477
Part IV DBVERIFY……Page 479
13 DBVERIFY: Offline Database Verification Utility……Page 481
Parameters……Page 482
Sample DBVERIFY Output……Page 483
Using DBVERIFY to Validate a Segment……Page 484
Command-Line Interface……Page 485
Part V Appendixes……Page 487
A SQL*Loader Syntax Diagrams……Page 489
Using the DB2 RESUME Option……Page 501
SORTDEVT and SORTNUM Statements……Page 502
PART Statement……Page 503
SQL*Loader Syntax with DB2-Compatible Statements……Page 504
Index……Page 507