A Instant Client for SQL*Loader, Export, and Import

Oracle Instant Client enables you to run your applications without installing the standard Oracle Client, or having an Oracle home. 

What is the Tools Instant Client?

The Tools Instant Client package is available on platforms that support the Oracle Call Interface (OCI) Instant Client.

The Tools package contains several command-line utilities, including SQL*Loader, Oracle Data Pump Export, Oracle Data Pump Import, Original (classic) Export, and Original (classic) Import. Instant Client installations are standalone, with all of the functionality of the command-line versions of the products. The Instant Client connects to existing remote Oracle Databases, but does not include its own database. It is easy to install, and uses significantly less disk space than the full Oracle Database Client installation required to use the command-line versions of products.

Overview of Steps Required to use Tools Instant Client

To use the Tools Instant Client, you need two packages:

  • Tools Instant Client Package

  • Either the Basic OCI Instant Client package, or the OCI Instant Client Light package.

The basic steps required to use the Tools Instant Client are as follows. Each of these steps is described in this appendix.

  1. Choose which OCI Package (Basic or Light) you want to use, and also select the directory in which to install the Instant Client files.

  2. Copy the Tools Instant Client Package, and the OCI Instant Client package of your choice, from an installed Oracle instance or download them from OTN.

  3. Install (unpack) the Tools Instant Client package and the OCI package. A new directory instantclient_12_2 is created as part of the installation.

  4. Configure the Instant Client.

  5. Connect to a remote instance with the utility you want to run.

Both the Tools package and OCI package must be from Oracle Database version 12.2.0.0.0, or higher, and the versions for both must be the same.

See Oracle Call Interface Programmer's Guide for more information about the OCI Instant Client.

Choosing Which Instant Client to Install

Before you install the Tools Instant Client Tools package, decide if you want to use Basic Instant Client, or take advantage of the smaller disk space requirements of Instant Client Light.

The Tools Instant Client package is fully supported with both of the Oracle Instant Client options. The primary difference between them is that the Instant Client Light option includes only error message files in English.

Basic Instant Client

The Tools Instant Client package, when used with Basic Instant Client works with any NLS_LANG setting supported by Oracle Database. It supports all character sets and language settings available with Oracle Database.

Instant Client Light

The Instant Client Light (English) version of Instant Client further reduces the disk space requirements of the client installation. The size of the library has been reduced by removing error message files for languages other than English and leaving only a few supported character set definitions out of around 250.

Instant Client Light is geared toward applications that use either US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, or a Unicode character set. There is no restriction on the LANGUAGE and the TERRITORY fields of the NLS_LANG setting, Instant Client Light operates with any language and territory settings. Because only English error messages are provided with Instant Client Light, error messages generated on the client side, such as Net connection errors, are always reported in English. This is true even if NLS_LANG is set to a language other than AMERICAN. Error messages generated by the database side, such as syntax errors in SQL statements, are in the selected language provided the appropriate translated message files are installed in the Oracle home of the Oracle Database instance.

Installing Instant Client Tools by Downloading from OTN

To install the Oracle Instant Client tools package, select the procedure for your platform from the Oracle Technical Network (OTN), and download the files.

The Instant Client tools package provides an easy way to add many Oracle Database utilities to your Instant Client. The tool package includes Oracle Data Pump, SQL*Loader and Workload Replay Client.

The OTN downloads for Linux are RPM packages. The OTN downloads for UNIX and Windows are zip files.

Installing Instant Client and Instant Client Tools RPM Packages for Linux

Use this procedure to download and install the Linux RPM packages for Oracle Instant Client, and Oracle Instant Client Tools.

In this deployment option, you download the Oracle Instant Client RPM and the Instant Client Tools RPM from the Oracle Technical Network.

Caution:

Set up a separate installation location for Oracle Instant Client. Never install Oracle Instant Client packages in an Oracle home.

  1. Download the Oracle Instant Client and Instant Client Tools RPM packages from the following URL:

    http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

    Both packages must be at least release 12.2.0.0.0 or higher, and both packages must be the same release.

  2. Use rpm -i for the initial install of the RPM packages, or rpm -u to upgrade to a newer version of the packages. Install Oracle Instant Client first before you attempt to install the Instant Client Tools package.

  3. Configure Instant Client.

Installing Instant Client and Instant Client Tools from Unix or Windows Zip Files

Use this procedure to download and install the zip files for Oracle Instant Client, and Oracle Instant Client Tools.

In this deployment option, you download the Oracle Instant Client RPM and the Instant Client Tools RPM from the Oracle Technical Network.

Caution:

Set up a separate installation location for Oracle Instant Client. Never install Oracle Instant Client packages in an Oracle home.

  1. Download the Oracle Instant Client and Instant Client Tools zip files from the following URL:

    http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

    Both zip files must be at least release 12.2.0.0.0 or higher, and both packages must be the same release.

  2. Create a new directory. For example, with an Oracle Instant Client 19c deployment, on Unix systems create /home/instantclient19c. On Windows, create c:\instantclient19c on Windows.

  3. Unzip the two packages into the new directory. Install the Oracle Instant Client package first.

  4. Configure Instant Client.

Installing Tools Instant Client from the Client Release Media

To install the Tools Instant Client package from the client release media, you copy files over to a local home.

  1. Run the installer on the Oracle Database Client Release media and choose the Administrator option.

  2. Create a new directory. For example, on Unix and Linux, create a directory such as /home/instantclientrelease, where release is the release number of the instant client package. For example: /home/instantclient19 On Microsoft Windows, create a path such as c:\instantclient19.

  3. Copy the Tools Instant Client package to the new directory. All files must be copied from the same Oracle home. Refer to "List of Oracle Instant Client Tools Files" for a list of the files to copy

After you copy the Instant Client files, you are ready to configure the Tools Instant Client package on your system.

List of Oracle Instant Client Tools Files

Learn about the purpose of the files that comprise the Oracle Instant Client Tools.

Oracle Instant Client Tools Files

Refer to the list of files for your platform. Note that, for convenience, the Microsoft Windows files include symbolic links (symlinks), so that you do not need to create them. When the zip file is unzipped and restored, the symlinks are also restored.

Table A-1 Instant Client Tools Files for Linux and Unix

File Name Description

exp

Original (classic) export executable

expdp

Oracle Data Pump export executable

imp

Original (classic) import executable

impdp

Oracle Data Pump import executable

libnfsodmrelease.so

A shared library used by the SQL*Loader Instant Client to use the Oracle Disk Manager (ODM). The value in the variable release corresponds to the release of the tools files contained in the zip. For example, and Oracle Database 19c tools file set has the shared library libnfsodm19.so

sqlldr

SQL*Loader executable

TOOLS_LICENSE

License document for the Tools Instant Client package.

TOOLS_README

Readme for the Tools Instant Client package

wrc

The Tools Instant Client package contains tools other than those described in this appendix. The wrc tool is the Workload Replay Client (wrc) for the Oracle Database Replay feature. The wrc tool is listed here, but it is not covered by the information in this appendix.

Table A-2 Oracle Instant Client Tools Files for Microsoft Windows

File Name Description

exp.exe

Original (classic) export executable.

exp.sym

Symbolic link file for the original (classic) export executable.

expdp.exe

Oracle Data Pump export executable.

expdp.sym

Symlink for Oracle Data Pump export executable.

imp.exe

Original (classic) import executable.

imp.sym

Symlink for Original (classic) import executable.

impdp.exe

Oracle Data Pump import executable.

impdp.sym

Symlink for Oracle Data Pump import executable.

sqlldr.exe

SQL*Loader executable.

sqlldr.exe

Symlink for SQL*Loader executable.

TOOLS_LICENSE

License document for the Tools Instant Client package.

TOOLS_README

Read Me document for the Tools Instant Client package.

wrc.exe

The Tools Instant Client package contains tools other than those described in this appendix. The wrc tool is the Workload Replay Client (wrc) for the Oracle Database Replay feature. The wrc tool is listed here, but it is not covered by the information in this appendix.

wrc.sym

Symlink for the Workload Replay Client.

Configuring Tools Instant Client Package

To configure the Tools Instant Client package executable for use with Oracle Instant Client, you must set environment variables.

With Oracle Instant Client, you do not need to set ORACLE_HOME or ORACLE_SID environment variables. However, you must set LD_LIBRARY_PATH, and you must set any globalization environment variables that you require.

Only use the Tools Instant Client package executable that is the same release as the Oracle Instant Client executable that you intend to use with the Tools package.

Example A-1 Configuring Tools Instant Client Package (from RPMS) on Linux

In this example, you move the RPMs downloaded from OTN install into the /usr file system, in release-specific subdirectories for the Tools Instant Client package By using release-specific folders in the /usr subdirectory, you can have multiple versions of Instant Client tools available for each release of Oracle Instant Client that you want to use.

  1. Add the name of the directory containing the Oracle Instant Client libraries to LD_LIBRARY_PATH. Remove any other Oracle directories.

    For example, to set LD_LIBRARY_PATH in the Bourne or Korn shells, use the following syntax:

    LD_LIBRARY_PATH=/usr/lib/oracle/19/client/lib:${LD_LIBRARY_PATH}
    export LD_LIBRARY_PATH

    Or, to set LD_LIBRARY_PATH in the C shell, use the following syntax:

    % setenv LD_LIBRARY_PATH /usr/lib/oracle/19/client/lib:$LD_LIBRARY_PATH
  2. Make sure the Tools executables installed from the RPM are the first executables found in your PATH. For example, to test if the Tools executable is found first, enter which sqlldr. If the PATH environment variable is configured correctly, then the response should be /usr/bin/sqlldr. If you do not obtain that response, then remove any other Oracle directories from PATH, or put /usr/bin before other Tools executables in PATH, or use an absolute or relative path to start Tools Instant Client.

    For example, to set PATH in the bash shell:

    PATH=/usr/bin:${PATH}
    export PATH
  3. Set Oracle globalization variables required for your locale. For example:

    NLS_LANG=AMERICAN_AMERICA.UTF8
    export NLS_LANG

    If you do not set a globalization value, then the Tools package takes the globalization values from the default locale.

Example A-2 Configuring Tools Instant Client Package (from Client Media or Zip File) on Linux and Unix

  1. Add the name of the directory containing the Instant Client files to the appropriate shared library path LD_LIBRARY_PATH, LIBPATH or SHLIB_PATH. Remove any other Oracle directories.

    For example, using Solaris the Bourne or Korn shells on Oracle Solaris, enter the following command:

    LD_LIBRARY_PATH=/home/instantclient19:${LD_LIBRARY_PATH}
    export LD_LIBRARY_PATH
  2. Add the directory containing the Instant Client files to the PATH environment variable. If it is not set, then an absolute or relative path must be used to start the utilities provided in the Tools package. Remove any other Oracle directories from PATH. For example:

    PATH=/home/instantclient19:${PATH}
    export PATH
  3. Set Oracle globalization variables required for your locale. For example:

    NLS_LANG=AMERICAN_AMERICA.UTF8
    export NLS_LANG

    If you do not set a globalization value, then the Tools package takes the globalization values from the default locale.

Example A-3 Configuring Tools Instant Client Package on Windows

You can configure your Microsoft Windows environment by using the SET commands in a Windows command prompt. You can make the environment variable permanent by setting Environment Variables in System Properties.

For example, to set environment variables in Windows Server 2019 using System Properties, open System from the Control Panel, click the Advanced System Settings link, and then click Environment Variables.

  1. Add the directory containing the Instant Client files to the PATH system environment variable. Remove any other Oracle directories from PATH.

    For example, add c:\instantclient19 to the beginning of PATH.

  2. Set Oracle globalization variables required for your locale. A default locale will be assumed if no variables are set.

    For example, to set NLS_LANG for a Japanese environment, create a user environment variable NLS_LANG set to JAPANESE_JAPAN.JA16EUC.

Connecting to a Database with the Tools Instant Client Package

After the Tools Instant Client package is installed and configured, there are multiple ways that you can connect to the database using the tools.

The utilities supplied in the Tools Instant Client are always remote from any database server. To use Oracle Instant Client, a server must have an Oracle Database instance up and running, and it must have the TNS listener running. For the Oracle Data Pump Export and Import clients, the dump files reside on the remote server; an Oracle Database directory object on the server must exist, and should have the appropriate permissions.

Example A-4 Different Ways You Can Connect to a Database Using the Instant Client Tools

To connect to a database, you must specify the database by using an Oracle Net connection identifier. The following information uses the SQL*Loader (sqlldr) utility, but the information applies to other utilities supplied in the Tools Instant Client package as well.

For example, you can use an Easy Connection identifier to connect to the HR schema in the MYDB database running on mymachine is:

sqlldr hr/your_password@\"//mymachine.mydomain:port/MYDB\"

Alternatively you can use a Net Service Name:

sqlldr hr/your_password@MYDB

Your Net Service Names can be stored in a number of places, including LDAP. To take full advantage of new release Oracle Database features, Oracle recommends that you use LDAP.

To use Net Service Names configured in a local Oracle Net tnsnames.ora file, set the environment variable TNS_ADMIN to the directory containing the tnsnames.ora file. For example, on Unix or Linux systems, if your tnsnames.ora file is in /home/user1 and it defines the Net Service Name MYDB2, you can use the following commands:

TNS_ADMIN=/home/user1
export TNS_ADMIN
sqlldr hr@MYDB2

If you do not set TNS_ADMIN as an environment variable, then an operating system dependent set of directories is examined to find tnsnames.ora. This search path includes looking in the directory specified by the ORACLE_HOME environment variable for network/admin/tnsnames.ora. Enabling the operating system to find the tnsnames.ora file is the only reason to set the ORACLE_HOME environment variable for SQL*Loader Instant Client. If ORACLE_HOME is set when running Instant Client applications, then you must set it to a directory that exists.

In the following example, we assume that the ORACLE_HOME environment variable is set, and the $ORACLE_HOME/network/admin/tnsnames.ora or ORACLE_HOME\network\admin\tnsnames.ora file defines the Net Service Name MYDB3:

sqlldr hr@MYDB3

You can set the environment variable TWO_TASK (on Unix and Linux) or LOCAL (on Microsoft Windows) to a connection identifier. By setting the environment variable this way, you can aovid the need to explicitly enter the connection identifier whenever a connection is made in SQL*Loader or SQL*Loader Instant Client. For example, suppose you want to connect to a database using a client on a Unix system. The following example connects to the database called MYDB4:

TNS_ADMIN=/home/user1
export TNS_ADMIN
TWO_TASK=MYDB4
export TWO_TASK
sqlldr hr

On Microsoft Windows, you can set both TNS_ADMIN and LOCAL in the System Properties.

Uninstalling Tools Instant Client Package and Instant Client

You can uninstall the Tools Instant Client package separately, or uninstall the entire Instant Client.

After uninstalling the Tools Instant Client package, the remaining Instant Client libraries still enable custom written OCI programs or third-party database utilities to connect to a database.

Example A-5 Uninstalling Tools Instant Client

  1. For installations on Linux from RPM packages, use rpm -e only on the Tools Instant Client package

    OR

    For installations on Unix and Windows, and installations on Linux from the Client Release media, manually remove any files specific to the Tools Instant Client. The files that you want to delete should be in the Instant Client directory that you specified at installation. Do not remove any Oracle home files.

    If necessary, reset environment variables and remove tnsnames.ora.

Example A-6 Uninstalling the Complete Instant Client

  1. For installations on Linux from RPM packages, choose one of the following options:

    • use rpm -qa to find the Tools Instant Client and Basic Oracle Instant Client package names. To remove them, run rpm -e

    • For installations on UNIX and Windows, and installations on Linux from the Client Release media, manually delete the directory containing the Tools executable and Oracle libraries.

  2. Reset environment variables, such as PATH, LD_LIBRARY_PATH and TNS_ADMIN.

  3. Remove tnsnames.ora if necessary.