Oracle import

I was asked by a colleague, how to import an oracle dump file, that was exported from customer production site without data. Someone may think that it is stupid to write about this trivial task. However it is not so simple. In order to provide support for production database, we sometime have to export customer database without data and then import the exported file containing only schema and store procedure into our developer sandbox for testing purpose. The problem is that when import, Oracle try to create table and allocate data segment on disk with the same size as in the production database. Our developer sandbox of couse does not have capacity as customer production machine, so the import process often fails. Solution to this problem is perform import process into 3 steps. In Step 1, we create table and index creation file using e.g.

$imp userid=system/manager@test file=exp-code.dmp full=yes indexfile=create-schema.sql

This will create create-schema.sql that contains tables and index creation commands. With little effort, using any text editor, we can modify this file to remove segment size parameter of each command and save it as create-schema-small.sql. In Step 2, we run modified create-schema-small.sql against test database to create tables and indexes

$sqlplus scott/tiger@test @create-schema-small.sql

In Step 3, we run normal import with ignore error option, so Oracle will create store procedures and others required metadata.

$imp userid=system/manager@test full=yes file=exp-code.dmp ignore=yes

After reading, other colleague points out that in Oracle 10g, Oracle provide other tools expdp and impdp that can handle the above mentioned problem.