Index

importing, 2-57

ANALYZE

Import parameter, 2-18

analyzer statistics, 2-61

APPEND keyword

SQL*Loader, 5-44

APPEND to table

example, 4-11

backslash escape character, 5-20

arrays

committing after insert

Import, 2-19

ASCII

character set

fixed-format files

attributes

backups

restoring dropped snapshots

Import, 2-49

BAD

SQL*Loader command-line parameter, 6-3

bad files

rejected records in SQL*Loader, 3-12

specifying bad records, 6-3

specifying for SQL*Loader, 5-25

BADDN keyword

SQL*Loader, 5-25

BADFILE keyword

SQL*Loader, 5-25

base backup

Export, 1-45

base tables

incremental export and, 1-49

BEGINDATA

control file keyword, 5-21

BFILE datatype, 5-106

BFILEs

columns

importing, 2-57

determining size of for SQL*Loader, 5-75, 5-76

bind arrays

minimizing SQL*Loader memory requirements, 5-79

minimum requirements, 5-75

size with multiple SQL*Loader INTO TABLE statements, 5-80

specifying, 6-4

specifying number of rows, 6-7

SQL*Loader performance implications, 5-75

BINDSIZE

SQL*Loader command-line parameter, 5-76, 6-4

blanks

loading fields consisting of blanks, 5-82

preserving, 5-87

SQL*Loader BLANKS keyword for field comparison, 5-15, 5-46

BLANKS keyword

for field comparison, 5-15

BLOBs

calculating for export, 1-16

BUFFER

Export parameter, 1-16

direct path export, 1-44

Import parameter, 2-18

buffers

space required by

LONG DATA in SQL*Loader, 5-64

VARCHAR data in SQL*Loader, 5-63

specifying with SQL*Loader BINDSIZE parameter, 5-76

BYTEINT datatype, 5-59, 5-60

cached sequence numbers

Export, 1-57

case studies

preparing tables for SQL*Loader, 4-4

SQL*Loader, 4-1

SQL*Loader associated files, 4-3

SQL*Loader filenames, 4-3

CATALOG.SQL

preparing database for Import, 2-6

CATEXP7.SQL

preparing database for Export, 1-63

CATEXP.SQL

preparing database for Import, 2-6

CATLDR.SQL

setup script

SQL*Loader, 8-10

CHAR columns

Version 6 export files, 2-62

CHAR datatype

delimited form and SQL*Loader, 5-70

reference

character datatypes

conflicting fields, 5-73

character fields

datatypes

delimiters and SQL*Loader, 5-64, 5-70

determining length for SQL*Loader, 5-73

character sets

conversion

during Export/Import, 1-54, 2-52

direct path export, 1-44, 1-54

eight-bit to seven-bit conversions

Export/Import, 1-54, 2-53

multibyte

and SQL*Loader, 5-30

Export/Import, 1-56, 2-54

NCHAR data

Export, 1-56

single-byte

Export/Import, 1-54, 2-53

SQL*Loader conversion between, 5-30

Version 6 conversions

Import/Export, 2-54

character strings

as part of a field comparison, 5-15

CHARACTERSET keyword

check constraints

CLOBs

clusters

columns

exporting LONG datatypes, 1-57

loading REF columns, 5-97

naming

SQL*Loader, 5-47

null columns at the end of a record, 5-81

objects

loading, 5-90

loading nested column objects, 5-92

stream record format, 5-90

variable record format, 5-91

reordering before Import, 2-13

setting to a constant value with SQL*Loader, 5-54

setting to a unique sequence number using SQL*Loader, 5-56

setting to datafile record number with SQL*Loader, 5-55

setting to null, 5-81

setting to null value with SQL*Loader, 5-55

setting to the current date using SQL*Loader, 5-55

setting value to zero, 5-80

specifying

SQL*Loader, 5-47

specifying as PIECED

SQL*Loader, 8-15

command line

parameters

description, 6-2

Export, 1-13

specifying defaults, 5-18

comments

in Export parameter file, 1-13

in Import parameter file, 2-10

in SQL*Loader control file, 4-12

COMMIT

Import parameter, 2-19

complete exports, 1-45, 1-47

Export parameter, 1-17, 2-51

specifying, 1-21

completion messages

Export, 1-41

COMPRESS

COMPUTE option

STATISTICS Export parameter, 1-24

CONCATENATE keyword

SQL*Loader, 5-36

concurrent conventional path loads, 8-25

connect string

Net8, 1-53

CONSISTENT

Export parameter, 1-17

nested table and, 1-17

partitioned table and, 1-17

consolidating extents

Export parameter COMPRESS, 1-17

CONSTANT keyword

SQL*Loader, 5-47, 5-54, 8-23

CONSTRAINTS

Export parameter, 1-19, 2-19

constraints

automatic

SQL*Loader, 8-22

check

disabling during a direct load, 8-21

direct path load, 8-20

disabling referential constraints, 2-14

enabling

after a direct load, 8-21

after a parallel direct path load, 8-29

enforced on a direct load, 8-21

failed

load method, 8-9

not null

preventing Import errors due to uniqueness constraints, 2-19

referential integrity

uniqueness

SQL*Loader command-line parameter, 6-4

CONTINUE_LOAD keyword

SQL*Loader, 5-35

CONTINUEIF keyword

example, 4-15

SQL*Loader, 5-36

continuing interrupted loads

SQL*Loader, 5-34

CONTROL

control files

data definition language syntax, 5-3

field delimiters, 5-16

guidelines for creating, 3-3

specifying data, 5-21

specifying SQL*Loader discard file, 5-27

conventional path Export

compared to direct path Export, 1-42

conventional path loads

basics, 8-2

compared to direct path loads, 8-7

concurrent, 8-26

SQL*Loader bind array, 5-75

using, 8-3

CREATE SESSION privilege, 2-10

Export, 1-3

CREATE USER command

Import, 2-13

CTIME column

cumulative exports, 1-45, 1-47

recording, 1-24

specifying, 1-21

SYS.INCFIL table, 1-52

SQL*Loader command-line parameter, 6-4

DATA

data

delimiter marks in data and SQL*Loader, 5-72

distinguishing different input formats for SQL*Loader, 5-51

exporting, 1-24

formatted data and SQL*Loader, 4-28

generating unique values with SQL*Loader, 5-56

including in control files, 5-21

loading data contained in the SQL*Loader control file, 5-54

loading in sections

SQL*Loader, 8-15

loading into more than one table

SQL*Loader, 5-51

loading LONG

maximum length of delimited data for SQL*Loader, 5-73

moving between operating systems using SQL*Loader, 5-74

saving in a direct path load, 8-12

saving rows

SQL*Loader methods of loading into tables, 5-32

unsorted

values optimized for SQL*Loader performance, 5-54

data conversion

SQL*Loader, 3-9

data definition language, 5-3

BEGINDATA keyword, 5-21

BLANKS keyword, 5-46

CHARACTERSET keyword

SQL*Loader, 5-31

column_name, 5-16

CONCATENATE keyword, 5-36

CONSTANT keyword, 5-47, 5-54

CONTINUEIF keyword, 5-36

date mask, 5-16

DEFAULTIF keyword, 5-80

delimiter_spec, 5-16

DISABLED_CONSTRAINTS keyword

DISCARDDN keyword, 5-28

DISCARDMAX keyword

SQL*Loader, 5-30

EXCEPTIONS keyword

expanded syntax diagrams, 5-15

EXTERNAL keyword, 5-67

field_condition, 5-15

FILE keyword

loading data in sections

parallel keyword

POSITION keyword, 5-48

precision, 5-16

RECNUM keyword, 5-47

REENABLE keyword

SEQUENCE keyword, 5-56

syntax diagrams

high-level, 5-4

SYSDATE keyword, 5-55

TERMINATED keyword, 5-70

UNRECOVERABLE keyword

specifying the SQL*Loader datatype, 5-48

WHITESPACE keyword, 5-70

data field

data path loads

direct and conventional, 8-2

data recovery

direct path load

privileges for export, 1-3

database administrator (DBA)

database objects

export privileges, 1-4

exporting LONG columns, 1-57

transferring across a network

Import, 2-47

databases

data structure changes

incremental export and, 1-49

full export, 1-21

full import, 2-22

incremental export, 1-45

preparing for Export, 1-9

privileges for exporting, 1-3

reducing fragmentation via full export/import, 2-44

reusing existing data files

Import, 2-20

datafiles

preventing overwrite during import, 2-20

reusing during import, 2-20

specifying, 6-4

specifying buffering for SQL*Loader, 5-24

specifying for SQL*Loader, 5-22

specifying format for SQL*Loader, 5-24

datatypes

DATE datatype

delimited form and SQL*Loader, 5-70

determining length, 5-74

mask

SQL*Loader, 5-74

SQL*Loader, 5-65

date mask, 5-16

DB2 load utility, B-1

placement of statements

DISCARDDDN, B-3

DISCARDS, B-3

restricted capabilities of SQL*Loader, B-3

RESUME keyword, 5-32

SQL*Loader compatibility

ignored statements, B-2

DBA role

EXP_FULL_DATABASE role, 1-9

DBCS (DB2 double-byte character set)

not supported by Oracle, B-4

DBVERIFY utility, 9-1

output, 9-3

restrictions, 9-1

syntax, 9-2

DDL. See data definition language

DECIMAL datatype, 5-61

(packed), 5-59

EXTERNAL format

length and precision, 5-16

DEFAULT column values

Oracle Version 6 export files, 2-62

DEFAULTIF keyword

SQL*Loader, 5-44, 5-80

DELETE ANY TABLE privilege

DELETE CASCADE

DELETE privilege

maximum length for SQL*Loader, 5-73

delimited data

delimited fields

field length, 5-73

delimited files

exporting, 1-3

delimited LOBs, 5-103

delimiter_spec, 5-16

delimiters

control files, 5-16

initial and trailing example, 4-28

loading trailing blanks, 5-73

marks in data and SQL*Loader, 5-72

optional SQL*Loader enclosure, 5-83

specifying for SQL*Loader, 5-42, 5-70

SQL*Loader enclosure, 5-83

SQL*Loader field specifications, 5-83

termination, 5-83

DESTROY

Import parameter, 2-20

DIRECT

Export parameter, 1-19, 1-44

SQL*Loader command-line parameter, 6-5

direct path export, 1-42

BUFFER parameter, 1-44

character set and, 1-54

invoking, 1-44

RECORDLENGTH parameter, 1-44

direct path load, 8-11

advantages, 8-6

choosing sort order

compared to conventional path load, 8-7

concurrent, 8-26

conditions for use, 8-8

data saves, 8-12, 8-18

DIRECT command-line parameter

SQL*Loader, 6-5, 8-10

DISABLED_CONSTRAINTS keyword, 8-21

disabling media protection

dropping indexes to continue an interrupted load, 5-34

dropping indexes, 8-20

effect of PRIMARY KEY constraints, 8-29

effect of UNIQUE KEY constraints, 8-29

example, 4-25

EXCEPTIONS keyword, 8-21

field defaults, 8-9

improper sorting

intersegment concurrency, 8-26

indexes, 8-10

instance recovery, 8-13

intrasegment concurrency, 8-26

loading into synonyms, 8-9

LONG data, 8-14

media recovery, 8-14

partitioned load

preallocating storage, 8-16

performance, 8-10, 8-16

presorting data, 8-16

recovery, 8-13

REENABLE keyword, 8-21

referential integrity constraints, 8-21

ROWS command-line parameter, 8-13

setting up, 8-10

specifying, 8-10

specifying number of rows to be read, 6-7

SQL*Loader data loading method, 3-15

table insert triggers, 8-22

temporary segment storage requirements, 8-11

triggers, 8-20

using, 8-7, 8-10

version requirements, 8-9

directory aliases

importing, 2-57

DISABLED_CONSTRAINTS keyword

placement in control file for DB2, B-3

discard files

basics, 3-14

DISCARDDN keyword

DISCARDS control file clause

placement in control file for DB2, B-3

example, 4-15

SQL*Loader, 5-27

SQL*Loader DISCARDDN keyword, 5-28

SQL*Loader DISCARDMAX keyword, 5-29, 5-30

SQL*Loader DISCARDS keyword, 5-29

discarded records

causes, 5-29

limiting, 5-29

SQL*Loader, 3-12

discarded SQL*Loader records

discard file, 5-27

DISCARDFILE

SQL*Loader command-line parameter, 6-5

DISCARDMAX keyword

SQL*Loader command-line, 6-5

SQL*Loader discarded records, 5-30

discontinued loads

continuing with SQL*Loader, 5-34

DOUBLE datatype, 5-59, 5-60

dropped snapshots

Import, 2-49

dropping

indexes

to continue a direct path load, 5-34

dump files

maximum size, 1-20

EBCDIC character set

Import, 2-53

eight-bit character set support, 1-54, 2-53

enclosed fields

ENCLOSED BY control file clause, 5-16

specified with enclosure delimiters and SQL*Loader, 5-70

whitespace, 5-86

enclosure delimiters

SQL*Loader, 5-83

ERRORS

SQL*Loader command-line parameter, 6-5

errors

caused by tab characters in SQL*Loader data, 5-49

Export, 1-40

export log file, 1-22

fatal

Export, 1-41

Import, 2-47

generated by DB2 load utility, B-3

handling

Export, 1-40

Import resource errors, 2-46

LONG data, 2-46

object creation, 2-46

Import parameter IGNORE, 2-22

row errors during import, 2-45

warning

escape character

ESTIMATE option

STATISTICS Export parameter, 1-24

EXCEPTIONS keyword

EXP_FULL_DATABASE role, 1-21, 2-10

assigning, 1-9

Export, 1-3

EXPDAT.DMP

Export output file, 1-19

EXPID column

Export

base backup, 1-45

BUFFER parameter, 1-16

CATALOG.SQL

preparing the database for Version 7 export, 1-63

CATEXP7.SQL

CATEXP.SQL

complete, 1-21, 1-45, 1-47

command line, 1-9

privileges, 1-45

CONSISTENT parameter, 1-17

COMPRESS parameter, 1-17

CONSTRAINTS parameter, 1-19

creating necessary privileges, 1-9

creating Version 7 export files, 1-61

cumulative, 1-21, 1-45, 1-47

privileges required, 1-45

database optimizer statistics, 1-24, 2-25

data structures, 1-49

DIRECT parameter, 1-19

direct path, 1-42

displaying help message, 1-21

eight-bit versus seven-bit character sets, 1-54

establishing export views, 1-9

examples, 1-28

full database mode, 1-28

partition-level, 1-34

table mode, 1-32

user mode, 1-31

exporting an entire database, 1-21

exporting indexes, 1-22

exporting sequence numbers, 1-57

exporting synonyms, 1-59

exporting to another operating system, 2-26

RECORDLENGTH parameter, 1-24

FEEDBACK parameter, 1-19

FILE parameter, 1-19

full database mode

example, 1-28

FULL parameter, 1-21

GRANTS parameter, 1-21

HELP parameter, 1-21

incremental, 1-21, 1-45

INCTYPE parameter, 1-21

INDEXES parameter, 1-22

interactive method, 1-10, 1-37

invoking, 1-9

kinds of data exported, 1-49

last valid export

logging error messages, 1-22

log files

specifying, 1-22

LOG parameter, 1-22

LONG columns, 1-57

message log file, 1-40

modes, 1-4

multibyte character sets, 1-56

network issues, 1-53

NLS support, 1-54

objects exported, 1-5

online help, 1-11

OWNER parameter, 1-22

parameter conflicts, 1-28

parameter file, 1-9, 1-12, 1-22

maximum size, 1-13

parameters, 1-13

PARFILE parameter, 1-9, 1-12, 1-22

preparing database, 1-9

previous versions, 1-60

RECORD parameter, 1-23

RECORDLENGTH parameter, 1-24

redirecting output to a log file, 1-40

remote operation, 1-53

restrictions, 1-4

rollback segments, 1-50

ROWS parameter, 1-24

sequence numbers, 1-57

STATISTICS parameter, 1-24

storage requirements, 1-9

SYS.INCFIL table, 1-52

table name restrictions, 1-26

table mode

example, 1-32

TABLES parameter, 1-25

tracking exported objects, 1-52

transferring export files across a network, 1-53

TRIGGERS parameter, 1-27

user access privileges, 1-3

user mode

examples, 1-31

specifying, 1-22

USER_SEGMENTS view, 1-9

USERID parameter, 1-27

using, 1-9

warning messages, 1-40

export file

displaying contents, 1-3

importing the entire file, 2-22

listing contents before importing, 2-26

reading, 1-3

specifying, 1-19

extent allocation

FILE command-line parameter, 6-6

extents

consolidating into one extent

Export, 1-17

importing consolidated, 2-51

EXTERNAL datatypes

DECIMAL

FLOAT

GRAPHIC

numeric

determining length, 5-73

ZONED

external files

EXTERNAL keyword

external LOBs (BFILEs), 5-106

specifying for SQL*Loader, 5-44

fatal errors

Export, 1-41

Import, 2-46, 2-47

FEEDBACK

Export parameter, 1-19

Import parameter, 2-20

field conditions

field delimiters

new behavior in 8.1.5, 3-19

field length

SQL*Loader specifications, 5-83

field location

SQL*Loader, 5-48

fields

FIELDS clause

SQL*Loader, 5-42

terminated by whitespace, 5-85

FILE, 6-6

Export parameter, 1-19

Import parameter, 2-20

keyword

SQL*Loader, 8-28

in SQL*Loader command file, 6-6

FILE columns

Import, 2-57

FILE keyword, 8-28

FILE parameter

SQL*Loader command line, 6-6

filenames

quotation marks, 5-19

specifying multiple SQL*Loader, 5-23

SQL*Loader, 5-18

SQL*Loader bad file, 5-25

files

SQL*Loader bad file, 3-12

SQL*Loader discard file, 3-14

SQL*Loader file processing options string, 5-24

FILESIZE, 1-20

FILLER field

example, 4-39

new behavior in 8.1.5, 3-20

using as argument to init_spec, 5-47

fine-grained access support, 2-49

fixed record length

example, 4-34

fixed-format records, 3-4

FLOAT datatype, 5-59

EXTERNAL format

FLOAT EXTERNAL data values

foreign function libraries

not allowed by SQL*Loader, B-4

FORMAT statement in DB2

formats

and SQL*Loader input records, 5-52

formatting errors

SQL*Loader, 5-25

fragmentation

reducing database fragmentation via full export/import, 2-44

FROMUSER

Import parameter, 2-21

FTP

Export files, 1-53

FULL

Export parameter, 1-21

full database mode

Import, 2-22

GRANTS

Export parameter, 1-21

Import parameter, 2-22

grants

exporting, 1-21

importing, 2-12, 2-22

GRAPHIC datatype, 5-59

EXTERNAL format

SQL*Loader, 5-66

GRAPHIC EXTERNAL datatype, 5-59

HELP parameter

Export, 1-11, 1-21

Import, 2-8, 2-22

hexadecimal strings

as part of a field comparison, 5-15

SQL*Loader, 5-46

IGNORE

Import parameter, 2-22, 2-55

existing objects, 2-46

IMP_FULL_DATABASE role, 2-6, 2-10, 2-22

Import, 2-29

Import, 2-1

ANALYZE parameter, 2-18

backup files, 2-49

BUFFER parameter, 2-18

CATEXP.SQL

preparing the database, 2-6

character set conversion, 1-54, 2-53

character sets, 2-53

COMMIT parameter, 2-19

committing after array insert, 2-19

compatibility, 2-4

complete export file, 2-41

consolidated extents, 2-51

controlling size of rollback segments, 2-19

conversion of Version 6 CHAR columns to VARCHAR2, 2-62

creating an index-creation SQL script, 2-25

cumulative, 2-41

data files

reusing, 2-20

database

reusing existing data files, 2-20

DESTROY parameter, 2-20

disabling referential constraints, 2-14

displaying online help, 2-22

dropping a tablespace, 2-51

error handling, 2-45

errors importing database objects, 2-46

example session, 2-33

export COMPRESS parameter, 2-51

export file

importing the entire file, 2-22

listing contents before import, 2-26

failed integrity constraints, 2-45

fatal errors, 2-46, 2-47

FEEDBACK parameter, 2-20

FILE parameter, 2-20

FROMUSER parameter, 2-21

grants

specifying for import, 2-22

GRANTS parameter, 2-22

HELP parameter, 2-8, 2-22

IGNORE parameter, 2-22, 2-46

importing grants, 2-12, 2-22

importing objects into other schemas, 2-12

importing rows, 2-26

importing tables, 2-27

incremental, 2-41

specifying, 2-24

INCTYPE parameter, 2-23

INDEXES parameter, 2-24

INDEXFILE parameter, 2-24

INSERT errors, 2-46

interactive method, 2-39

invalid data, 2-45

invoking, 2-6

length of Oracle Version 6 export file DEFAULT columns, 2-62

log files

LOG parameter, 2-25

LONG columns, 2-59

manually ordering tables, 2-14

modes, 2-4

NLS considerations, 2-52

NLS_LANG environment variable, 2-53

object creation errors, 2-22

objects imported, 1-5

OPTIMAL storage parameter, 2-50

Oracle Version 6 integrity constraints, 2-62

parameter file, 2-9, 2-25

parameters, 2-15

partition-level, 2-5

preparing the database, 2-6

read-only tablespaces, 2-51

recompiling stored procedures, 2-58

RECORDLENGTH parameter, 2-26

records

specifying length, 2-26

reducing database fragmentation, 2-44

refresh error, 2-48

reorganizing tablespace during, 2-52

resource errors, 2-46

rows

specifying for import, 2-26

ROWS parameter, 2-26

schema objects, 2-11, 2-12

sequences, 2-46

SHOW parameter, 1-3, 2-26

single-byte character sets, 2-53

snapshot master table, 2-48

snapshots, 2-48

restoring dropped, 2-49

specifying by user, 2-21

specifying index creation commands, 2-24

specifying the export file, 2-20

storage parameters

overriding, 2-51

stored functions, 2-58

stored packages, 2-58

stored procedures, 2-58

system objects, 2-12

table objects

import order, 2-3

table-level, 2-5

tables created before import, 2-13

TABLES parameter, 2-27

TOUSER parameter, 2-29

transferring files across networks, 2-47

unique indexes, 2-23

uniqueness constraints

preventing import errors, 2-19

user definitions, 2-13

USERID parameter, 2-30

using Oracle Version 6 files, 2-62

incremental export, 1-45

backing up data, 1-50

session example, 1-50

specifying, 1-21

SYS.INCFIL table, 1-52

SORTED INDEXES with SQL*Loader, 5-44

incremental import

parameter, 2-23

specifying, 2-24

INCTYPE

Export parameter, 1-21

Import parameter, 2-23

index options

SQL*Loader SINGLEROW keyword, 5-44

Index Unusable state, 5-34

indexes left in Index Unusable state, 8-11

INDEXES

Export parameter, 1-22

Import parameter, 2-24

indexes

creating manually, 2-25

direct path load

left in direct load state, 8-11

dropping

before continuing a direct path load, 5-34

SQL*Loader, 8-20

exporting, 1-22

importing, 2-24

index-creation commands

Import, 2-24

left direct load state

maintenance options, 6-9, 8-20

multiple-column

skipping maintenance, 6-9, 8-20

presorting data, 4-25

SQL*Loader, 8-16

skipping unusable, 2-26, 6-10, 8-20

SQL*Loader, 5-43

state after discontinued load, 5-34

unique, 2-23

INDEXFILE

Import parameter, 2-24

INFILE keyword

insert errors

INSERT into table

INTEGER datatype, 5-59

EXTERNAL format, 5-67

integrity constraints

disabled during direct path load, 8-21

enabled during direct path load, 8-21

failed on Import, 2-45

load method, 8-9

Oracle Version 6 export files, 2-62

reenabled after direct path load, 8-21

interactive method

Export, 1-37

internal LOBs

continuing with SQL*Loader, 5-34

interrupted loads

INTO TABLE statement

effect on bind array size, 5-80

multiple statements with SQL*Loader, 5-51

invalid data

invalid objects

warning messages

during export, 1-40

invoking Export, 1-9

direct path, 1-44

ITIME column

generating with SQL*Loader, 5-56

key values

language support

leading whitespace

trimming and SQL*Loader, 5-85

length

specifying record length for export, 1-24, 2-26

length indicator

determining size, 5-77

length of a numeric field, 5-16

length subfield

VARCHAR DATA

SQL*Loader, 5-62

length-value pair specified LOBs, 5-104

libraries

foreign function

LOAD

combined physical records, 4-15

loading

datafiles containing tabs

SQL*Loader, 5-49

delimited, free-format files, 4-11

fixed-length data, 4-8

LOBs, 5-98

negative numbers, 4-15

subpartitioned tables, 8-6

tables, 8-6

variable-length data, 4-5

LOB data, 1-9

compression, 1-17

Export, 1-57

in delimited fields, 5-99

in length-value pair fields, 5-100

in predetermined size fields, 5-99

LOBFILEs, 5-98, 5-101

example, 4-39

SQL*Loader DDL support, 3-20

LOBs

loading internal LOBs, 5-98

loading external, 5-98

LOG

Export parameter, 1-22, 1-40

Import parameter, 2-25

after a discontinued load, 5-34

log files

example, 4-26, 4-31

Export, 1-22, 1-40

Import, 2-25

specifying for SQL*Loader, 6-6

SQL*Loader, 3-14

SQL*Loader datafile information, 7-3

SQL*Loader global information, 7-2

SQL*Loader header Information, 7-2

SQL*Loader summary statistics, 7-4

SQL*Loader table information, 7-3

SQL*Loader table load information, 7-4

logical records

consolidating multiple physical records using SQL*Loader, 5-36

LONG data

C language datatype LONG FLOAT, 5-60

importing, 2-59

loading

loading with direct path load, 8-14

LONG VARRAW datatype, 5-63

master table

snapshots

Import, 2-48

materialized views, 2-48

media protection

disabling for direct path loads

controlling SQL*Loader use, 5-24

media recovery

direct path load, 8-14

SQL*Loader, 8-14

memory

messages

Export, 1-40

Import, 2-44

migrating data across partitions, 2-32

missing data columns

SQL*Loader, 5-43

modes

full database

Export, 1-21, 1-28

objects exported by each, 1-5

table

Export, 1-25, 1-32

user

Export, 1-22, 1-31

multibyte character sets

blanks with SQL*Loader, 5-46

Export and Import issues, 1-56, 2-54

SQL*Loader, 5-30

multiple CPUs

generating unique sequence numbers using SQL*Loader, 5-57

multiple table load

SQL*Loader control file specification, 5-51

multiple-column indexes

multiple-table load

discontinued, 5-35

NAME column

transporting Export files across a network, 1-53

National Language Support (NLS)

native datatypes

conflicting length specifications

NCHAR data

NCLOBs

negative numbers

nested column objects

loading, 5-92

nested tables

exporting, 1-59

consistency and, 1-17

importing, 2-56

Net8, 1-53

networks

Export, 1-53

Import and, 2-47

new features in 8.1.5

SQL*Loader behavior and restrictions, 3-18

SQL*Loader support for LOBFILEs and SDFs, 3-20

NLS. See National Language Support (NLS)

NLS_LANG environment variable, 2-53

Export, 1-54

Import, 2-53

SQL*Loader, 5-30

NOLOG attribute, 8-20

NONE option

STATISTICS Export parameter, 1-24

nonfatal errors

warning messages, 1-40

nonscalar datatypes, 5-93

normalizing data during a load

SQL*Loader, 4-19

NOT NULL constraint

missing columns at end of record during load, 5-43

load method, 8-9

null columns

at end of record, 5-81

null data

unspecified columns and SQL*Loader, 5-47

NULL values

objects, 5-93

NULLIF clause

SQL*Loader, 5-44, 5-81, 5-82

NULLIF...BLANKS clause

nulls

NUMBER datatype

numeric EXTERNAL datatypes

delimited form and SQL*Loader, 5-70

determining length, 5-73

precision versus length, 5-16

numeric fields

object identifiers, 2-54, 5-95

object names

object tables

object type definitions

object type identifiers, 2-28

importing, 2-56

objects, 3-16

considerations for importing, 2-54

creation errors, 2-46

ignoring existing objects during import, 2-22

import creation errors, 2-22

loading column objects, 5-90

loading nested column objects, 5-92

NULL values, 5-93

privileges, 2-11

restoring sets

Import, 2-41

stream record format, 5-90

variable record format, 5-91

offline bitmapped tablespaces, 1-58

OID. See object identifiers

online help

Export, 1-11

Import, 2-8

operating systems

moving data to different systems using SQL*Loader, 5-74

OPTIMAL storage parameter, 2-50

optimizer statistics, 2-61

optimizing

direct path loads, 8-16

SQL*Loader input file processing, 5-24

OPTIONALLY ENCLOSED BY, 5-16

SQL*Loader, 5-83

OPTIONS keyword, 5-18

for parallel loads, 5-41

Oracle Advanced Queuing. See Advanced Queuing

Oracle Version 6

exporting database objects, 2-62

Oracle7

creating export files with, 1-63

output file

specifying for Export, 1-19

OWNER

Export parameter, 1-22

OWNER# column

packed decimal data, 5-16

padding of literal strings

SQL*Loader, 5-46

PARALLEL

PARALLEL command-line parameter, 6-6

PARALLEL keyword

SQL*Loader, 8-27

parallel loads, 8-25

allocating extents, 6-6

restrictions on direct path, 8-27

parameter file

maximum size

parameters

BUFFER

COMMIT

conflicts between export parameters, 1-28

CONSTRAINTS

DESTROY

DIRECT

FEEDBACK

FILE

FROMUSER

FULL

GRANTS

HELP

IGNORE

INCTYPE

INDEXES

INDEXFILE

LOG, 1-40

OWNER

PARFILE

RECORD

RECORDLENGTH

ROWS

SHOW

SKIP_INDEX_MAINTENANCE, 6-9, 8-20

SKIP_UNUSABLE_INDEXES, 6-10, 8-20

STATISTICS

TABLES

TOID_NOVALIDATE, 2-28

TOUSER

Import, 2-29

USERID

Export, 1-27

Import, 2-30

PARFILE

Export command-line option, 1-9, 1-12, 1-22

Import command-line option, 2-9, 2-25

not allowed by SQL*Loader, B-4

PART statement in DB2

partitioned loads

concurrent conventional path loads, 8-25

export consistency and, 1-17

partitioned tables

example, 4-34

exporting, 1-8

importing, 2-5, 2-33

in DB2

no Oracle equivalent, B-4

loading, 8-6

partition-level Export, 1-8

examples, 1-34

partition-level Import, 2-5, 2-31

guidelines, 2-31

specifying, 1-25

passwords

hiding, 2-7

performance

direct path Export, 1-42, 1-44

Import, 2-19

optimizing reading of SQL*Loader data files, 5-24

partitioned load

conventional path for small loads, 8-22

performance improvement

performance issues

direct path loads, 8-16

PIECED keyword

SQL*Loader, 8-15

POSITION keyword

specification of field position, 5-15

tabs, 5-49

with multiple SQL*Loader INTO TABLE clauses, 5-48, 5-50, 5-53

precision of a numeric field versus length, 5-16

predetermined size fields

SQL*Loader, 5-83

predetermined size LOBs, 5-102

prerequisites

SQL*Loader, 3-15

PRESERVE BLANKS keyword

SQL*Loader, 5-87

presorting data for a direct path load

example, 4-25

PRIMARY KEY

constraints

effect on direct path load, 8-29

primary key OIDs

example, 4-44, 5-95

primary key REF columns, 5-97

primary keys

required for SQL*Loader, 3-15

privileges, 2-10

complete export, 1-45

creating for Export, 1-9

cumulative export, 1-45

Export and, 1-3

incremental export, 1-45

quotation marks

escaping, 5-20

filenames, 5-19

SQL string, 5-19

table names and, 1-26, 2-27

use with database object names, 5-18

RAW datatype, 5-59, 5-64

read-consistent export, 1-17

READBUFFERS keyword

SQL*Loader, 5-24, 8-15

read-only tablespaces

Import, 2-51

READSIZE

SQL*Loader command-line parameter, 6-7

real REF columns, 5-97

RECALCULATE_STATISTICS parameter, 2-25

RECNUM keyword

SQL*Loader, 5-47

use with SQL*Loader keyword SKIP, 5-55

recompiling

stored functions, procedures, and packages, 2-58

RECORD parameter, 1-23

RECORDLENGTH

Export parameter, 1-24

direct path export, 1-44

Import parameter, 2-26

records

recovery

direct path load

disabling for import, 2-14

replacing rows, 5-32

redo logs

direct path load, 8-14

instance and media recovery

SQL*Loader, 8-14

saving space

direct path load, 8-19

REENABLE keyword

REF data

REF fields

referential integrity constraints

specifying for SQL*Loader, 5-25

SQL*Loader, 8-20

refresh error

snapshots

Import, 2-48

reject files

rejected records

SQL*Loader, 3-12, 5-25

relative field positioning

where a field starts and SQL*Loader, 5-84

with multiple SQL*Loader INTO TABLE clauses, 5-51

remote operation

Export/Import, 1-53

REPLACE table

example, 4-15

replacing a table using SQL*Loader, 5-33

resource errors

restrictions

DB2 load utility, B-3

Export, 1-4

importing grants, 2-12

importing into another user's schema, 2-12

importing into own schema, 2-11

table names in Export parameter file, 1-26

table names in Import parameter file, 2-27

RESUME

DB2 keyword, 5-32

roles

EXP_FULL_DATABASE, 1-4, 1-9

IMP_FULL_DATABASE, 2-6, 2-22, 2-29

RESOURCE, 2-11

rollback segments

CONSISTENT Export parameter, 1-17

controlling size during import, 2-19

during SQL*Loader loads, 5-26

Export, 1-50

row errors

ROWID

Import, 2-48

ROWS

command-line parameter

Export parameter, 1-24

Import parameter, 2-26

performance issues

SQL*Loader command-line parameter, 6-7

rows

choosing which to load using SQL*Loader, 5-41

exporting, 1-24

specifying for import, 2-26

specifying number to insert before save

updates to existing rows with SQL*Loader, 5-33

schemas

export privileges, 1-4

specifying for Export, 1-25

scientific notation for FLOAT EXTERNAL, 5-67

script files

running before Export, 1-9, 1-63

SDFs. See secondary data files

secondary data files (SDFs)

SQL*Loader DDL support, 3-21

segments

temporary

FILE keyword in SQL*Loader, 8-28

SEQUENCE keyword

SQL*Loader, 5-56

sequence numbers

cached, 1-57

for multiple tables and SQL*Loader, 5-57

generated by SEQUENCE clause, 4-11

generated by SQL*Loader SEQUENCE clause, 5-56

generated, not read and SQL*Loader, 5-47

setting column to a unique number with SQL*Loader, 5-56

sequences, 2-46

C Language datatype, 5-59

short records with missing data

SQL*Loader, 5-43

SHORTINT

SHOW

Import parameter, 1-3, 2-26

SILENT

SQL*Loader command-line parameter, 6-8

single-byte character sets

Import, 2-53

SINGLEROW index keyword, 5-44, 8-20

single-table load

discontinued, 5-34

SKIP

effect on SQL*Loader RECNUM specification, 5-55

SQL*Loader, 5-35

SQL*Loader command-line parameter, 6-9

SQL*Loader control file keyword, 5-76

SKIP_INDEX_MAINTENANCE parameter, 6-9, 8-20

SKIP_UNUSABLE_INDEXES parameter, 2-26, 6-10, 8-20

SMALLINT datatype, 5-59

snapshot log

master table

restoring dropped

SORTED INDEXES clause

direct path loads, 5-44

example, 4-25

sorting

multiple-column indexes

optimum sort order

presorting in direct path load, 8-16

SORTED INDEXES clause

applying SQL operators to fields, 5-88

special characters, A-2

SQL

keywords, A-2

reserved words, A-2

special characters, A-2

SQL operators

applying to fields, 5-88

SQL strings

example, 4-28

new behavior in 8.1.5, 3-19

quotation marks, 5-19

SQL*Loader

appending rows to tables, 5-33

bad file, 3-12

BADDN keyword, 5-25

BADFILE keyword, 5-25

basics, 3-2

bind arrays and performance, 5-75

BINDSIZE command-line parameter, 5-76, 6-4

case studies, 4-1

associated files, 4-3

direct path load, 4-25

extracting data from a formatted report, 4-28

loading combined physical records, 4-15

loading data into multiple tables, 4-19

loading delimited, free-format files, 4-11

loading fixed-length data, 4-8

loading variable-length data, 4-5

choosing which rows to load, 5-41

command-line parameters, 6-2

CONCATENATE keyword, 5-36

concepts, 3-1

CONTINUE_LOAD keyword, 5-35

CONTINUEIF keyword, 5-36

CONTROL command-line parameter, 6-4

controlling memory use, 5-24

conventional path loads, 8-2

DATA command-line parameter, 6-4

data conversion, 3-9

data definition language

expanded syntax diagrams, 5-15

high-level syntax diagrams, 5-4

data definition language syntax, 5-3

datatype specifications, 3-9

DB2 load utility, B-1

DIRECT command-line parameter, 6-5, 8-10

direct path method, 3-15

discard file, 3-14

discarded records, 3-12

DISCARDFILE command-line parameter, 6-5

DISCARDFILE keyword, 5-28

DISCARDMAX command-line parameter, 6-5

DISCARDMAX keyword, 5-29

DISCARDS keyword, 5-29

errors caused by tabs, 5-49

ERRORS command-line parameter, 6-5

example sessions, 4-1

exclusive access, 8-25

FILE command-line parameter, 6-6

filenames, 5-18

index options, 5-43

inserting rows into tables, 5-32

INTO TABLE statement, 5-39

LOAD command-line parameter, 6-6

load methods, 8-2

loading data contained in the control file, 5-54

loading LONG data, 5-64

LOG command-line parameter, 6-6

log file datafile information, 7-3

log file entries, 7-1

log file header information, 7-2

log file summary statistics, 7-4

log file table information, 7-3

log file table load information, 7-4

log files, 3-14

methods of loading data, 3-15, 5-32

multiple INTO TABLE statements, 5-51

National Language Support, 5-30

object names, 5-18

PARALLEL command-line parameter, 6-6

parallel data loading, 8-25, 8-26, 8-30

PARFILE command-line parameter, 6-6

preparing tables for case studies, 4-4

READBUFFERS keyword, 5-24

READSIZE command-line parameter, 6-7

rejected records, 3-12

replacing rows in tables, 5-33

required privileges, 3-15

reserved words, A-2

ROWS command-line parameter, 6-7

SILENT command-line parameter, 6-8

SINGLEROW index keyword, 5-44

SKIP command-line parameter, 6-9

SKIP keyword, 5-35

SORTED INDEXES during direct path loads, 5-44

specifying columns, 5-47

specifying datafiles, 5-22

specifying field conditions, 5-44

specifying fields, 5-47

specifying more than one data file, 5-23

suppressing messages, 6-8

updating rows, 5-33

SQL*Loader log file

global information, 7-2

SQL*Loader restrictions on records, 3-18

SQL*Net. See Net8

SQL/DS option (DB2 file format)

not supported by SQL*Loader, B-4

STATISTICS

Export parameter, 1-24

statistics, 2-61

specifying for Export, 1-24, 2-25

STORAGE keyword, 8-29

storage parameters, 2-50

estimating export requirements, 1-9

exporting tables, 1-17

OPTIMAL parameter, 2-50

overriding

Import, 2-51

preallocating

direct path load, 8-16

temporary for a direct path load, 8-11

stored functions

stored packages

stored procedures

direct path load, 8-24

stream record format, 5-90

stream record format records, 3-6

string comparisons, 5-15

SQL*Loader, 5-46

subpartitioned

tables

loading, 8-6

synonyms

direct path load, 8-9

syntax

syntax diagrams

SYSDATE datatype

SYSDATE keyword

SYS.INCEXP table

SYS.INCFIL table

SYS.INCVID table

system objects

system tables

incremental export, 1-51

table-level Export, 1-8

table-level Import, 2-5, 2-31

table-mode Export

table-mode Import

Advanced Queuing

appending rows with SQL*Loader, 5-33

continuing a multiple-table load, 5-35

continuing a single-table load, 5-34

defining before Import, 2-13

definitions

creating before Import, 2-13

exclusive access during direct path loads

direct path load in SQL*Loader, 8-22

exporting

specifying, 1-25

importing, 2-27

insert triggers

inserting rows using SQL*Loader, 5-32

loading data into more than one table using SQL*Loader, 5-51

loading data into tables, 5-32

loading object tables, 5-95

maintaining consistency, 1-17

manually ordering for Import, 2-14

master table

name restrictions

nested

object import order, 2-3

partitioned, 1-8, 2-5

partitioned in DB2

no Oracle equivalent, B-4

replacing rows using SQL*Loader, 5-33

size

USER_SEGMENTS view, 1-9

specifying table-mode Export, 1-25

SQL*Loader method for individual tables, 5-40

system

incremental export, 1-51

truncating

updating existing rows using SQL*Loader, 5-33

TABLES parameter

Export, 1-25

Import, 2-27

tablespaces

dropping during import, 2-51

metadata

read-only

reorganizing

TABLESPACES parameter, 2-28

tabs

loading data files containing tabs, 5-49

trimming, 5-82

whitespace, 5-82

temporary segments, 8-28

FILE keyword

SQL*Loader, 8-28

not exported during backup, 1-50

temporary storage in a direct path load, 8-11

TERMINATED BY, 5-16

WHITESPACE

with OPTIONALLY ENCLOSED BY, 5-83

terminated fields

specified with a delimiter, 5-83

specified with delimiters and SQL*Loader, 5-70

TOID_NOVALIDATE parameter, 2-28

TOUSER

Import parameter, 2-29

trailing blanks

loading with delimiters, 5-73

TRAILING NULLCOLS

example, 4-28

SQL*Loader keyword, 5-43

trailing whitespace

trimming, 5-86

TRANSPORT_TABLESPACE parameter, 2-30

transportable tablespaces, 2-60

TRIGGERS

export parameter, 1-27

triggers, 8-22

database insert, 8-22

permanently disabled, 8-24

update

trimming

trailing whitespace

TTS_OWNERS parameter, 2-30

TYPE# column

effect on direct path load, 8-29

unique indexes

Import, 2-23

UNIQUE KEY

constraints

unique values

generating with SQL*Loader, 5-56

uniqueness constraints

preventing errors during import, 2-19

UNLOAD (DB2 file format)

not supported by SQL*Loader, B-4

UNRECOVERABLE keyword

unsorted data

direct path load

updating rows in a table

USER_SEGMENTS view

Export and, 1-9

USERID

Export parameter, 1-27

Import parameter, 2-30

user-mode Export

specifying, 1-22

users

definitions

importing, 2-13

VARCHAR datatype, 5-59

SQL*Loader, 5-62

VARCHAR2 datatype, 2-62

variable record format, 5-91

VARCHARC datatype

SQL*Loader, 5-68

VARGRAPHIC datatype

SQL*Loader, 5-61

variable records, 3-5

VARRAW datatype, 5-63

VARRAWC datatype, 5-68

VARRAY Columns

memory issues when loading, 5-111

varrays

example, 4-44

views

creating views necessary for Export, 1-9

Export, 1-50

VOLSIZE

export parameter, 2-31

Import parameter, 1-28

warning messages, 1-40

WHEN clause

example, 4-19

SQL*Loader, 5-41, 5-44

SQL*Loader discards resulting from, 5-29

whitespace

included in a field, 5-85

leading, 5-82

terminating a field, 5-85

trailing, 5-82

trimming, 5-82

WHITESPACE keyword, 5-16

ZONED datatype, 5-60

EXTERNAL format