2 Oracle Database Utilities

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

SQL*Loader
The Export Utility
The Import Utility
The Datapump Export Utility
The Datapump 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 Siemens BS2000/OSD.

2.1.1 Starting the SQL*Loader Utility

To start SQL*Loader, enter the following command:

/START-PROGRAM $ORAC1020.SQLLDR
* username/password

2.1.2 Using the SQL*Loader Demonstration Files

The demonstration files are shipped under:

$ORAC1020.RDBMS.DEMO.ULCASE*.CTL
$ORAC1020.RDBMS.DEMO.ULCASE*.SQL
$ORAC1020.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 $ORAC1020.SQLPLUS
* SCOTT/TIGER
SQL> START $ORAC1020.RDBMS.DEMO.ULCASE1 
```
Note:
This example sets up the table required for the userid/password SCOTT/TIGER to run the demonstrations.

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

/START-PROGRAM $ORAC1020.SQLLDR
* SCOTT/TIGER $ORAC1020.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 Siemens BS2000/OSD.

2.2.1 Starting the Export Utility

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

/START-PROGRAM $ORAC1020.EXP
* username/password

If you omit the username/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 extent 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 $ORAC1020.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. To write the file to tape directly, enter a corresponding FILE command, and specify the EXP output file as link=name, where name is the LINK name of the FILE command.

2.2.2.2 Transferring Data by File Transfer

If you use FTP, 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 Siemens BS2000/OSD.

2.3.1 Starting the Import Utility

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

/START-PROGRAM $ORAC1020.IMP 
* username/password [options]

If you omit the username/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 2Kb, 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 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 Datapump Export Utility

Datapump 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 databaseserver rather than in the client utility session.

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

2.4.1 Starting the Datapump Export Utility

To invoke the Datapump Export utility, EXPDP, enter the following command:

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

Datapump 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 invoke 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.

Datapump Export to tape is not supported.

2.5 The Datapump Import Utility

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

2.5.1 Starting the Datapump Import Utility

To invoke the Datapump Import utility, IMPDP, enter the following command:

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

When you try to use an export file from an ASCII platform as an import file on BS2000, 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 invoke 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.

Datapump 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 Siemens BS2000/OSD.

To start Oracle Text Loader enter the following command:

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

For more information, refer to Oracle Text Reference.