Export / Import

Let's consider some of the export/import examples for this month's tip.

What is Export & Import (excerpts from Oracle Server Utilities Guide)?

Export provides a simple way for you to transfer data objects between Oracle database. Export extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format. Export dump file located typically on disk or tape.

Such files can then be FTPed or physically transported (in the case of tape) to a different site and used, with the Import utility, to transfer data between databases that are on machines not connected via a network or as backups in addition to normal backup procedures.

Export dump files can only be read by the Oracle utility, Import. Export files are stored in Oracle-binary format. Export files generated by Oracle8 Export cannot be read by utilities other than Oracle8 Import. Export files created by Oracle8 Export cannot be read by earlier versions of the Import utility.

exp help=y will provide this help screen (Oracle7).

Keyword  Description (Default)        Keyword      Description (Default)
USERID   username/password            FULL         export entire file (N)
BUFFER   size of data buffer          OWNER        list of owner usernames
FILE     output file (EXPDAT.DMP)     TABLES       list of table names
COMPRESS import into one extent (Y)   RECORDLENGTH length of IO record
GRANTS   export grants (Y)            INCTYPE      incremental export type
INDEXES  export indexes (Y)           RECORD       track incr. export (Y)
ROWS     export data rows (Y)         PARFILE      parameter filename
CONSTRAINTS export constraints (Y)    CONSISTENT   cross-table consistency
LOG      log file of screen output    STATISTICS   analyze objects (ESTIMATE)
DIRECT   direct path (N)
FEEDBACK display progress every x rows (0)
POINT_IN_TIME_RECOVER   Tablespace Point-in-time Recovery (N)
RECOVERY_TABLESPACES    List of tablespace names to recover
VOLSIZE  number of bytes to write to each tape volume

Similarly imp help=y will provide this help screen

Keyword  Description (Default)       Keyword      Description (Default)
USERID   username/password           FULL         import entire file (N)
BUFFER   size of data buffer         FROMUSER     list of owner usernames
FILE     input file (EXPDAT.DMP)     TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output
DESTROY  overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
CHARSET  character set of export file (NLS_LANG)
POINT_IN_TIME_RECOVER  Tablespace Point-in-time Recovery (N)
SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
ANALYZE  execute ANALYZE statements in dump file (Y)
FEEDBACK display progress every x rows(0)
VOLSIZE  number of bytes in file on each volume of a file on tape

For detailed explanation of each parameter, refer to Oracle Server Utilities Guide.

The following case studies will help you understand how export / import can be used by DBAs to have their life easier.

CASE I: Export to backup data every day for small and medium databases

It is always good to have an export backup of data taken every night along with your other backups (cold / hot backups). From export backups, it is easier to restore a table or a couple of tables to the point when the export was taken. Also export helps to check data block corruption. Unix provides a utility called 'compress' to compress the size of the export dump file. If you are short on disk space, you might consider writing the export dump file compressed using a UNIX pipe. The following scripts will show how to accomplish this.

Script to export data to a compressed file:

rm -r /tmp/exp_pipe
mknod /tmp/exp_pipe
compress < /tmp/exp_pipe > FullExport.dmp.Z & $ORACLE_HOME/bin/exp file=/tmp/exp_pipe parfile=export.par

Script to import data from a compressed export file:

rm -r /tmp/imp_pipe
mknod /tmp/imp_pipe
uncompress < FullExport.dmp.Z > /tmp/imp_pipe & sleep 3 $ORACLE_HOME/bin/imp file=/tmp/imp_pipe parfile=import.par

where import.par and export.par have the parameters to be considered for the import and export respectively. For example, if you are doing full exports, the export.par file could be


To import one table from the above export file, the import.par file could be


CASE II: Export / Import to reduce fragmentation of table

Though experts say, it does not affect the performance to have many extents for a table or index, it still is better to reorganize tables and indexes if they have too many extents. For reorganizing the index, you can use the command "ALTER INDEX ... REBUILD TABLESPACE ... STORAGE (...);" To reorganize the table, you can export the table data, drop the table, recreate the table with new storage parameters and import data. Well, this is always not very easy to drop and recreate the table, if the table has lot of child tables (foreign key relationship to this table). To drop the parent table, you need to drop all the foreign key constraints first. So you need to save the script for re-creating all constraints. Also when you drop a table, all the privileges on that table to different roles/users also lost. I prefer not to drop the table, but to truncate the table after disabling the foreign keys and triggers on the table. So you have an INITIAL extent which you cannot make it bigger, but you can alter the table to have a larger NEXT extent value and do the import. The following script will disable all foreign keys from the table.

set feedback off verify off echo off lines 200 trims on pages 0
spool /tmp/disablekeys.sql
select 'alter table ' || owner ||'.'|| table_name || ' disable constraint ' || constraint_name || ';'
from dba_constraints
where (r_owner, r_constraint_name) = (select owner, constraint_name
from dba_constraints
where owner = upper('&1')
and table_name = upper('&2')
and constraint_type = 'P')
spool off
set feedback on verify on lines 80 pages 24

To disable the triggers, do



set pages 0 feedback off echo off lines 132
spool /tmp/disabletrig.sql
select 'alter trigger ' || owner ||'.'|| trigger_name ||' disable; '
from dba_triggers
where table_owner = 'OWNER' and table_name in
('T1', 'T2', 'T3', 'T4', 'T5')

Case III: Import to recover data

If your users have messed up some data, or deleted some data accidentally, you can recover that from an export file, if you have one. To do so, you can truncate the table, disable foreign keys (child tables), disable triggers and import the data. The above scripts can be used here also. The import parameter file could be (here we import just the data, no indexes, grants...)


Case IV: Export / Import to move data between databases

You can also move data between one database to another or one machine to another using export and import. The export/import can be database level, user level or table level. To speed up exports use DIRECT=Y. To import data from an export file taken using DIRECT=Y, the database character set should be the same.

In Oracle, there is neat feature in EXP/IMP to show the progress of export/import - FEEDBACK=nn; where nn is the number of rows. A dot (.) will be displayed on the screen for each nn rows exported/imported.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.