Index

Import, 2-12

advanced queue (AQ) tables

exporting, 1-49

importing, 2-61

aliases

B

backslash (\)

escape character in quoted strings

SQL*Loader, 5-13

quoted filenames and

SQL*Loader, 5-14

backups

restoring dropped snapshots

SQL*Loader command-line parameter, 6-3

BAD

bad file

rejected records

specifying

BADDN keyword

BADFILE keyword

base backup

base tables

incremental export and, 1-41

BEGINDATA

control file keyword

SQL*Loader, 5-15

BFILE columns

bind array

determining size

determining size of

SQL*Loader, 5-67

minimizing memory requirements

SQL*Loader, 5-70

minimum requirements

no space required for generated data

performance implications

SQL*Loader command-line parameter, 6-3

size with multiple INTO TABLE clauses

SQL*Loader, 5-70

specifying number of rows

conventional path load

SQL*Loader, 6-6

specifying size

SQL*Loader, 6-3

BINDSIZE

BINDSIZE command-line parameter

blanks

BLANKS keyword for field comparison

SQL*Loader, 5-7, 5-38

loading fields consisting of blanks

preserving

direct path export, 1-34, 1-36

trailing

loading with delimiters

trimming

whitespace

BLANKS keyword

BUFFER

Export parameter, 1-13

Import parameter, 2-19

buffers

calculating for export, 1-13

calculating for import, 2-19

space required by

LONG DATA

VARCHAR data

SQL*Loader, 5-56

specifying with BINDSIZE parameter

SQL*Loader, 5-67

BYTEINT datatype, 5-51

specification

SQL*Loader, 5-52

C

cached sequence numbers

Export, 1-46

case studies

SQL*Loader, 4-1

associated files, 4-3

file names, 4-3

preparing tables, 4-4

CATALOG.SQL

preparing database for Import, 2-7

CATEXP7.SQL

preparing database for Export, 1-50

CATEXP.SQL

preparing database for Import, 2-7

CATLDR.SQL

setup script

SQL*Loader, 8-9

CHAR columns

Version 6 export files, 2-63

CHAR datatype

delimited form

reference

specification

trimming whitespace

character datatypes

conflicting fields

character fields

datatypes

delimiters

SQL*Loader, 5-60

determining length

specified with delimiters

during Export/Import, 1-45, 2-55

character sets

conversion between

direct path export, 1-36, 1-45

eight-bit to seven-bit conversions

Export/Import, 1-46, 2-56

multi-byte

Export/Import, 1-46, 2-56

Export/Import, 1-46, 2-55

NCHAR data

Export, 1-45

single-byte

Version 6 conversions

Import/Export, 2-56

character strings

as part of a field comparison

SQL*LOader, 5-7

padded

when shorter than field

CHARACTERSET keyword

CHARSET

Import parameter, 2-20

check constraints

exporting LONG datatypes, 1-47

clusters

Export, 1-42

columns

naming

null columns at the end of a record

reordering before Import, 2-15

setting to a constant value

SQL*Loader, 5-46

setting to a unique sequence number

setting to datafile record number

setting to null

setting to null value

setting to the current date

setting value to zero

specifiying as PIECED

SQL*Loader, 8-14

specifying

in Export parameter file, 1-10

combining partitions, 2-31

command-line parameters

description

specifying defaults

Comments

in Import parameter file, 2-11

in SQL*Loader control file, 4-12

COMMIT

Import parameter, 2-20

complete exports, 1-37, 1-39

completion messages

Export parameter, 1-13, 2-53

COMPRESS

COMPUTE option

STATISTICS Export parameter, 1-19

CONCATENATE keyword

concurrent conventional path loads, 8-24

connect string

Net8, 1-44

CONSISTENT

Export parameter, 1-14

nested table and, 1-14

partitioned table and, 1-14

consolidating extents

Export parameter COMPRESS, 1-13

CONSTANT keyword

no space used in bind array

SQL*Loader, 5-40, 5-46

CONSTRAINTS

Export parameter, 1-15

constraints

automatic

SQL*Loader, 8-22

check

disabling during a direct load, 8-20

direct path load, 8-20

disabling referential constraints

Import, 2-15

enabling after a direct load, 8-20

enforced on a direct load, 8-20

failed

load method

SQL*Loader, 8-8

not null

preventing Import errors due to uniqueness constraints, 2-21

referential integrity

uniqueness

continuation fields

CONTINUE_LOAD keyword

CONTINUEIF keyword

example

SQL*Loader, 4-14

continuing interrupted loads

CONTROL

control files

CONTROL

creating

compared to direct path Export, 1-33

data definition language syntax

SQL*Loader, 5-4

data definitions

basics

definition

editing

field delimiters

guidelines for creating

SQL*Loader, 3-4

location

SQL*Loader, 3-5

specifying data

SQL*Loader, 5-15

specifying discard file

SQL*Loader, 5-21

storing

SQL*Loader, 3-5

conventional path Export

conventional path loads

basics, 8-2

bind array

compared to direct path loads, 8-7

using, 8-3

CREATE SESSION privilege

Import, 2-12

CREATE USER command

Import, 2-14

CTIME column

cumulative exports, 1-37, 1-38

recording, 1-18

SYS.INCFIL table, 1-44

D

DATA

data

binary versus character format

delimiter marks in data

SQL*Loader, 5-62

distinguishing different input formats

enclosed

formatted data

generating unique values

including in control files

SQL*Loader, 5-15

loading in sections

SQL*Loader, 8-14

loading into more than one table

loading LONG

loading without files

SQL*Loader, 5-46

mapping to Oracle format

maximum length of delimited data

methods of loading into tables

SQL*Loader, 5-25

moving between operating systems

saving in a direct path load, 8-12

saving rows

terminated

SQL*Loader, 3-10

unsorted

values optimized for performance

SQL*Loader, 5-46

data conversion

description

SQL*Loader, 3-10

data definition language

basics

BEGINDATA keyword, 5-15

BLANKS keyword

SQL*Loader, 5-38

CHARACTERSET keyword, 5-24

column_name

CONSTANT keyword, 5-40, 5-46

CONCATENATE keyword, 5-29

CONTINUEIF keyword, 5-29

datatype_spec

date mask

DEFAULTIF keyword

delimiter_spec

description

DISABLED_CONSTRAINTS keyword

DISCARDDN keyword, 5-21

DISCARDMAX keyword

example definition

SQL*Loader, 3-5

EXCEPTIONS keyword

EXTERNAL keyword, 5-60

field_condition

FILE keyword

length

loading data in sections

NULLIF keyword

parallel keyword

pos_spec

POSITION keyword, 5-40

precision

RECNUM keyword, 5-40

REENABLE keyword

reference

keywords and parameters

SQL*Loader, 5-1

SEQUENCE keyword, 5-48

syntax diagrams

expanded

high-level

SQL*Loader, 5-4

syntax reference

SQL*Loader, 5-1

SYSDATE keyword, 5-47

TERMINATED keyword, 5-61

UNRECOVERABLE keyword

data definition language (DDL), 3-3

WHITESPACE keyword, 5-61

data field

specifying the datatype

data mapping

concepts

direct and conventional, 8-2

data path loads

data recovery

direct path load

privileges for export, 1-3

database administrator (DBA)

database objects

export privileges, 1-3

exporting LONG columns, 1-47

transferring across a network

Import, 2-50

databases

data structure changes

incremental export and, 1-41

full export, 1-16

full import, 2-23

importing into secondary

Import, 2-21

incremental export, 1-37

preparing for Export, 1-6

preparing for Import, 2-7

privileges for exporting, 1-3

privileges for importing, 2-11

reducing fragmentation via full export/import, 2-47

reusing existing data files

Import, 2-21

datafiles

preventing overwrite during import, 2-21

reusing during import, 2-21

specifying

SQL*Loader, 5-16, 6-4

specifying buffering

specifying format

storage

conflicting character datatype fields, 5-63

datatypes

BFILE

CHAR, 5-58

converting

SQL*Loader, 3-10, 5-50

DATE, 5-58

determining length, 5-64

default

determining character field lengths

FLOAT, 5-52

GRAPHIC EXTERNAL, 5-54

LONG

MLSLABEL

Trusted Oracle7 Server, 5-59

native

conflicting length specifications

SQL*Loader, 5-57

inter-operating system transfer issues, 5-65

SQL*Loader, 3-9, 5-51

NUMBER

SQL*Loader, 5-50

numeric EXTERNAL, 5-60

trimming

RAW, 5-54

specifications

specifying

specifying the datatype of a data field

VARCHAR2

ZONED, 5-52

DATE datatype

delimited form

determining length

mask

specification

trimming whitespace

date mask

DB2 Load utility

use with SQL*Loader, 3-4

DB2 load utility, B-1

different placement of statements

DISCARDDDN, B-4

DISCARDS, B-4

restricted capabilities of SQL*Loader, B-4

RESUME keyword

SQL*Loader equivalents, 5-26

SQL*Loader compatibility

ignored statements, B-3

DBA role

EXP_FULL_DATABASE role, 1-7

DBCS (DB2 double-byte character set)

not supported by Oracle, B-5

DDL, 3-5

SQL*Loader data definition language, 3-3

DECIMAL datatype

EXTERNAL format

trimming whitespace

length and precision

specification

DEFAULT column values

Oracle Version 6 export files, 2-63

DEFAULTIF keyword

field condition

DELETE ANY TABLE privilege

DELETE CASCADE

SQL*Loader, 5-26, 5-27

DELETE privilege

delimited data

maximum length

delimited fields

field length

delimited files

delimiter_spec

delimiters

control files

enclosure

field specifications

initial and trailing

loading trailing blanks

marks in data

SQL*Loader, 5-62

optional enclosure

specifying

SQL*Loader, 5-35, 5-60

termination

Export parameter, 1-15, 1-34

DESTROY

Import parameter, 2-21

DIRECT

RECORDLENGTH parameter, 1-36

direct path export, 1-33

BUFFER parameter, 1-36

character set and, 1-45

invoking, 1-34

performance, 1-36

direct path load

, 8-10

advantages, 8-6

case study, 4-24

choosing sort order

compared to convetional path load, 8-7

conditions for use, 8-7

data saves, 8-12, 8-18

DIRECT

DISABLED_CONSTRAINTS keyword, 8-21

DIRECT command line parameter

SQL*Loader, 8-9

disabling media protection

dropping indexes, 8-19

to continue an interrupted load

directory aliases

DISABLED_CONSTRAINTS keyword

DISCARD

different placement from DB2, B-4

discard file

basics

SQL*Loader, 3-15

DISCARDDN keyword

DISCARDFILE keyword

DISCARDMAX keyword

DISCARDS control file clause

different placement from DB2, B-4

DISCARDS keyword

DISCRDMAX keyword

SQL*Loader, 5-21

discarded records

causes

discard file

limiting the number

DISCARDMAX

DISCARDMAX keyword

discarded records

discontinued loads

continuing

DOUBLE datatype, 5-51

specification

SQL*Loader, 5-52

dropped snapshots

eight-bit character set support, 1-46, 2-56

dropping

indexes

to continue a direct path load

SQL*LOader, 5-28

E

EBCDIC character set

Import, 2-55

enclosed fields

and SQL*Loader, 3-10

ENCLOSED BY control file clause

specified with enclosure delimiters

whitespace in

enclosure delimiters

error handling

error messages

caused by tab characters in data

SQL*Loader, 5-42

Export, 1-32

export log file, 1-17

fatal errors

generated by DB2 load utility, B-4

row errors during import, 2-48

warning errors

Export, 1-32

ERRORS

errors

fatal

Import resource errors, 2-49

Import, 2-49

LONG data, 2-48

object creation

Import parameter IGNORE, 2-23

object creation errors, 2-49

warning

escape character

quoted strings

ESTIMATE option

STATISTICS Export parameter, 1-19

EXCEPTIONS keyword

EXP_FULL_DATABASE role, 1-16

assigning, 1-7

Import, 2-12

EXPDAT.DMP

Export output file, 1-16

EXPID column

Export

base backup, 1-37

BUFFER parameter, 1-13

CATALOG.SQL

preparing the database for Version 7 export, 1-50

CATEXP7.SQL

CATEXP.SQL

command line, 1-7

complete, 1-17, 1-37, 1-39

privileges, 1-37

CONSTRAINTS parameter, 1-15

COMPRESS parameter, 1-13

CONSISTENT parameter, 1-14

creating necessary privileges, 1-7

creating Version 7 export files, 1-49

cumulative, 1-17, 1-37, 1-38

privileges required, 1-37

database optimizer statistics, 1-19

data structures, 1-41

DIRECT parameter, 1-15

direct path, 1-33

displaying help message, 1-17

eight-bit vs. seven-bit character sets, 1-46

establishing export views, 1-6

examples, 1-22

full database mode, 1-22

partition-level, 1-27

table mode, 1-25

user mode, 1-24

exporting an entire database, 1-16

exporting indexes, 1-17

exporting sequence numbers, 1-46

exporting to another operating system

RECORDLENGTH parameter, 1-18, 2-26

FEEDBACK parameter, 1-16

FILE parameter, 1-16

full database mode

example, 1-22

FULL parameter, 1-16

GRANTS parameter, 1-16

HELP parameter, 1-17

incremental, 1-17, 1-37

INCTYPE parameter, 1-17

INDEXES parameter, 1-17

interactive method, 1-8, 1-29

invoking, 1-7

kinds of data exported, 1-41

last valid export

log files

logging error messages, 1-17

LOG parameter, 1-17

LONG columns, 1-47

message log file, 1-32

modes, 1-4

multi-byte character sets, 1-46

network issues, 1-44

NLS support, 1-46

NLS_LANG environment variable, 1-46

objects exported, 1-4

online help, 1-9

OWNER parameter, 1-17

parameter conflicts, 1-21

parameter file, 1-7, 1-10, 1-17

maximum size, 1-10

parameters, 1-11

PARFILE parameter, 1-7, 1-10, 1-17

POINT_IN_TIME_RECOVER, 1-18

preparing database, 1-6

previous versions, 1-49

RECORD parameter, 1-18

RECORDLENGTH parameter, 1-18

RECOVERY_TABLESPACES parameter, 1-19

redirecting output to a log file, 1-32

remote operation, 1-44

restrictions, 1-3

rollback segments, 1-42

ROWS parameter, 1-19

sequence numbers, 1-46

STATISTICS parameter, 1-19

storage requirements, 1-7

SYS.INCFIL table, 1-44

table name restrictions, 1-20

table mode

example, 1-25

TABLES parameter, 1-19

tracking exported objects, 1-43

transferring export files across a network, 1-44

user access privileges, 1-3

user mode

examples, 1-24

importing the entire file, 2-23

USER_SEGMENTS view, 1-7

USERID parameter, 1-21

using, 1-6

warning messages, 1-32

export file

displaying contents, 1-3

listing contents before importing, 2-27

reading, 1-3

specifying, 1-16

extent allocation

FILE

SQL*Loader command line parameter, 6-5

extents

consolidating into one extent

Export, 1-13

importing consolidated, 2-53

EXTERNAL datatypes

DECIMAL

FLOAT

GRAPHIC

numeric

determining length

trimming

ZONED

external files

EXTERNAL keyword

F

fatal errors

Import, 2-48, 2-49

FEEDBACK

Import parameter, 2-22

field conditions

specifying

field length

specifications

fields

and SQL*Loader, 3-10

character

data length

comparing

comparing to literals

continuation

DECIMAL EXTERNAL

trimming whitespace

delimited

determining length

specifications

enclosed

FLOAT EXTERNAL

trimming whitespace

INTEGER EXTERNAL

trimming whitespace

length of

loading all blanks

location

numeric and precision versus length

numeric EXTERNAL

trimming whitespace

precision

predetermined size

length

SQL*Loader, 5-64

relative positioning

specification of position

specified with a termination delimiter

SQL*Loader, 5-61

specified with enclosure delimiters

SQL*Loader, 3-10, 5-61

specifying

specifying default delimiters

terminated

SQL*Loader, 5-61

VARCHAR

never trimmed

ZONED EXTERNAL

trimming whitespace

FIELDS clause

terminated by whitespace

SQL*Loader, 5-76

FILE

Import parameter, 2-22

keyword

SQL*Loader, 8-27

FILE columns

Import, 2-60

filenames

bad file

quotation marks

SQL*Loader, 5-13

specifying more than one

SQL*Loader, 5-17

SQL*Loader, 5-12

files

file processing options string

logfile

SQL*Loader

storage

fixed format records

fixed-format records

vs. variable

EXTERNAL format

trimming whitespace

specification

FLOAT EXTERNAL data values

SQL*Loader, 5-60

FLOAT keyword

SQL*Loader, 5-60

foreign function libraries

not allowed by SQL*Loader, B-5

FORMAT statement in DB2

formats

and input records

SQL*Loader, 5-44

formatting errors

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

fragmentation

FROMUSER

Import parameter, 2-22

FTP

Export files, 1-44

FULL

full database mode

Import, 2-23

G

GRANTS

grants

exporting, 1-16

importing, 2-13, 2-23

GRAPHIC datatype, 5-51

EXTERNAL format

SQL*Loader, 5-54

specification

GRAPHIC EXTERNAL datatype, 5-51

SQL*Loader, 5-54

H

HELP

help

Export, 1-9

Import, 2-10

hexadecimal strings

as part of a field comparison

padded

when shorter than field

I

IGNORE

object identifiers and, 2-57

existing objects, 2-49

IMP_FULL_DATABASE role, 2-23

created by CATEXP.SQL, 2-7

Import, 2-12, 2-29

Import, 2-1

ANALYZE parameter, 2-19

backup files, 2-51

BUFFER parameter, 2-19

CATALOG.SQL

preparing the database, 2-7

CATEXP.SQL

preparing the database, 2-7

character set conversion, 1-45, 2-55

character sets, 2-55

CHARSET parameter, 2-20

COMMIT parameter, 2-20

committing after array insert, 2-20

compatibility, 2-5

complete export file, 2-43

consolidated extents, 2-53

controlling size of rollback segments, 2-21

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

creating an index-creation SQL script, 2-25

cumulative, 2-43

data files

reusing, 2-21

database

reusing existing data files, 2-21

DESTROY parameter, 2-21

disabling referential constraints, 2-15

displaying online help, 2-23

dropping a tablespace, 2-54

error handling, 2-48

errors importing database objects, 2-48

example session, 2-33

export COMPRESS parameter, 2-53

export file

importing the entire file, 2-23

listing contents before import, 2-27

failed integrity constraints, 2-48

fatal errors, 2-48, 2-49

FEEDBACK parameter, 2-22

FILE parameter, 2-22

FROMUSER parameter, 2-22

full database mode

specifying, 2-23

FULL parameter, 2-23

grants

specifying for import, 2-23

GRANTS parameter, 2-23

HELP parameter, 2-10, 2-23

IGNORE parameter, 2-23, 2-49

IMP_FULL_DATABASE role, 2-12

importing grants, 2-13, 2-23

importing objects into other schemas, 2-14

importing rows, 2-27

importing tables, 2-27

incremental, 2-43

specifying, 2-24

INCTYPE parameter, 2-24

INDEXES parameter, 2-25

INDEXFILE parameter, 2-25

INSERT errors, 2-48

interactive method, 2-41

into a secondary database, 2-21

invalid data, 2-48

invoking, 2-7

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

log files

LOG parameter, 2-26

LONG columns, 2-61

manually ordering tables, 2-15

modes, 2-5

NLS considerations, 2-55

NLS_LANG environment variable, 2-56

object creation errors, 2-23

objects imported, 1-4

OPTIMAL storage parameter, 2-52

Oracle Version 6 integrity constraints, 2-63

parameter file, 2-11, 2-26

parameters, 2-16

PARFILE parameter, 2-26

POINT_IN_TIME_RECOVER parameter, 2-26

preparing the database, 2-7

privileges required, 2-11, 2-12

read-only tablespaces, 2-53

recompiling stored procedures, 2-60

RECORDLENGTH parameter, 2-26

records

specifying length, 2-26

reducing database fragmentation, 2-47

refresh error, 2-51

reorganizing tablespace during, 2-54

resource errors, 2-49

restrictions, 2-53

rows

specifying for import, 2-27

ROWS parameter, 2-27

schema objects, 2-12, 2-14

schemas

specifying for import, 2-22

sequences, 2-49

SHOW parameter, 1-3, 2-27

single-byte character sets, 2-55

SKIP_UNUSABLE_INDEXES parameter, 2-27

snapshot log, 2-50

snapshot master table, 2-51

snapshots, 2-50

restoring dropped, 2-51

specifying by user, 2-22

specifying index creation commands, 2-25

specifying the export file, 2-22

SQL*Net See Net8

statistics on imported data, 2-62

storage parameters

overriding, 2-53

stored functions, 2-60

stored packages, 2-60

stored procedures, 2-60

system objects, 2-14, 2-23

table objects

import order, 2-4

tables created before import, 2-15

TABLES parameter, 2-27

tablespaces, 2-21

TOUSER parameter, 2-29

transferring files across networks, 2-50

Trusted Oracle and, 2-2

unique indexes, 2-24

uniqueness constraints

preventing import errors, 2-21

user definitions, 2-14

user mode

specifying, 2-29

USERID parameter, 2-29

using Oracle Version 6 files, 2-63

incremental export, 1-37

backing up data, 1-42

session example, 1-42

SYS.INCFIL table, 1-44

incremental import

parameter, 2-24

specifying, 2-24

INCTYPE

Import parameter, 2-24

INDDN keyword

SQL*Loader, 5-16

index options

SINGLEROW keyword

indexes left in Index Unusable state, 8-11

SORTED INDEXES

SQL*Loader, 5-36

Index Unusable state

INDEXES

left in direct load state, 8-11

Import parameter, 2-25

indexes

creating manually, 2-25

direct path load

dropping

before continuing a direct path load

index-creation commands

Import, 2-25

left direct load state

multiple column

presorting data

skipping unusable, 2-27

SQL*Loader, 5-36

state after discontinued load

unique, 2-24

INDEXFILE

Import parameter, 2-25

INFILE keyword

SQL*Loader, 5-16

INIT.ORA file

Export, 1-36

insert errors

specifying allowed number before termination

SQL*Loader, 6-5

INSERT into table

INTEGER datatype, 5-51

EXTERNAL format, 5-60

trimming whitespace

specification

Oracle Version 6 export files, 2-63

integrity constraints

failed on Import, 2-48

load method

SQL*Loader, 8-8

interactive method

Export, 1-29

Import, 2-41

interrupted loads

continuing

INTO TABLE clause

effect on bind array size

SQL*Loader, 5-70

INTO TABLE statement

column names

discards

multiple

invalid data

invalid objects

warning messages

ITIME column

K

key values

generating

key words, A-2

L

language support

Export, 1-45

Import, 2-55

leading whitespace

definition

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

trimming

SQL*Loader, 5-75

length

length indicator

determining size

SQL*loader, 5-68

length of a numeric field

length subfield

VARCHAR DATA

SQL*Loader, 5-56

libraries

foreign function

LOAD

delimited, free-format files, 4-11, 4-32

combined physical records

SQL*Loader, 4-14

datafiles containing TABs

SQL*Loader, 5-41

fixed-length data, 4-8

negative numbers

SQL*Loader, 4-14

variable-length data, 4-5

LOB data, 1-7

compression, 1-13

Export, 1-47

LOG

Export parameter, 1-17, 1-32

Import parameter, 2-26

log file

specifying

SQL*Loader, 6-6

log files

after a discontinued load

datafile information

global information

header Information

summary statistics

table information

table load information

SQL*Loader, 7-4

logical records

consolidating multiple physical records into

loading with direct path load, 8-14

versus physical records

LONG data

LONG FLOAT

C language datatype, 5-52

LXBCNF executable, 9-16

LXEGEN executable, 9-20

LXINST executable, 9-2

M

master table

snapshots

media protection

disabling for direct path loads

media recovery

direct path load, 8-13

memory

controlling usage

merging partitions in a table, 2-7, 2-31

messages

Export, 1-32

Import, 2-47

migrating data across partitions, 2-31

missing data columns

Trusted Oracle7 Server, 5-59

MLSLABEL datatype

SQL*Loader

mode

full database

Export, 1-16, 1-22

Import, 2-23

objects exported by each, 1-4

table

Export, 1-19, 1-25

Import, 2-27

user

Export, 1-17, 1-24

multi-byte character sets

blanks

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

multiple CPUs

multiple table load

control file specification

discontinued

generating unique sequence numbers for

SQL*Loader, 5-49

multiple-column indexes

N

NAME column

National Language Support

NLS Configuration Utility, 9-16

National Language Support (NLS)

data object files, 9-4

Export, 1-45

Import, 2-20, 2-55

NLS Data Installation Utility, 9-2

native datatypes

and SQL*Loader, 5-51

binary versus character data

transporting Export files across a network, 1-44

conflicting length specifications

SQL*Loader, 5-57

delimiters

SQL*Loader, 5-51

inter-operating system transfer issues

NCHAR data

negative numbers

nested tables

consistency and, 1-14

importing, 2-58

networks

Export, 1-44

Import and, 2-50

NLS

See National Language Support (NLS)

NLS Calendar Utility, 9-20

NLS Data Installation Utility, 9-2

NLS_LANG

environment variable

STATISTICS Export parameter, 1-19

NLS_LANG environment variable

Export, 1-46

Import, 2-56

NONE option

non-fatal errors

warning messages, 1-32

normalizing data during a load

SQL*Loader, 4-18

NOT NULL constraint

load method

SQL*Loader, 8-8

null columns

at end of record

setting

null data

missing columns at end of record

unspecified columns

NULLIF keyword

field condition

SQL*Loader, 5-71, 5-72

NULLIF...BLANKS

case study, 4-25

NULLIF...BLANKS keyword

SQL*Loader, 5-38

NUMBER datatype

SQL*Loader, 5-50

numeric EXTERNAL datatypes

binary versus character data

delimited form

determining length

trimming

trimming whitespace

numeric fields

precision versus length

considerations for Importing, 2-57

O

object identifiers

object names

object tables

object type definitions

exporting, 1-48

importing, 2-58

objects

creation errors, 2-48, 2-49

identifiers, 2-57

ignoring existing objects during import, 2-23

import creation errors, 2-23

privileges, 2-12

restoring sets

Import, 2-43

online help

Export, 1-9

Import, 2-10

operating systems

moving data to different systems

OPTIMAL storage parameter, 2-52

optimizing

direct path loads, 8-15

input file processing

exporting database objects, 2-63

OPTIONALLY ENCLOSED BY

SQL*Loader, 5-10, 5-74

OPTIONS keyword

for parallel loads

SQL*Loader, 5-34

SQL*Loader, 5-11

Oracle Version 6

Oracle7

creating export files with, 1-50

ORANLS option, 9-2, 9-16

output file

specifying for Export, 1-16

OWNER

OWNER# column

P

packed decimal data

padding of literal strings

PARALLEL

PARALLEL keyword

SQL*Loader, 8-26

parallel loads

allocating extents

FILE

PARALLEL

conflicts between export parameters, 1-21

parameter file

maximum size

parameters

ANALYZE

BUFFER

CHARSET

COMMIT

CONSTRAINTS

DESTROY

DIRECT

FEEDBACK

FILE

FROMUSER

FULL

GRANTS

HELP

IGNORE

INCTYPE

INDEXES

INDEXFILE

LOG, 1-32

OWNER

PARFILE

POINT_IN_TIME_RECOVER

RECORD

RECORDLENGTH

RECOVERY_TABLESPACES

ROWS

SHOW

SKIP_UNUSABLE_INDEXES

STATISTICS

TABLES

TOUSER

USERID

PARFILE

Export command-line option, 1-7, 1-10, 1-17

Import command-line option, 2-11, 2-26

not allowed by SQL*Loader, B-5

PART statement in DB2

partitioned load

concurrent conventional path loads, 8-24

export consistency and, 1-14

partitioned table

exporting, 1-6

importing, 2-6, 2-31, 2-33

partitioned tables in DB2

no Oracle equivalent, B-5

partition-level Export, 1-6

examples, 1-27

partition-level Import, 2-30

guidelines, 2-30

merging partitions, 2-31

reconfiguring partitions, 2-32

specifying, 1-19

passwords

hiding, 2-8

performance

direct path Export, 1-33, 1-36

direct path loads, 8-15

Import, 2-21

optimizing reading of data files

partitioned load

conventional path for small loads, 8-21

performance improvement

physical versus logical records

PIECED keyword

SQL*Loader, 8-14

POINT_IN_TIME_RECOVER

Export parameter, 1-18

Import parameter, 2-26

POSITION keyword

specification of field position

tabs, 5-41

with multiple INTO TABLE clauses

SQL*Loader, 5-42, 5-45

precision of a numeric field versus length

predetermined size fields

Send Us Your Comments, xix

preface

prerequisites

SQL*Loader, 3-16

PRESERVE BLANKS keyword

presorting data for a direct path load

case study, 4-24

primary keys

privileges

complete export, 1-37

creating for Export, 1-7

cumulative export, 1-37

DELETE

DELETE ANY TABLE

required for SQL*Loader, 3-16

Export and, 1-3

granted to others, 2-13

Import, 2-11, 2-12

incremental export, 1-37

See also grants, roles

SQL*Loader and Trusted Oracle7 Server, 3-16

Q

quotation marks

backslash

escape character

in filenames

filenames

SQL string

table names and, 1-20, 2-28

use with database object names

SQL*Loader, 5-12

R

RAW datatype, 5-51

specification

read-consistent export, 1-14

SQL*Loader, 5-54

READBUFFERS keyword

SQL*Loader, 5-18, 8-15

read-only tablespaces

Import, 2-53

RECNUM keyword

no space used in bind array

use with SKIP

stored functions, procedures, and packages, 2-60

recompiling

reconfiguring partitions, 2-32

RECORD

Export parameter, 1-18

RECORDLENGTH

Export parameter, 1-18

direct path export, 1-34, 1-36

Import parameter, 2-26

records

consolidating into a single logical record

discarded

DISCARD

DISCARDMAX

discarded by SQL*Loader, 3-13, 5-23

SQL*Loader, 5-21

distinguishing different formats

SQL*Loader, 5-44

extracting multiple logical records

fixed format

null columns at end

physical versus logical

rejected

rejected by SQL*Loader, 3-13

rejected by Oracle

SQL*Loader, 3-15

setting column to record number

short

missing data columns

skipping

SQL*Loader, 6-8

specifying how to load

LOAD

specifying length for export, 1-18, 2-26

specifying length for import, 2-26

stream format

variable format

RECOVERABLE keyword

recovery

direct path load

replacing rows, 5-26

RECOVERY_TABLESPACES parameter

Export, 1-19

redo log files

direct path load, 8-13

instance and media recovery

saving space

direct path load, 8-18

REENABLE keyword

REF data

exporting, 1-13

importing, 2-59

referential integrity constraints

disabling for import, 2-15

SQL*Loader, 8-20

refresh error

snapshots

reject file

specifying

rejected records

SQL*Loader, 3-13, 5-19

relative field positioning

where a field starts

with multiple INTO TABLE clauses

SQL*Loader, 5-44

remote operation

Export/Import, 1-44

REPLACE table

example, 4-14

to replace table during a load

resource errors

restrictions

DB2 load utility, B-4

importing into another user's schema, 2-14

Import, 2-53

importing grants, 2-13

importing into own schema, 2-12

table names in Export parameter file, 1-20

table names in Import parameter file, 2-28

RESUME

DB2 keyword

SQL*Loader equivalents, 5-26

roles

EXP_FULL_DATABASE, 1-3, 1-7

IMP_FULL_DATABASE, 2-7, 2-12, 2-23, 2-29

RESOURCE, 2-12

rollback segments

CONSISTENT Export parameter, 1-14

controlling size during import, 2-21

during loads

row errors

ROWID

ROWS

command line parameter

SQL*Loader, 8-12

Export parameter, 1-19

Import parameter, 2-27

performance issues

specifying for import, 2-27

rows

choosing which to load

SQL*Loader, 5-34

exporting, 1-19

specifying number to insert before save

SQL*Loader, 8-12

updates to existing

specifying for Export, 1-19

S

schemas

export privileges, 1-3

specifying for import, 2-22

scientific notation for FLOAT EXTERNAL, 5-60

script files

preparing database for Import, 2-7

running before Export, 1-6, 1-50

secondary database

importing, 2-21

segments

temporary

FILE keyword

SQL*Loader, 8-27

Send Us Your Comments

SEQUENCE keyword

sequence numbers

for multiple tables

generated by SEQUENCE clause

example, 4-11

generated, not read

no space used in bind array

setting column to a unique number

sequences, 2-49

exporting, 1-46

short records with missing data

C Language datatype, 5-52

SHORTINT

SHOW

Import parameter, 1-3, 2-27

SILENT

SQL*Loader command-line parameter, 6-7

single table load

discontinued

single-byte character sets

Import, 2-55

SINGLEROW

SKIP

control file keyword

effect on RECNUM specification

SQL*Loader command-line parameter, 6-8

SKIP_UNUSABLE_INDEXES parameter

Import, 2-27

SMALLINT datatype, 5-51

specification

snapshot log

snapshots

log

master table

restoring dropped

SORTED INDEXES

direct path loads

sorting

multiple column indexes

optimum sort order

presorting in direct path load, 8-16

SORTED INDEXES statement

SQL*Loader, 8-16

special characters, A-2

SQL

key words, A-2

reserved words, A-2

special characters, A-2

SQL operators

applying to fields

SQL string

applying SQL operators to fields

appending rows to tables, 5-26

example of, 4-27

quotation marks

SQL*Loader, 5-13

SQL*Loader

BAD

command-line parameter, 6-3

bad file, 3-13

BADDN keyword, 5-19

BADFILE keyword, 5-19

basics, 3-2

bind arrays and performance, 5-66

BINDSIZE

command-line parameter, 6-3

BINDSIZE command-line parameter, 5-66

case studies, 4-1

associated files, 4-3

direct path load, 4-24

extracting data from a formatted report, 4-27

loading combined physical records, 4-14

loading data into multiple tables, 4-18

loading delimited, free-format files, 4-11, 4-32

loading fixed-length data, 4-8

loading variable-length data, 4-5

preparing tables, 4-4

choosing which rows to load, 5-34

command-line arguments, 6-3

command-line parameters, 6-2

summary, 6-2

CONCATENATE keyword, 5-29

concepts, 3-1

concurrent sessions, 8-26

CONTINUE_LOAD keyword, 5-29

CONTINUEIF keyword, 5-29

CONTROL

controlling memory usage, 5-18

control file

creating, 3-3

conventional path loads, 8-2

DATA

expanded syntax diagrams, 5-7

data conversion, 3-10

data definition language

high-level syntax diagrams, 5-4

data definition language (DDL), 3-5

data definition language syntax, 5-4

data mapping concepts, 3-3

datafiles

specifying, 5-16

datatype specifications, 3-10

DB2 load utility, B-1

DDL syntax reference, 5-1

delimiters, 3-10

DIRECT

DIRECT command line parameter, 8-9

DISCARD

discard file, 3-15

discarded records, 3-13

DISCARDFILE keyword, 5-21

DISCARDMAX

DISCARDMAX keyword, 5-23

DISCARDS keyword, 5-23

enclosed data, 3-10

ERRORS

errors caused by tabs, 5-41

example sessions, 4-1

exclusive access, 8-24

fields, 3-10

FILE

filenames, 5-12

index options, 5-36

INTO TABLE statement, 5-33

keywords and parameters

reference, 5-1

LOAD

loading data without files, 5-46

load methods, 8-2

loading data

direct path method, 3-16

loading LONG data, 5-58

LOG

table load information, 7-4

log file

datafile information, 7-4

global information, 7-2

header information, 7-2

summary statistics, 7-5

table information, 7-3

log file entries, 7-1

log files, 3-15

mapping data, 3-3

methods for loading data into tables, 5-25

methods of loading data, 3-16

multiple INTO TABLE statements, 5-43

National Language Support, 5-24

native datatype handling, 3-9

NULLIF...BLANKS clause

case study, 4-25

object names, 5-12

PARALLEL

parallel data loading, 8-25, 8-29

parallel loading, 8-26

PARFILE

replacing rows in tables, 5-26

READBUFFERS keyword, 5-18

rejected records, 3-13

required privileges, 3-16

reserved words, A-2

ROWS

inserting into tables, 5-26

rows

SILENT

command-line parameter, 6-7

SINGLEROW index keyword, 5-37

SKIP

command-line parameter, 6-8

SKIP keyword, 5-29

SORTED INDEXES

case study, 4-24

direct path loads, 5-36

specifying a single load method for all tables, 5-27

specifying columns, 5-39

specifying data format, 3-5

specifying data location, 3-5

specifying datatypes, 5-50

specifying field conditions, 5-37

specifying fields, 5-39

specifying more than one data file, 5-17

suppressing messages

SILENT, 6-7

terminated data, 3-10

updating rows, 5-27

USERID

command-line parameter, 6-8

SQL*Net See Net8

SQL/DS option (DB2 file format)

not supported by SQL*Loader, B-5

STATISTICS

Export parameter, 1-19

statistics

generating on imported data, 2-62

specifying for Export, 1-19

storage parameters, 2-52

estimating export requirements, 1-7

exporting tables, 1-13

OPTIMAL parameter, 2-52

overriding

Import, 2-53

preallocating

direct path load, 8-15

temporary for a direct path load, 8-10

stored functions

stored packages

stored procedures

direct path load, 8-23

stream format records, 3-8

string comparisons

SQL*Loader, 5-7, 5-39

synonyms

direct path load, 8-9

Export, 1-42

syntax

data definition language

SQL*Loader, 5-1

Export command, 1-7

Import command, 2-7

syntax diagrams

SQL*Loader

expanded, 5-7

high-level, 5-4

SYSDATE datatype

case study, 4-27

no space used in bind array

SYSDATE keyword

advanced queue (AQ) importing, 2-61

SYSDBA, 1-8, 1-29, 2-42

SYS.INCEXP table

Export, 1-43

SYS.INCFIL table

Export, 1-44

SYS.INCVID table

Export, 1-44

system objects

importing, 2-14, 2-23

system tables

incremental export, 1-43

T

table-level Export, 1-6

table-level Import, 2-30

table-mode Export

specifying, 1-19

table-mode Import

examples, 2-33

tables

advanced queue (AQ)

exporting, 1-49

appending rows to

continuing a multiple table load

continuing a single table load

defining before Import, 2-15

definitions

creating before import, 2-15

exclusive access during direct path loads

exporting

specifying, 1-19

importing, 2-27

insert triggers

direct path load

inserting rows

loading data into more than one table

maintaining consistency, 1-14

loading data into tables

SQL*Loader, 5-25

loading method

for individual tables

SQL*Loader, 5-34

manually ordering for import, 2-15

master table

name restrictions

nested

object import order, 2-4

partitioned, 1-6, 2-6, 2-30

partitioned in DB2

no Oracle equivalent, B-5

replacing rows in

size

USER_SEGMENTS view, 1-7

specifying a single load method for all tables

specifying table-mode Export, 1-19

system

incremental export, 1-43

truncating

updating existing rows

dropping during import, 2-54

TABLES parameter

Export, 1-19

Import, 2-27

tablespaces

read-only

reorganizing

tabs

loading data files and

SQL*Loader, 5-41

trimming

whitespace

not exported during backup, 1-42

temporary segments

FILE keyword

SQL*Loader, 8-27

temporary storage in a direct path load, 8-10

TERMINATED BY

SQL*Loader, 5-10, 5-61

WHITESPACE

SQL*Loader, 5-61, 5-75

with OPTIONALLY ENCLOSED BY

terminated fields

specified with a delimiter

SQL*Loader, 5-61, 5-74

TOUSER

Import parameter, 2-29

trailing

whitespace

trimming

SQL*Loader, 5-76

trailing blanks

loading with delimiters

TRAILING NULLCOLS

case study, 4-27

control file keyword

SQL*LOader, 5-36

triggers

, 8-22

database insert triggers

, 8-21

permanently disabled

, 8-24

update triggers

SQL*Loader, 8-22

trimming

summary

SQL*Loader, 5-77

trailing whitespace

SQL*Loader, 5-76

VARCHAR fields

migrating with export/import, 2-2

Trusted Oracle

Trusted Oracle7 Server

privileges for SQL*Loader, 3-16

TYPE# column

U

unique indexes

Import, 2-24

unique values

generating

uniqueness constraints

preventing errors during import, 2-21

UNLOAD (DB2 file format)

not supported by SQL*Loader, B-5

UNRECOVERABLE keyword

SQL*Loader, 5-12, 8-18

unsorted data

direct path load

updating rows in a table

SQL*Loader command-line parameter, 6-8

user definitions

importing, 2-14

USER_SEGMENTS view

Export and, 1-7

USERID

Export parameter, 1-21

Import parameter, 2-29

user-mode Export

user-mode Import

specifying, 2-29

V

VARCHAR datatype, 5-51

specification

SQL*Loader, 5-56

trimming whitespace

VARCHAR2 datatype, 2-63

SQL*Loader, 5-50

VARGRAPHIC datatype, 5-51

specification

SQL*Loader, 5-55

variable format records

vs. fixed

creating views necessary for Export, 1-7

views

Export, 1-42

W

warning messages, 1-32

WHEN clause

discards resulting from

field condition

whitespace

included in a field, or not

SQL*Loader, 5-75

leading

preserving

terminating a field with

trimming

WHITESPACE

WHITESPACE keyword

Z

ZONED datatype, 5-51

EXTERNAL format

SQL*LOader, 5-60

trimming whitespace