This chapter describes how to start the following Oracle Database utilities:
For a more information on these utilities, see Oracle Database Utilities guide.
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. See the Oracle Database Utilities guide for a detailed description of SQL*Loader and its demonstration files.
For restrictions when using SQL*Loader, see the section " Known Problems, Restrictions, and Workarounds" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.
To start SQL*Loader, enter the following command:
/START-PROGRAM $ORAC1120.SQLLDR
* SCOTT/password
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, perform 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 userSCOTT 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
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, see section, " Known Problems, Restrictions, and Workarounds" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.
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 to enter the values.
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 large volumes 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 occurs.
You should always preallocate the EXP output file with the BS2000 /FILE command, before 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 ...
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.
If you use FTP, then ensure that you specify binary mode (to avoid automatic EBCDIC-ASCII conversion).
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, see section, "Known Problems, Restrictions and Workarounds" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.
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 to enter the values.
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 2 KB, 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 file directly, enter a FILE command as following:
/FILE tapefile,LINK=tapelink,DEV=T-C1,STATE=FOREIGN
Then, call IMP and specify 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 to avoid insertion of NL (new lines) at block boundaries.
The 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.
This 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.
To start the Data Pump Export utility, EXPDP, enter the following command:
/START-PROGRAM $ORAC1120.EXPDP * username/password [options]
The Data Pump Export dump files are created by EXPDP as PAM files with BLKSIZE=(STD,2).
To use an export file from BS2000 as an import file on an ASCII platform, then use as transfer utility FTP on BS2000 side and indicate the parameter binary.
Note:
If you startEXPDP 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.
The Data Pump Import utility is used to write data from the files created by the Data Pump Export utility to an Oracle database.
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 startIMPDP 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 by tape is not supported.
This utility imports and exports text data. For installation of Oracle Text, see 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, see Oracle Text Reference.