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).

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:

#!/bin/ksh
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:

#!/bin/ksh
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

log=ExportFull.log
full=y
userid=/
buffer=1048576

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

log=tableimport.log
fromuser=username
touser=username
table=tablename
ignore=y
indexes=n
buffer=1048576
commit=y

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

ALTER TABLE tablename DISABLE ALL TRIGGERS;

Or

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...)

log=brecovery.log
fromuser=USER1,USER2
touser=USER1,USER2
tables=T1,T2,T3,T4,T5
indexes=n
ignore=y
grants=n
commit=y
buffer=10485760

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.