Listing 22.1. Export command line example and output.

Unix->exp scott/tiger file=example.exp owner=scott grants=Y rows=Y

 compress=y



Export: Release 8.0.3.0.0 - Production on Tue Feb 3 9:01:15 1998



" Copyright 1997 Oracle Corporation.  All rights reserved.



Connected to: Oracle8 Server Release 8.0.3.0.0 - Production

PL/SQL Release 8.0.3.0.0 - Production

Export done in US7ASCII character set

. exporting object type definitions for user SCOTT



About to export SCOTT’s objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SCOTT’s tables via Conventional Path ...

. . exporting table                         BONUS        0 rows exported

. . exporting table                          DEPT        4 rows exported

. . exporting table                           EMP       14 rows exported

. . exporting table                      SALGRADE        5 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting referential integrity constraints

. exporting triggers

. exporting posttables actions

. exporting snapshots

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

Export terminated successfully without warnings.

The example in Listing 22.2 imports the file exported by Export. I told it to ignore any errors due to the object’s existence.

Listing 22.2. Import command line example and output.

Unix-> imp scott/tiger  file=example.exp ignore=Y





Import: Release 8.0.3.0.0 - Production on Tue Feb 3 9:31:21 1998



" Copyright 1997 Oracle Corporation.  All rights reserved.



Connected to: Oracle8 Server Release 8.0.3.0.0 - Production

PL/SQL Release 8.0.3.0.0 - Production

Export file created by EXPORT:V08.00.03 via conventional path

. importing SCOTT’s objects

. . importing table                         BONUS        0 rows imported

. . importing table                        “DEPT”        4 rows imported

. . importing table                         “EMP”       14 rows imported

. . importing table                    “SALGRADE”        5 rows imported



Import terminated successfully without warnings.

Incremental Backups

Oracle Export and Import also provide for object level backups. Export has a method of capturing the entire database of objects and two levels below that initial level of just exporting those objects that have changed since the prior incremental backup. This method of backup is known as Export Incremental Backups and is covered in depth in Chapter 23, “Backup and Recovery.”

SQL*Loader

Oracle also comes with SQL*Loader, a table or multiple-table load facility. This tool is capable of reading a variety of formatted files from a variety of sources (disk files, tape, even named pipes), and with some logic, can populate multiple tables from one pass of the data file being loaded. SQL*Loader has a direct path load option, that is, a feature that allows SQL*Loader to ignore the Oracle kernel and directly load the data into Oracle blocks in the underlying files assigned to the tablespace.

---

Note:  
This is an unprotected write where there is no read consistency, undo ability, or archive logs of the event.

---

SQL*Loader can also save rejected records to a disk file.

The example in Listing 22.3 shows the SQL*Loader command line syntax and the associated control file that will load a single table with data from file EXAMPLE.DAT, and it contains some constant values that will populate the table as well.

Listing 22.3. SQL*Loader command line and control file example.

Unix->sqlldr USERID=hotka/dan CONTROL=EXAMPLE.ctl, LOG=EXAMPLE.log,

BAD=EXAMPLE.bad





Unix->cat EXAMPLE.ctl

    load data

    infile ‘EXAMPLE.DAT’

    append

    into table APPL_PROGRAM_TABLE

     (PROGRAM_NAME position(1:8) char,

    PROGRAM_SUFFIX constant ‘COB’,

    PROGRAM_SIZE position(15:22) integer external,

    PROGRAM_PATH constant ‘C:\COBOL’,

    ASSIGNED_ANALYST constant ‘HOTKA’)