This chapter describes how you start the following Oracle Database utilities:
For a more detailed discussion of these utilities, refer to Oracle Database Utilities.
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.
To start SQL*Loader, enter the following command:
/START-PROGRAM $ORAC1020.SQLLDR * username/password
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
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.
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 withSAM
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 ...
You can export to foreign systems using the following methods:
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.
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.
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.
This section gives you some guidelines on importing data from non-BS2000 systems.
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
.
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.
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.
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.
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 invokeEXPDP
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.
The Datapump Import utility is used to write data from the files created by the Datapump Export utility to an Oracle database.
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 invokeIMPDP
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.
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.