Oracle 9i Utilities

This tutorial explains about Export Utility, Import Utility and SQL* Loader. They are supplied as part of the Oracle Software and need not have to be purchased or downloaded separately.

Oracle Utilities

The 3 utilities supplied along with Oracle Server are

1. Export
2. Import 
3.  SQL* Loader

They are supplied as part of the Oracle Software and need not have to be purchased or downloaded separately. They are available as .exe files in the BIN directory. And can be executed by typing their name before command prompt. In the following sections I will be discussing syntaxes and the usage of these utilities.

Export Utility

This utility can be used to transfer data objects between oracle databases. The objects and the data in Oracle database can be moved to other Oracle database running even on a different hardware and software configurations.

The export utility copies database definitions and actual data into an operating system file (export file). The export file is an Oracle binary-format dump file (with .dmp extension), which is normally created on disk or tape. Before exporting we must ensure that there is enough space available on the disk or tape used.

Exported dump files can be read only by using the Import utility of Oracle. We cannot use earlier versions of import utility for importing the data exported using current version (Versions of Oracle utilities also change along with the Oracle Versions)

EXP command can be used to invoke export utility interactively without any parameters. (Requests the user to enter the value). Otherwise parameters can be specified in a file called parameter file. We can use more than one parameter file at a time with exp command.

Syntax:
exp PARFILE = filename

Parameter file is a simple text file creating using any text editor.

The exports are 3 types; Full, Owner, and Table. Full export exports all the objects, structures and data within the database for all schemas. Owner export exports only the objects owned by specific user account. Table export exports only tables owned by a specific user account.

To export a table we can run EXP utility either interactively or by putting all the parameters for the export on the command line. In interactive mode just type EXP before the command prompt and answer the questions when prompted, otherwise the parameters can be typed on the command line as shown below.

EXP scott/tiger file=emp.dmp tables=(EMP) log= error.log

In the above example SCOTT/TIGER is the username and password respectively.

emp.dat is the file into which exporting is done. This file is created in the current folder, to create it in a different folder we need to mention the complete path. Ex. C:sampledept.dmp.

Tables parameter takes table names as it value, o export more than one table their names need to be separated by a comma. Example tables = (EMP, DEPT, SALGRADE) to export tables EMP, DEPT and SALGRADE.

Log parameter is optional; we give a file name as its value. This file is used to write errors if any occur while exporting a table.