Oracle 8i Utils

PDF Directory......Page 0
Send Us Your Comments......Page 19
Preface......Page 21
Part I Export/Import......Page 27
1 Export......Page 29
What Is the Export Utility?......Page 30
Access Privileges......Page 31
Export Modes......Page 32
Using Export......Page 36
Invoking Export......Page 37
Getting Online Help......Page 39
Specifying Export Parameters on the Command Line or in the Parameter File......Page 40
Export Parameters......Page 41
BUFFER......Page 44
CONSISTENT......Page 45
FILE......Page 47
FILESIZE......Page 48
INCTYPE......Page 49
QUERY......Page 50
RECORD......Page 51
STATISTICS......Page 52
TABLES......Page 53
USERID (username/password)......Page 55
Example Export Session in Full Database Mode......Page 56
Example Export Session in User Mode......Page 59
Example Export Sessions in Table Mode......Page 60
Example Export Session Using Partition-Level Export......Page 62
Using the Interactive Method......Page 65
Warning Messages......Page 68
Completion Messages......Page 69
Direct Path Export......Page 70
Performance Issues......Page 72
Incremental Exports......Page 73
Complete Exports......Page 75
A Scenario......Page 76
Which Data Is Exported?......Page 77
Example Incremental Export Session......Page 78
System Tables......Page 79
Exporting and Importing with Net8......Page 81
Character Set Conversion......Page 82
Fine-Grained Access Support......Page 84
Exporting Foreign Function Libraries......Page 85
Exporting Object Type Definitions......Page 86
Exporting Synonyms......Page 87
Using a Previous Version of Export......Page 88
Creating Oracle Release 8.0 Export Files from an Oracle8i Database......Page 89
Excluded Objects......Page 91
2 Import......Page 93
What Is the Import Utility?......Page 94
Table Objects: Order of Import......Page 95
Import Modes......Page 96
Understanding Table-Level and Partition-Level Import......Page 97
Invoking Import......Page 98
Getting Online Help......Page 100
The Parameter File......Page 101
Access Privileges......Page 102
Importing Objects into Your Own Schema......Page 103
Importing System Objects......Page 104
Manually Creating Tables Before Importing Data......Page 105
Manually Ordering the Import......Page 106
Import Parameters......Page 107
BUFFER......Page 110
CONSTRAINTS......Page 111
FILE......Page 112
FROMUSER......Page 113
IGNORE......Page 114
INCTYPE......Page 115
INDEXFILE......Page 116
RECALCULATE_STATISTICS......Page 117
SKIP_UNUSABLE_INDEXES......Page 118
TABLES......Page 119
TOID_NOVALIDATE......Page 120
TOUSER......Page 121
USERID (username/password)......Page 122
Guidelines for Using Partition-Level Import......Page 123
Migrating Data Across Partitions and Tables......Page 124
Example Import of Selected Tables for a Specific User......Page 125
Example Import of Tables Exported by Another User......Page 126
Example Import of Tables from One User to Another......Page 127
Example Import Session Using Partition-Level Import......Page 128
Using the Interactive Method......Page 131
Restoring a Set of Objects......Page 133
Index Creation and Maintenance Controls......Page 134
Delaying Index Creation......Page 135
Warning, Error, and Completion Messages......Page 136
Row Errors......Page 137
Errors Importing Database Objects......Page 138
Exporting and Importing with Net8......Page 139
Snapshots......Page 140
Fine-Grained Access Support......Page 141
Storage Parameters......Page 142
Dropping a Tablespace......Page 143
Character Set and NLS Considerations......Page 144
Import and Single-Byte Character Sets......Page 145
Importing Object Identifiers......Page 146
Importing Nested Tables......Page 148
Importing BFILE Columns and Directory Aliases......Page 149
Importing Advanced Queue (AQ) Tables......Page 150
Importing Tables......Page 151
Transportable Tablespaces......Page 152
Using Export Files from a Previous Oracle Release......Page 153
Using Oracle Version 6 Export Files......Page 154
The CHARSET Parameter......Page 155
Part II SQL*Loader......Page 157
3 SQL*Loader Concepts......Page 159
SQL*Loader Basics......Page 160
SQL*Loader Control File......Page 161
Input Data and Datafiles......Page 162
Data Fields......Page 165
Data Conversion and Datatype Specification......Page 166
The Bad File......Page 169
Log File and Logging Information......Page 171
Conventional Path Load Versus Direct Path Load......Page 172
Supported Object Types......Page 173
Supported LOB Types......Page 174
SQL*Loader DDL Behavior and Restrictions......Page 175
SQL*Loader DDL Support for LOBFILES and Secondary Data Files (SDFs)......Page 177
Application Development: Direct Path Load API......Page 180
4 SQL*Loader Case Studies......Page 181
The Case Studies......Page 182
Case Study Files......Page 183
Running the Case Study SQL Scripts......Page 184
Control File......Page 185
Log File......Page 186
Control File......Page 188
Log File......Page 189
Control File......Page 191
Log File......Page 193
Control File......Page 195
Data File......Page 196
Log File......Page 197
Bad File......Page 198
Control File......Page 199
Invoking SQL*Loader......Page 200
Log File......Page 201
Loaded Tables......Page 203
Control File......Page 205
Log File......Page 206
Insert Trigger......Page 208
Control File......Page 209
Log File......Page 211
Dropping the Insert Trigger and the Global-Variable Package......Page 213
Control File......Page 214
Table Creation......Page 215
Log File......Page 216
Control File......Page 219
Input Data Files......Page 220
Invoking SQL*Loader......Page 221
Log File......Page 222
Control File......Page 224
Log File......Page 225
5 SQL*Loader Control File Reference......Page 229
SQL*Loader DDL Syntax Diagram Notation......Page 231
High-Level Syntax Diagrams......Page 232
Expanded DDL Syntax......Page 243
Comments in the Control File......Page 245
Filenames That Conflict with SQL and SQL*Loader Reserved Words......Page 246
Operating System Considerations......Page 247
Identifying Data in the Control File with BEGINDATA......Page 249
Naming the File......Page 250
Specifying Multiple Datafiles......Page 251
File Processing Example......Page 252
BADFILE: Specifying the Bad File......Page 253
Rejected Records......Page 254
Specifying the Discard File......Page 255
Input Character Conversion......Page 258
Loading Data into Nonempty Tables......Page 260
TRUNCATE......Page 261
Continuing an Interrupted Load......Page 262
Assembling Logical Records from Physical Records......Page 264
Specifying Table Names......Page 267
Table-Specific Loading Method......Page 268
Choosing Which Records to Load......Page 269
Specifying Default Data Delimiters......Page 270
Index Options......Page 271
Specifying Field Conditions......Page 272
Comparing Fields to Literals......Page 274
Specifying Filler Fields......Page 275
Specifying the Position of a Data Field......Page 276
Using POSITION with Data Containing Tabs......Page 277
Using POSITION with Multiple Table Loads......Page 278
Extracting Multiple Logical Records......Page 279
Distinguishing Different Input Record Formats......Page 280
Summary......Page 281
Setting a Column to a Constant Value......Page 282
Setting a Column to the Current Date......Page 283
Setting a Column to a Unique Sequence Number......Page 284
Generating Sequence Numbers for Multiple Tables......Page 285
SQL*Loader Datatypes......Page 286
Nonportable Datatypes......Page 287
Portable Datatypes......Page 292
Datatype Conversions......Page 297
Specifying Delimiters......Page 298
Conflicting Character Datatype Field Lengths......Page 301
Loading Data Across Different Platforms......Page 302
Performance Implications......Page 303
Calculations......Page 304
Minimizing Memory Requirements for the Bind Array......Page 307
DEFAULTIF Clause......Page 308
Null Columns at the End of a Record......Page 309
Datatypes......Page 310
Field Length Specifications......Page 311
Relative Positioning of Fields......Page 312
Leading Whitespace......Page 313
Enclosed Fields......Page 314
PRESERVE BLANKS Keyword......Page 315
Applying SQL Operators to Fields......Page 316
Combinations of Operators......Page 317
Loading Column Objects in Stream Record Format......Page 318
Loading Column Objects in Variable Record Format......Page 319
Loading Nested Column Objects......Page 320
Specifying NULL Values for Objects......Page 321
Loading Object Tables......Page 323
Loading REF Columns......Page 325
Internal LOBs (BLOB, CLOB, NCLOB)......Page 326
External LOB (BFILE)......Page 334
Loading Collections (Nested Tables and VARRAYs)......Page 335
Memory Issues When Loading VARRAY Columns......Page 339
6 SQL*Loader Command-Line Reference......Page 341
SQL*Loader Command Line......Page 342
BAD (bad file)......Page 343
DATA (datafile)......Page 344
ERRORS (errors to allow)......Page 345
PARFILE (parameter file)......Page 346
ROWS (rows per commit)......Page 347
SILENT (feedback mode)......Page 348
SKIP_INDEX_MAINTENANCE......Page 349
Exit Codes for Inspection and Display......Page 350
7 SQL*Loader: Log File Reference......Page 353
Global Information......Page 354
Datafile Information......Page 355
Summary Statistics......Page 356
Oracle Statistics That Are Logged......Page 357
8 SQL*Loader: Conventional and Direct Path Loads......Page 359
Conventional Path Load......Page 360
Direct Path Load......Page 361
Building Indexes......Page 368
Indexes Left in Index Unusable State......Page 369
Data Saves......Page 370
Recovery......Page 371
Loading LONG Data Fields......Page 372
Presorting Data for Faster Indexing......Page 374
Infrequent Data Saves......Page 376
Specifying UNRECOVERABLE......Page 377
Direct Loads, Integrity Constraints, and Triggers......Page 378
Integrity Constraints......Page 379
Database Insert Triggers......Page 380
Permanently Disabled Triggers and Constraints......Page 382
Parallel Data Loading Models......Page 383
Intrasegment Concurrency with Direct Path......Page 384
Initiating Multiple SQL*Loader Sessions......Page 385
Options Keywords for Parallel Direct Path Loads......Page 386
PRIMARY KEY and UNIQUE KEY constraints......Page 387
General Performance Improvement Hints......Page 388
Part III Offline Database Verification Utility......Page 391
Restrictions......Page 393
Parameters......Page 394
Sample DBVERIFY Output......Page 395
Part IV Appendixes......Page 397
A SQL*Loader Reserved Words......Page 399
Reserved Word List and Information......Page 400
B DB2/DXT User Notes......Page 403
Inclusions for Compatibility......Page 404
Restrictions......Page 405
SQL*Loader Syntax with DB2-Compatible Statements......Page 406
Index......Page 409