Oracle DBA FAQ - Loading and Exporting Data--tutorial.jcwcn.com

A collection of 27 FAQs on Oracle loading data and exporting data. Clear answers are provided with tutorial exercises on saving data as flat files, loading data from flat, exporting and importing database, schema and tables, creating external tables. Topics included in this FAQ are:

What Is the Simplest Tool to Run Commands on Oracle Servers?
What Is the Quickest Way to Export a Table to a Flat File?
How To Export Data with a Field Delimiter?
What Is SQL*Loader?
What Is a SQL*Loader Control File?
How To Load Data with SQL*Loader?
What Is an External Table?
How To Load Data through External Tables?
What Are the Restrictions on External Table Columns?
What Is a Directory Object?
How To Define an External Table with a Text File?
How To Run Queries on External Tables?
How To Load Data from External Tables to Regular Tables?
What Is the Data Pump Export Utility?
What Is the Data Pump Import Utility?
How To Invoke the Data Pump Export Utility?
How To Invoke the Data Pump Import Utitlity?
What Are Data Pump Export and Import Modes?
How To Estimate Disk Space Needed for an Export Job?
How To Do a Full Database Export?
Where Is the Export Dump File Located?
How To Export Your Own Schema?
How To Export Several Tables Together?
What Happens If the Imported Table Already Exists?
How To Import One Table Back from a Dump File?
What Are the Original Export and Import Utilities?
How To Invoke the Original Export Import Utilities?

Sample scripts used in this FAQ assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server.

Some sample scripts may require database tables created by other samples in the beginning of the collection.

What Is the Simplest Tool to Run Commands on Oracle Servers?

The simplest tool to connect to an Oracle server and run commands to manage data is SQL*Plus. It is an Oracle database client tool that works as a command-line user interface to the database server. SQL*Plus allows you:

Format, perform calculations on, store, and print from query results.
Examine table and object definitions.
Develop and run batch scripts.
Perform database administration.

What Is the Quickest Way to Export a Table to a Flat File?

The quickest way to export a table to a flat file is probably to use the SQL*Plus SPOOL command. It allows you to record SELECT query result to a text file on the operating system. The following tutorial exercise shows you how control the output format, start the spooler, and dump all record from a table to a flat text file:

>mkdir \oraclexe\test
>sqlplus /nolog

SQL> connect HR/fyicenter

SQL> SET HEADING OFF;
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 1000;
SQL> SPOOL \oraclexe\test\employees.txt;
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF;

You should get all records in employees.txt with fixed length fields.

How To Export Data with a Field Delimiter?

The previous exercise allows you to export data with fixed field lengths. If you want export data with variable field lengths and field delimiters, you can concatenate your fields with an expression in the SELECT clause as shown in the tutorial exercise bellow:

SQL> SET HEADING OFF;
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 1000;
SQL> SPOOL \oraclexe\test\fyi_links.txt;
SQL> SELECT id ||','|| url ||','|| notes ||','|| counts
  ||','|| created FROM fyi_links;
......
SQL> SPOOL OFF;

You should see all records in fyi_links.txt with ',' delimited fields as shown here:

101,fyicenter.com,Session 1,,17-MAY-06
110,centerfyi.com,Session 1,,17-MAY-06

What Is SQL*Loader?

SQL*Loader is a database tool that allows to load data from external files into database tables. SQL*Loader is available as part of the free Oracle 10g Expression Edition. It has some interesting features as:

Can load data from multiple data files into multiple tables in one load session.
Can specify character set of the data.
Can generate sophisticated error reports.
Can load data directly to Oracle datafiles, bypassing normal record inserting process.

What Is a SQL*Loader Control File?

A SQL*Loader control file a text that defines how data files should be loaded into the database. It allows you to specify:

Where is the input data file.
The format of the input date file.
The target table where the data should be loaded.
How input data fields should be mapped to target table columns.
Select criteria to select input records for loading.
Where to output errors.

How To Load Data with SQL*Loader?

Let's say you have a table defined as:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);

There is an input data file stored at \oraclexe\text\student.txt with the following records:

1,Steven,King,17-JUN-77,515.123.4567
2,Neena,Kochhar,21-SEP-79,515.123.4568
3,Lex,De Haan,13-JAN-83,515.123.4569
4,Alexander,Hunold,03-JAN-80,590.423.4567
5,Bruce,Ernst,21-MAY-81,590.423.4568
6,David,Austin,25-JUN-87,590.423.4569
7,Valli,Pataballa,05-FEB-88,590.423.4560
8,Diana,Lorentz,07-FEB-89,590.423.5567
9,Nancy,Greenberg,17-AUG-84,515.124.4569
10,Daniel,Faviet,16-AUG-84,515.124.4169

You can create a control file at \oraclexe\test\student.ctl as:

LOAD DATA
  APPEND INTO TABLE STUDENT
  FIELDS TERMINATED BY ','
  (id, first_name, last_name, birth_date, social_number)

When you are ready to load data, you can run the SQL*Loader with the "sqlldr" command:

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>sqlldr userid=hr/fyicenter, 
  control=\oraclexe\test\student.ctl, 
  data=\oraclexe\test\student.txt, 
  log=\oraclexe\test\student.log

SQL*Loader: Release 10.2.0.1.0 -
Commit point reached - logical record count 10

To see details of the loading process, you should check the log file \oraclexe\test\student.log.

What Is an External Table?

An external table is a table defined in the database with data stored outside the database. Data of an external table is stored in files on the operating systems. Accessing data of external tables are done through data access drivers. Currently, Oracle supports two data access drivers: ORACLE_LOADER and ORACLE_DATAPUMP.

External tables can be used to load data from external files into database, or unload data from database to external files.

How To Load Data through External Tables?

If you have data stored in external files, you can load it to database through an external table by the steps below:

Create an external table with columns matching data fields in the external file.
Create a regular table with the same columns.
Run an INSERT INTO ... SELECT statement to load data from the external file to the regular table through the external table.

What Are the Restrictions on External Table Columns?

When creating external table columns, you need to watch out some restrictions:

"PRIMARY KEY" is not allowed.
"NOT NULL" is not allowed.
"DEFAULT value" is not allowed.

What Is a Directory Object?

A directory object is a logical alias for a physical directory path name on the operating system. Directory objects can be created, dropped, and granted access permissions to different users. The following tutorial exercise shows you some good examples:

>sqlplus /nolog

SQL> connect SYSTEM/fyicenter

SQL> CREATE DIRECTORY test_dir AS '/oraclexe/test';
Directory created.

SQL> GRANT READ ON DIRECTORY test_dir TO hr;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY test_dir TO hr;
Grant succeeded.

SQL> CREATE DIRECTORY temp_dir AS '/oraclexe/temp';
Directory created.

SQL> DROP DIRECTORY temp_dir;
Directory dropped.
How To Define an External Table with a Text File?
You can use the CREATE TABLE statement to create external tables. But you need to use ORGANIZATION EXTERNAL clause to specify the external file location and the data access driver. The tutorial exercise below shows you how to define an external table as a text file:
>sqlplus /nolog

SQL> connect HR/fyicenter

SQL> CREATE TABLE ext_fyi_links (
    id NUMBER(4),
    url VARCHAR2(16),
    notes VARCHAR2(16),
    counts NUMBER(4),
    created DATE
  ) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY test_dir
    LOCATION ('ext_fyi_links.txt')
  );
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
  FROM USER_TABLES;
TABLE_NAME            TABLESPACE_NAME          NUM_ROWS
--------------------- ---------------------- ----------
REGIONS               USERS                           4
LOCATIONS             USERS                          23
DEPARTMENTS           USERS                          27
JOBS                  USERS                          19
EMPLOYEES             USERS                         107
JOB_HISTORY           USERS                          10
FYI_LINKS             USERS                           2
EXT_FYI_LINKS
COUNTRIES                                            25
How To Run Queries on External Tables?
If you have an external table defined as a text file with the ORACLE_LOADER driver, you can add data to the text file, and query the text file through the external table. By default, data fields in the text file should be terminated by ','. The tutorial exercise below shows you how add data to the external table defined in the previous exercise:
>edit /oraclexe/test/ext_fyi_links.txt
1101,dba.fyicenter,Link #1,88,07-MAY-06
1110,dev.fyicenter,Link #2,88,07-MAY-06

>sqlplus /nolog

SQL> connect HR/fyicenter

SQL> SELECT * FROM ext_fyi_links;
       ID URL              NOTES         COUNTS CREATED
--------- ---------------- ----------- -------- ---------
     1101 dba.fyicenter    Link #1           88 07-MAY-06
     1110 dev.fyicenter    Link #2           88 07-MAY-06
How To Load Data from External Tables to Regular Tables?
Once you have your data entered in a text file, and an external table defined to this text file, you can easily load data from this text file to a regular table. The following tutorial exercise shows you how to load data from the text file and the external table defined in the previous exercises to a regular table:
SQL> CREATE TABLE fyi_links (
    id NUMBER(4) PRIMARY KEY,
    url VARCHAR2(16) NOT NULL,
    notes VARCHAR2(16),
    counts NUMBER(4),
    created DATE DEFAULT (sysdate)
  );

SQL> INSERT INTO fyi_links SELECT * FROM ext_fyi_links;
2 rows created.

SQL> SELECT * FROM fyi_links;
       ID URL              NOTES         COUNTS CREATED
--------- ---------------- ----------- -------- ---------
      101 fyicenter.com    Session 1            07-MAY-06
      110 centerfyi.com    Session 1            07-MAY-06
     1101 dba.fyicenter    Link #1           88 07-MAY-06
     1110 dev.fyicenter    Link #2           88 07-MAY-06
What Is the Data Pump Export Utility?
Oracle Data Pump Export utility is a standalone programs that allows you to export data objects from Oracle database to operating system files called dump file set, which can be imported back to Oracle database only by Oracle Data Pump Import utility.
The dump file set can be imported on the same system or it can be moved to another system and loaded there.
The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set. Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects.
What Is the Data Pump Import Utility?
Oracle Data Pump Import utility is a standalone programs that allows you to import data objects from an Oracle dump file set into Oracle database. Oracle dump file set is written in a proprietary binary format by the Data Pump Export utility.
Import can also be used to load a target database directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time. This is known as a network import. Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands.
How To Invoke the Data Pump Export Utility?
The Data Pump Export utility is distributed as executable file called "expdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "expdp" command. Here is tutorial exercise on how to invoke the export utility:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>expdp help=y
Export: Release 10.2.0.1.0 - 

The Data Pump export utility provides a mechanism for
transferring data objects between Oracle databases. The 
utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir
     DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp'
command followed by various parameters:

   Format:  expdp KEYWORD=value or
     KEYWORD=(value1,value2,...,valueN)

   Example: expdp scott/tiger DUMPFILE=scott.dmp
     DIRECTORY=dmpdir 
               SCHEMAS=scott or TABLES=(T1:P1,T1:P2)

USERID must be the first parameter on the command line.

Keyword       Description (Default)
------------------------------------------------------------
ATTACH        Attach to existing job, e.g. ATTACH [=job name
COMPRESSION   Reduce size of dumpfile contents where valid
              keyword values are: (METADATA_ONLY) and NONE.
DIRECTORY     Directory object to be used for dumpfiles 
DUMPFILE      List of destination dump files (expdat.dmp).
FLASHBACK_SCN SCN used to set session snapshot back to.
FULL          Export entire database (N).
HELP          Display Help messages (N).
......
How To Invoke the Data Pump Import Utitlity?
The Data Pump Import utility is distributed as executable file called "impdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "impdp" command. Here is tutorial exercise on how to invoke the import utility:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>impdp help=y
Import: Release 10.2.0.1.0 - 

The Data Pump Import utility provides a mechanism for 
transferring data objects between Oracle databases. The 
utility is invoked with the following command:

   Example: impdp scott/tiger DIRECTORY=dmpdir 
     DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' 
command followed by various parameters.

   Format:  impdp KEYWORD=value or
     KEYWORD=(value1,value2,...,valueN)

   Example: impdp scott/tiger DIRECTORY=dmpdir 
     DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

Keyword    Description (Default)
----------------------------------------------------------
ATTACH     Attach to existing job, e.g. ATTACH [=job name]
DIRECTORY  Directory object to be used for dump, log, ... 
DUMPFILE   List of dumpfiles to import from (expdat.dmp)
FULL       Import everything from source (Y).
HELP       Display help messages (N).
......
What Are Data Pump Export and Import Modes?
Data pump export and import modes are used to determine the type and portions of database to be exported and imported. Oracle 10g supports 5 export and import modes: 
Full: Exports and imports a full database. Use the FULL parameter to specify this mode. 
Schema: Enables you to export and import all objects that belong to a schema. Use the SCHEMAS parameter to specify this mode. This is the default mode. 
Table: Enables you to export and import specific tables and partitions. Use the TABLES parameter to specify this mode. 
Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TABLESPACES parameter to specify this mode. 
Tablespace: Enables a privileged user to move metadata from the tables within a specified set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACES parameter to specify this mode. 
How To Estimate Disk Space Needed for an Export Job?
If you just want to know how much disk space for the dump without actually exporting any data, you can use the ESTIMATE_ONLY=y parameter on the expdp command. The following tutorial exercise shows you how a system user wants to see the disk space estimates on a full database export: 
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>expdp SYSTEM/fyicenter FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  SYSTEM/**** FULL=y
  ESTIMATE_ONLY=y

Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
.  estimated "FLOWS_020100"."WWV_FLOW_PAGE_PLUGS"      42 MB
.  estimated "FLOWS_020100"."WWV_FLOW_STEP_ITEMS"      27 MB
.  estimated "FLOWS_020100"."WWV_FLOW_STEP_PROCESSING" 16 MB
......
.  estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"         0 KB
.  estimated "TSMSYS"."SRS$"                            0 KB
Total estimation using BLOCKS method: 169.8 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed
Now you know that you need 170 MB disk space to export the entire data base.
Oracle also records the screen output in a log file called export.log at \oraclexe\app\oracle\admin\XE\dpdump.
How To Do a Full Database Export?
If you are ready to do a full database export, you can use the FULL=y parameter on the expdp command, as shown in the following tutorial exercise: 
>expdp SYSTEM/fyicenter FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  SYSTEM/**** FULL=y 
Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 169.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
......
. . exported FLOWS_020100.WWV_FLOW_PAGE_PLUGS      32.51MB
. . exported FLOWS_020100.WWV_FLOW_STEP_ITEMS      21.68MB
. . exported FLOWS_020100.WWV_FLOW_STEP_PROCESSING 11.17MB
......
Master table "SYSTEM"."SYS_EXPORT_FULL_01" unloaded
**********************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\EXPDAT.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed
Where Is the Export Dump File Located?
If you are not specifying the dump directory and file name, the dump file will be stored in the default dump directory with the default file name. The tutorial exercise below tells you find what is your default dump directory and locate the dump file.
>sqlplus /nolog

SQL> connect SYSTEM/fyicenter

SQL> COL owner FORMAT A8;
SQL> COL directory_name FORMAT A16;
SQL> COL directory_path FORMAT A40;
SQL> SELECT * FROM dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- -------------- -------------------------------------
SYS   DATA_PUMP_DIR  \oraclexe\app\oracle\admin\XE\dpdump\
SYS   TEST_DIR       /oraclexe/test
SYS   ORACLECLRDIR   \oraclexe\app\oracle\product\10.2.0\
                     server\bin\clr
Obviously, the default dump directory is directory object defined to \oraclexe\app\oracle\admin\XE\dpdump\. If you go to that directory, you will find the full database dump file is called "expdat.dmp".
How To Export Your Own Schema?
If you have a non-system user account and you want to export all data objects in the schema associated with your account, you can use the "expdp" command with the SCHEMAS parameter. Running "expdp" command with a non-system user account requires a directory object granted to this user account. The following tutorial exercise shows you how to define a directory object and export a schema:
>mkdir \oraclexe\hr_dump

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>sqlplus /nolog
SQL> connect SYSTEM/fyicenter

SQL> CREATE DIRECTORY hr_dump AS '\oraclexe\hr_dump';
Directory created.

SQL> GRANT READ ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> quit

>expdp hr/fyicenter SCHEMAS=hr 
  DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=schema.log

Starting "HR"."SYS_EXPORT_SCHEMA_01":  
  hr/******** SCHEMAS=hr DIRECTORY=hr_dump
  DUMPFILE=schema.dmp 
  LOGFILE=schema.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCH
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
......
. . exported "HR"."COUNTRIES"          6.085 KB   25 rows
. . exported "HR"."DEPARTMENTS"        6.632 KB   27 rows
. . exported "HR"."EMPLOYEES"          15.76 KB  107 rows
. . exported "HR"."EMPLOYEES_TEMP"     15.86 KB  107 rows
......
Master table "HR"."SYS_EXPORT_SCHEMA_01" loaded/unloaded
*********************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLEXE\HR_DUMP\SCHEMA.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed
How To Export Several Tables Together?
If you don't want to export the entire schema and only want to export several tables only, you can use the "expdp" command with the "TABLES" parameter as shown in the following tutorial exercise: 
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>expdp hr/fyicenter TABLES=employees,departments 
  DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log

Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** 
  TABLES=employees,departments DIRECTORY=hr_dump
  DUMPFILE=tables.dmp 
  LOGFILE=tables.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTI...
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF...
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TAB...
. . exported "HR"."DEPARTMENTS"       6.632 KB      27 rows
. . exported "HR"."EMPLOYEES"         15.76 KB     107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" loaded/unloaded
***********************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  C:\ORACLEXE\HR_DUMP\TABLES.DMP
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed
What Happens If the Imported Table Already Exists?
If the import process tries to import a table that already exists, the Data Pump Import utility will return an error and skip this table. The following exercise shows you a good example: 
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>impdp hr/fyicenter TABLES=employees 
  DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log

Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01":  hr/** TABLES=employees
  DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent
  metadata and data will be skipped due to table_exists_action
  of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
......
How To Import One Table Back from a Dump File?
If you only want to import one table back to the database, you can use a dump file that was created by full export, schema export or a table export. The following tutorial exercise shows you how to import the "fyi_links" table from a dump file created by a schema export: 
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>sqlplus /nolog
SQL> connect HR/fyicenter

SQL> DROP TABLE fyi_links;
Table dropped.

SQL> exit;

>impdp hr/fyicenter TABLES=fyi_links DIRECTORY=hr_dump
  DUMPFILE=schema.dmp LOGFILE=tables.log

Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01":  hr/** TABLES=fyi_links
  DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=tables.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."FYI_LINKS"             6.375 KB    4 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTI...
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TAB...
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed.
What Are the Original Export and Import Utilities?
Oracle original Export and Import utilities are standalone programs that provide you a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file.
An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.
Export and Import utilities are now being replaced by Data Pump Export and Import utilities in Oracle 10g. But you can still use them.
How To Invoke the Original Export Import Utilities?
If you really want to run the original export import utilities, you can still go to "bin" directory of the Oracle server path and run the "exp" or "imp" command. The tutorial exercise below tells you how to run the export and import utilities in help modes:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>exp help=y
You can let Export prompt you for parameters by entering the
EXP command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP
command followed by various arguments. To specify parameters, 
you use:

     Format:  EXP KEYWORD=value or
       KEYWORD=(value1,value2,...,valueN)

     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned
               table
......

>imp help=y
......


 discuss this topic to forum
[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

Oracle DBA FAQ - Loading and Exporting Data

relation tutorial

Category

New

Hot