An Oracle blog – Amardeep Sidhu

June 6, 2007

IMPORT & EXPORT from/to compressed files directly…

Filed under: Oracle Tips — Sidhu @ 8:57 pm
Tags:

Well, a simple method to import(export) directly to(from) compressed files using pipes. Its for Unix based systems only, as I am not aware of any pipe type functionality in Windows. The biggest advantage is that you can save lots of space as uncompressing a file makes it almost 5 times or more. (Suppose you are uncompressing a file of 20 GB, it will make 100 GB) As a newbie I faced this problem, so thought about writing a post.

Lets talk about export first. The method used is that create a pipe, write to a pipe(ie the file in exp command is the pipe we created), side by side read the contents of pipe, compress (in the background) and redirect to a file. Here is the script that achieves this:

export ORACLE_SID=MYDB
rm -f ?/myexport.pipe
mkfifo ?/myexport.pipe
cat ?/myexport.pipe |compress > ?/myexport.dmp.Z &
sleep 5
exp file=?/myexport.pipe full=Y log=myexport.log

Same way for import, we create a pipe, zcat from the dmp.Z file, redirect it to the pipe and then read from pipe:

export ORACLE_SID=MYDB
rm -f ?/myimport.pipe
mkfifo ?/myimport.pipe
zcat ?/myexport.dmp.Z > ?/myimport.pipe &
sleep 5
imp file=myimport.pipe full=Y show=Y log=?/myimport.log

In case there is any issue with the script, do let me know 🙂

Update: If you are on Wintel, you can directly use a compressed folder as an export target. No need to create a pipe as the file system will automatically do it for you. ( Thanks Noons for the tip)

If you are using gunzip:

For export:

export ORACLE_SID=MYDB
rm -f exp.pipe
mknod exp.pipe
gzip < exp.pipe > T1.dmp.gz &amp;amp;
exp file=exp.pipe full=Y log=myexport.log

For import:

export ORACLE_SID=MYDB
rm -f imp.pipe
mknod imp.pipe
gzip < T1.dmp.gz > imp.pipe &amp;amp;
imp file=imp.pipe full=Y log=myimport.log

Update: I came across an article that discusses few ways to achieve the same on Windows. Check it here.

5 Comments »

  1. Suggestion: in Wintel you could use a compressed folder as the target for the export file.
    No need for the pipe as the file system does it for you automagically.

    Comment by Noons — June 27, 2007 @ 5:34 pm | Reply

  2. […] to create locally managed tablespaces. (If you have the dmp file in compressed (.Z) format check here, to run the import directly from compressed […]

    Pingback by Oracle and more - Amardeep Sidhu » Blog Archive » Importing a full database… — June 4, 2008 @ 11:33 pm | Reply

  3. I want to do compressed export on the fly, but may database is big and in my export I need to put filesize=5G and I had 12 files until now, then I want to do compressed export but using more than 1 file.

    Can you help me?

    Comment by Gabriela — August 1, 2008 @ 8:06 pm | Reply

  4. Hi

    When you pass filesize=5g to exp, it is handled by Oracle and it knows that it is creating multiple files. When we import it back and pass all the file names, again Oracle is aware that “I created these files and i know how to read them in sequence”.

    But here we do a trick: we create a pipe, feed it from one end and redirect the other end to a file and ask OS to compress it. Now this is entirely an OS thing. Oracle is writing to only one file that is export pipe !

    Right now nothing is hitting my mind, but if something is possible, i think that has again to be done at OS level. exp doesn’t understand anything like compression of files.

    If you come to know something, do let me know 🙂

    Comment by Sidhu — August 3, 2008 @ 9:53 pm | Reply

  5. Gabriel,
    As Sidhu already mentioned,there is no way by whch export files can understand compression. There is a limit that oracle puts over the export file and that’s 2g. That’s what they maintain.For the compression either you have to use the filesize option or use a pipe to compress it. Here are two links that you can use as a refenece for using a pipe.

    http://www.tc.umn.edu/~hause011/code/exp-imp-db.ksh
    http://www.jlcomp.demon.co.uk/faq/bigexp.html
    HTH
    Aman….

    Comment by Aman.... — August 4, 2008 @ 11:32 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Theme: Rubric. Get a free blog at WordPress.com

%d bloggers like this: