2 Oracle Database Utilities

This chapter describes how you start the following Oracle Database utilities:

SQL*Loader
The Export Utility
The Import Utility
The Data Pump Export Utility
The Data Pump Import Utility
Oracle Text Loader

For a more detailed discussion of these utilities, refer to Oracle Database Utilities.

2.1 SQL*Loader

SQL*Loader is a tool used for moving data from an external file (or files) into the tables of an Oracle database. SQL*Loader can load data in several formats and can even load several tables simultaneously. You can also use it to load only records that match a particular data value. Refer to the Oracle Database Utilities manual for a detailed description of SQL*Loader and its demonstration files.

For restrictions when using SQL*Loader refer to the section, " Known Problems, Restrictions, and Workarounds" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.

2.1.1 Starting the SQL*Loader Utility

To start SQL*Loader, enter the following command:

/START-PROGRAM $ORAC1120.SQLLDR
* SCOTT/password

2.1.2 Using the SQL*Loader Demonstration Files

The demonstration files are shipped under:

$ORAC1120.RDBMS.DEMO.ULCASE*.CTL
$ORAC1120.RDBMS.DEMO.ULCASE*.SQL
$ORAC1120.RDBMS.DEMO.ULCASE*.DAT

To run the ULCASE1 demo use the following steps:

Run SQL*Plus and set up the table to be used in the demonstration by entering the following commands:
```
START-PROGRAM $ORAC1120.SQLPLUS
* SCOTT/password
SQL> START $ORAC1120.RDBMS.DEMO.ULCASE1 
```
Note:
This example sets up the table for the user SCOTT to run the demonstrations.

Start SQL*Loader to run the demonstration by entering the following command:

/START-PROGRAM $ORAC1120.SQLLDR
* SCOTT/password $ORAC1120.RDBMS.DEMO.ULCASE1 ULCASE1 ULCASE1

2.2 The Export Utility

The Export utility is used to write data from an Oracle Database into the BS2000 system files. Use this utility with the Import utility to back up your data, and to move data between Oracle Databases.

For restrictions when using the Export utilities refer to the section, " Known Problems, Restrictions, and Workarounds" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.

2.2.1 Starting the Export Utility

To start the Export utility, EXP, enter the following command:

/START-PROGRAM $ORAC1120.EXP
* SCOTT/password

If you omit the SCOTT/password parameters, then you are prompted for them.

Export dump files are usually created by EXP as SAM files. You can override default output file specifications by running a file command such as:

/FILE expfile,LINK=explink,FCBTYPE=SAM,RECFORM=F, -
BLKSIZE=(STD,1),RECSIZE=2048

Then, call EXP specifying the following in response to the output file name prompt:

LINK=explink

On a nonkey public volume set you may need to adjust the BLKSIZE and RECSIZE values for efficient disk-space usage (note that RECSIZE must be 16 bytes less than the BLKSIZE on nonkey disks). Specify the RECSIZE value to match the export record size.

For example:

/FILE expfile,LINK=explink,FCBTYPE=SAM,RECFORM=F, -
BLKSIZE=(STD,1),RECSIZE=2032

Note:

Do not use variable record size with SAM files.

When using a block size (PAM) or record size (SAM) other than 2048, you must also specify a corresponding RECORDLENGTH parameter to EXP on the options line.

When exporting a large volume of data, the default disk-space allocation for the output file will be inappropriate, and the program will spend a significant amount of time allocating secondary extents of disk space. If the maximum number of extents exceeds the number that the catalog entry can hold, then an output-file error will occur.

As a counter-measure, you should always preallocate the EXP output file with the BS2000 /FILE command, prior to starting the Export utility. When allocating the file, you should use a realistic estimate for both the primary and secondary space allocations.

For example:

/FILE LARGE.EXPORT.DMP,LINK=EXPOUT,SPACE=(3000,3000)
/START-PROGRAM $ORAC1120.EXP
* system/manager
...
Export file: EXPDAT.DMP >link=expout
...

2.2.2 Exporting to Foreign Systems

You can export to foreign systems using the following methods:

Transferring Data by Tape
Transferring Data by File Transfer

2.2.2.1 Transferring Data by Tape

To export directly to tape, enter a FILE command as follows:

/FILE tapefile,LINK=tapelink,FCBTYPE=SAM,RECFORM=F, -
BLKSIZE=2048,RECSIZE=2048,DEV=<device>,VOL=<vsn>

Then, call EXP, specifying the following value in response to the output file name prompt:

LINK=tapelink

You also must set the EXP_CLIB_FILE_IO environment variable to FALSE in the ORAENV file.

The export utility writes the output as SAM files, which simplifies export to an Oracle Database on foreign systems.

2.2.2.2 Transferring Data by File Transfer

If you use FTP, then ensure that you specify binary mode (to avoid automatic EBCDIC-ASCII conversion).

2.3 The Import Utility

The Import utility is used to write data from the files created by the Export utility to an Oracle Database.

For restrictions when using the Import utility refer to the section, "Known Problems, Restrictions and Workarounds" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.

2.3.1 Starting the Import Utility

To start the Import utility, IMP, enter the following command:

/START-PROGRAM $ORAC1120.IMP 
* SCOTT/password [options]

If you omit the SCOTT/password parameters, then you are prompted for them.

2.3.2 Importing from Foreign Systems

This section gives you some guidelines on importing data from non-BS2000 systems.

2.3.2.1 Import File Block Size

If the import file on the BS2000/OSD operating system has a block size (BLKSIZE) not equal to 2 KB, then you must specify the block size during import with the Import parameter RECORDLENGTH.

2.3.2.2 Transferring Data by Tape

The Import utility can read directly from tape, provided the file can be processed as a SAM file, which is usually the case even for EXP files created on foreign systems (for example, as a sequence of fixed 2 KB blocks).

To read a foreign export file directly, enter a FILE command such as the following:

/FILE tapefile,LINK=tapelink,DEV=T-C1,STATE=FOREIGN

Then, call IMP, specifying the following as the input file name at the input file name prompt:

LINK=tapelink

You must also set the IMP_CLIB_FILE_IO environment variable to FALSE in the ORAENV file.

2.3.2.3 Transferring Data by File Transfer

If you use FTP, then ensure that you specify binary mode (to avoid automatic ASCII-EBCDIC conversion). The received file will be stored as a PAM file by the BS2000 FTP utility and can immediately be used as an input file to IMP.

When you try to use an export file from BS2000 as an import file on an ASCII platform use as transfer utility FTP on BS2000 side and indicate the parameters binary and ftyp binary in order to avoid insertion of NL (new lines) at block boundaries.

2.4 The Data Pump Export Utility

Data Pump Export and Import are functionally similar to Export and Import discussed previously, but all of the I/O processing for dump files is done in the Oracle Database server rather than in the client utility session.

The Data Pump Export utility is used to write data from an Oracle Database into the BS2000 system files. Use this utility with the Data Pump Import utility to backup your data, and to move data between Oracle databases.

2.4.1 Starting the Data Pump Export Utility

To start the Data Pump Export utility, EXPDP, enter the following command:

/START-PROGRAM $ORAC1120.EXPDP
* username/password [options]

Data Pump Export dump files are created by EXPDP as PAM files with BLKSIZE=(STD,2).

When you try to use an export file from BS2000 as an import file on an ASCII platform, use as transfer utility FTP on BS2000 side and indicate the parameter binary.

Note:

If you start EXPDP in UNIX-Style and use interactive-command mode [K2] key, then the parameters must be specified when you are prompted for them and not on the command line.

Data Pump Export to tape is not supported.

2.5 The Data Pump Import Utility

The Data Pump Import utility is used to write data from the files created by the Data Pump Export utility to an Oracle database.

2.5.1 Starting the Data Pump Import Utility

To start the Data Pump Import utility, IMPDP, enter the following command:

/START-PROGRAM $ORAC1120.IMPDP
* username/password [options]

If you use an export file from an ASCII platform as an import file on BS2000, then use as transfer utility FTP on BS2000 side and indicate the parameter binary.

Before you get the file, issue the FTP command:

file dmp-file,fcbtype=pam,blksize=(std,2),blkctrl=no

Note:

If you start IMPDP in UNIX-Style and use interactive-command mode [K2] key, then the parameters must be specified when you are prompted for them and not on the command line.

Data Pump Import from tape is not supported.

2.6 Oracle Text Loader

This utility imports and exports text data. For installation of Oracle Text, refer to the chapter, "Oracle Text" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.

To start Oracle Text Loader enter the following command:

/START-PROGRAM $ORAC1120.CTXLDR
*-USER username/password [options]

For more information, refer to Oracle Text Reference.