Import and Export

Import and Export

Import (IMP) and Export (EXP) are among the oldest surviving Oracle tools. They are command line tools used to extract tables, schemas, or entire database definitions from one Oracle instance, to be imported into another instance or schema.

Traditionally, Import and Export have been considered to be in the domain of the DBA. But it is more useful to the individual developer than as a DBA tool. As databases have grown, both in size and importance, the tools needed to manage them have grown as well. With ‘small’ databases today starting in the gigabytes range, simple tools that process everything serially (such as import and export), just do not scale. While not useless to the DBA, they are definitely not as useful as they once were. Other tools and methods have come along to take their place. For example, we would now use Recovery Manager (RMAN) to perform an incremental backup of very large databases, not EXP. They still have many other uses such to detect logical and physical corruption, to transport datafiles from database to data, and so on.

EXP and IMP are tools you will probably use at some point or another. If you wish to copy a schema from one user to another, the easiest method will be to use EXP and IMP. If you wish to extract the DDL for a schema, EXP and IMP will be the tools you use. If you wish to copy a schema from one user to another, the easiest method will be to use EXP and IMP. If you wish to extract the DDL for a schema, EXP and IMP will be the tools you use. If you wish to partition an existing table into many physical partitions ( which requires a rebuild of the table), EXP and IMP might be a valid approach for you.

Why You might Use IMP and EXP

EXP has many uses, some are briefly mentioned above. Here some of the situations where EXP is find useful.

Detecting Corruption

EXP can be used as a tool to proactively detect corruption, physical or logical, in a database. If you use EXP to perform a full database export, it will completely exercise the data dictionary, finding almost any logical dictionary corruption for us. Additionally, it will full scan every table in your database, reading out all the rows. If there is a table with a bad block, this is bound to find it.

Extracting DDL

EXP is an excellent tool for extracting DDL from the database, providing a very easy way to see the verbose CREATE statement for many objects.

There are two ways to get the DDL: SHOW = Y and INDEXFILE = filename. It is recommended that you use INDEXFILE option and never the SHOW = Y option. The latter is designed to show us what EXP would do if it was actually executed.

Using the INDEXFILE option, we can reconstruct most of the DDL for a schema into a script file. For example suppose we started with:

SQL> create table rw2 (col1 int references rw1, col2 int check (col2>0));

Table created.

SQL> create index rw2_idx on rw2(col2,col1);

Index created.

SQL> create trigger rw2_trigger before insert or update of col1, col2 on
rw2 for each row

2begin

3if ( :new.col1 < :new.col2 ) then

4raise_application_error(-20001, 'Invalid Operation Col1 cannot be
less then col2');

5end if;

6end;

7/

Trigger created.

SQL> create view v

2as select rw1.y rw1_y, rw2.col2 rw2_col2 from rw1, rw2

3where rw1.x = rw2.col1;

View created..

We can now run EXP and IMP as follows:

C:\>exp userid=robert/robert owner=Robert

C:\>imp userid=robert/robert full=y indexfile=robert.sql

Inspecting robert.sql shows:

REM CREATE TABLE "ROBERT"."RW1" ("X" NUMBER(*,0), "Y" NUMBER(*,0))

REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL

REM 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS

REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ;

REM ... 0 rows

REM ALTER TABLE "ROBERT"."RW1" ADD PRIMARY KEY ("X") USING INDEX PCTFREE

REM 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 10240 NEXT 10240

REM MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST

REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE ;

REM CREATE TABLE "ROBERT"."RW2" ("COL1" NUMBER(*,0), "COL2" NUMBER(*,0))

REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL

REM 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS

REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ;

REM ... 0 rows

CONNECT ROBERT;

CREATE INDEX "ROBERT"."RW2_IDX" ON "RW2" ("COL2" , "COL1" ) PCTFREE 10

INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1

MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

DEFAULT) TABLESPACE "SYSTEM" LOGGING ;

REM ALTER TABLE "ROBERT"."RW2" ADD CHECK (col2>0) ENABLE ;

REM ALTER TABLE "ROBERT"."RW2" ADD FOREIGN KEY ("COL1") REFERENCES "RW1"

REM ("X") ENABLE ;

If we remove the REM statements, we have the DDL for objects that consume space, but not the trigger or view. EXP exports these objects, but IMP does not show them in the INDEXFILE option

Cloning Schemas

EXP and IMP can be used to clone a schema for testing. Using the FROMUSER and TOUSER options of the IMP command, we can easily export data from one user account to another.

Copying Data between Platforms

EXP and IMP provide a good way to copy data from one platform to another, or even as a way to ‘email’ some data to someone else. If we create a DMP file on one platform, we can import that on any other platform – the data is platform independent, even though the DMP file is a binary file.

Large Exports

When EXP is used to write to a device that supports ‘seeking’, as a normal file does, it is limited in the size of the file it can generate. EXP uses the normal OS file APIs, which, on a 32-bit OS, limits the size of the file to 2GB. There are four (mainly used) solutions to this issue , and we’ll take a look at each of these.

Use the FILESIZE Parameter

This option first came with Oracle 8i. Using FILESIZE parameter we can set the maximum size (in bytes) of the DMP files that constitute our export, and EXP will create as many DMP files as necessary in order to export the data. For example, in order to export a series of files that should be no larger then 500 MB apiece we can use:

exp userid=robert/robert file = file1,file2,file3,file4,file5 filesize = 500m owner=robert

This would create DMP files file1.dmp,file2.dmp, and so on, each of which would be up to 500 MB in size. If the total size of the export were less then 2GB, EXP would not need to create the file5.dmp file.

Exporting Smaller Pieces

This solves the problem by avoiding it altogether. If you have a 10 GB database with 50 application schemas, and each schema is under 2 GB is size, you can use user-level exports. You’ll end up with 50 export files, each of which contain an application schema.

Export to an OS Pipe

This is a solution that works very well on Unix. In this case mknod command it used to create a named pipe. A named pipe is device by which one process can write into the pipe and another process can read out from the other end. EXP can write an unlimited amount of data to pipes, since they do not support ‘seeking’. In the event the dmp file is larger than 2 GB in size, we can use split utility to break the file up into smaller pieces. Below is the script for EXP and IMP I use to export and import VLC database on Unix:

Export

cd /home2/dmp

rm export.dmp

mknod exp.dmp p

split –b1024m < /home2/dmp/export.dmp &

exp userid=user/passwd file= /home2/dmp/export.dmp

Import

cd /home2/dmp

rm export.dmp

mknod exp.dmp p

cat xaa xab xac xad > /home2/dmp/export.dmp &

imp userid=user/passwd file= /home2/dmp/export.dmp

Backup and Recovery

EXP and IMP should not be considered backup tools. They are not appropriate to be your backup and recovery mechanism. RMAN and operating system backups are the only true backups. The reasons EXP/IMP should not be considered as your backup tools are:

They present at best, a point-in-time picture of the database. Using CONSISTENT = Y will allow you to extract a point in time picture of the database, but it is just that – a single point-in-time. If you use this export to recover, you will lose all work that happened after the start of the EXP. Also, archived redo logs cannot be applied to an IMP.

Restoring a database of any significant size using IMP is a slow process; all data will have to be inserted, all indexes will have to be rebuilt, all constraints must be validated, all code must be compiled, and so on. What might take a few minutes with a real backup will take hours or days using IMP.

Incremental EXP/IMP will soon no longer be a supported feature. The use of the INCTYPE= parameter will be removed. The quote from Oracle is ‘Important: Incremental, cumulative and complete Exports are obsolete features that will be phased out in the subsequent release.

It does not mean that EXP/IMP utilities are not useful as part of a larger backup and recovery plan. It is believed they can play an important role as part of a larger backup and recovery plan. Your database must be running in archive log mode to allow you to perform ‘point-in-time’ and media recovery. This is crucial and there is no substitute for it. In addition to this, some proactive fault detection is a god idea as part of a well-rounded backup/recovery plan. As part of this, EXP can be used as mentioned above. This fully exercises the data dictionary, using almost all of its indexes and objects ensuring it is OK. It also scans all table data, ensuring that it is safe. The resulting DMP file can also be useful to extract that lost piece of code, or that accidentally dropped table at times as well, saving us from having to perform a point-in-time recovery in many cases.

Cloning

This is a common use of EXP/IMP; you want to copy an entire application schema. You need to copy all of the tables, triggers, views, procedures, and so on. In general, this works great, I simply use the following:

Exp userid=robert/robert owner=old_user

Imp userid=robert/robert fromuser=old_user touser=new_user

Using IMP / EXP Across Versions

You can easily IMP and EXP across different versions of Oracle. You can ever EXP and IMP to and from version 7 databases and version 8 databases. However, you have to use the proper version of EXP and IMP when doing so. The rules for picking the version of IMP and EXP are:

Always use the version of IMP that matches the version of the database. If you are going to import into version 8.1.6, use the 8.1.6 import tool.

Always use the version of EXP that matches the lowest of the two versions of the database. If you were exporting from version 8.1.6 to 8.1.5, you would use the version 8.1.5 EXP tool, over Net8, against the version 8.1.6 database. If you were exporting from 8.1.5 to 8.1.6, you would use the version 8.1.5 EXP tool directly against the 8.1.5 database.

Summary

We have covered many uses of the tools Import and Export. We have presented the common solutions to the problems. EXP and IMP are extremely powerful once you get a trick or two under your belt.

The roles of IMP and EXP are changing over time. In the Oracle version 5 and 6 days, it was considered a viable backup tool. Databases were small (a 100 MB database might have been considered large). Over time, the usefulness of IMP/EXP in the area of backup and recovery has severely diminished, to the point where I would say outright that it is not a backup and recovery tool at all. Today EXP and IMP are relatively simple tools that should be used to move a modest amount of data back and forth between instances.

It still has many of its conventional uses, such as the ability to ‘clone’ a schema or to extract the DDL for a schema to a file.

Robert Wylie
DBA, SSG VLC System
O/o the Accountant General(A&E),
Rajasthan, JAIPUR-302005

Email :

Robert WylieImport and Export( 1 )