2
Oracle Server Utilities

This chapter describes how you invoke the following Oracle Server utilities:

• SQL*Loader

• Export utility

• Import utility

• Text Loader utility

interMedia Text Loader

For a more detailed discussion of these utilities, refer to the
Oracle9i Utilities User's Guide.

SQL*Loader

SQL*Loader is a tool for moving data from an external file (or files) into the tables of an Oracle Server 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. See the Oracle9i Utilities manual for a detailed description of SQL*Loader and its demonstration files.

For restrictions refer to Installation and Database Administration Guide (see section Known Problems, Restrictions and Workarounds).

Invoking the SQL*Loader Utility

To invoke SQL*Loader, enter the following:

/START-PROGRAM $ORACL920.SQLLDR

* username/password 

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

Using the SQL*Loader Demonstration Files

The demonstration files are shipped under:

$ORACL920.RDBMS.DEMO.ULCASE*.CTL

$ORACL920.RDBMS.DEMO.ULCASE*.SQL

$ORACL920.RDBMS.DEMO.ULCASE*.DAT

The following steps show you how to run the ULCASE1 demo. Note that the example below sets up the table required for the userid/password SCOTT/TIGER to run the demonstrations.

1. Run SQL*Plus and set up the table to be used in the demonstration by entering the following commands:

   	START-PROGRAM $ORACL920.SQLPLUS
   
   	* SCOTT/TIGER
   
   	SQL> START $ORACL920.RDBMS.DEMO.ULCASE1 
   

2. Invoke SQL*Loader to run the demonstration by entering the following commands:

   	/START-PROGRAM $ORACL920.SQLLDR
   
   	* SCOTT/TIGER $ORACL920.RDBMS.DEMO.ULCASE1 ULCASE1 ULCASE1 
   

The Export Utility

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

For restrictions refer to Installation and Database Administration Guide (Known Problems, Restrictions, Workarounds)

Invoking the Export Utility

To invoke the Export utility, EXP, enter the following:

/START-PROGRAM $ORACL920.EXP

* username/password 

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

Export dump files are normally created by EXP as ISAM files. You can override default output file specifications by issuing 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 filename prompt:

LINK=explink
 

On a non-key public volume set you may need to adjust the BLKSIZE and RECSIZE values for efficient disk-space usage (note that the RECSIZE must be 16 bytes less than the BLKSIZE on non-key disks). Specify the RECSIZE value as the same as 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.














Note:



When using a blocksize (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, an output-file error will occur.

As a counter-measure you should always pre-allocate the EXP output file with the BS2000 /FILE command, prior to invoking 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 $ORACL920.EXP

* system/manager

...

Export file: EXPDAT.DMP >link=expout

... 

Exporting to Foreign Systems

Transferring Data by Tape

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

/FILE tapefile,LINK=tapelink,FCBTYPE=SAM,RECFORM=F, -

BLKSIZE=2048,RECSIZE=2048,DEV=T-C1 

and then call EXP, specifying the following in response to the output filename prompt:

LINK=tapelink 

You also need to set the EXP_CLIB_FILE_IO environment variable to false in your ORAENV file.

Export writes its output as SAM files, which simplifies export to an Oracle Server on foreign systems. To write the file to tape directly, issue a corresponding FILE command, and specify the EXP output file as link=name, where name is the LINK name of the FILE command.

Transferring Data by File Transfer

When using file transfer, ensure that you specify binary file transfer (to avoid any EBCDIC-ASCII conversion). When using FT-SIE, you may have to convert the export file to SAM first, using PERCON.

If you use TCP/IP FTP, ensure that you specify binary (or image) mode (to avoid automatic EBCDIC-ASCII conversion).

The Import Utility

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

For restrictions refer to Installation and Database Administration Guide (Known Problems, Restrictions, Workarounds)

Invoking the Import Utility

To invoke the Import utility, IMP, enter the following:

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

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

Importing from Foreign Systems

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

Import File Blocksize

If the import file on the BS2000/OSD operating system has a blocksize (BLKSIZE) not equal to 2Kb, you need to specify the blocksize during Import with the Import parameter RECORDLENGTH.

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 2Kb blocks).

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

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

and then call IMP, specifying the following as the input filename at the input filename prompt:

LINK=tapelink
 

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

Transferring Data by File Transfer

When using file transfer, ensure that you specify binary file transfer (to avoid any ASCII-EBCDIC conversion). When the transferred file becomes a SAM file (for example, when you use the FT-SIE utilities), you must convert it to a PAM file using the BS2000 PERCON utility before it can be used as IMP input file.

If you use TCP/IP FTP, ensure that you specify binary (or image) mode (to avoid automatic ASCII-EBCDIC conversion). The received file will be stored as a UPAM 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 ftype binary in order to avoid insertion of NL (new lines) at block boundaries.

Oracle Text Loader

This Utility imports and exports text data. For installation of Oracle Text see chapter about Oracle Text in the Installation and Database Administration Guide.

To invoke Oracle Text Loader enter the following:

/START-PROGRAM $ORACL920.CTXLDR
*-USER scott/tiger -NAME ...

For more information see Oracle9i Oracle Text Reference.