One of the biggest challenges for DBAs is the lack of disk space especially for the backups. Two days before, I came across the situation where I was need to do export/import schema from UAT to DEV, but none of the mount points on filesystem were having sufficient space available to fit export dumpfile. In my case, taking EXPDP schema backup followed by compresing dumpfile with GZIP/BZIP2 was not possible due to insufficient disk space.
So I started exploring different methodologies to apply compression on dumpfile which will allow me to compress dumpfile on fly.
1. Use of pipes:
Export:
cd /home/oracle # create a named pipe mknod exp_disc_schema_scott.pipe p # read the pipe - output to zip file in the background gzip < exp_disc_schema_scott.pipe > exp_disc_schema_scott.dmp.gz & # feed the pipe exp file=exp_disc_schema_scott.pipe log=exp_disc_schema_scott.log owner=scott
Import:
cd /home/oracle # create a name pipe mknod imp_disc_schema_scott.pipe p # read the zip file and output to pipe gunzip < imp_disc_schema_scott.dmp.gz > imp_disc_schema_scott.pipe & # feed the pipe imp file=imp_disc_schema_scott.pipe log=imp_disc_full.log fromuser=scott touser=scott1
Advantages:
1. Compression happens parallel with the export.
2. Compressed dumpfile can be used for import without decompression. (As shown above)
3. Can be used for 10g as well as 11g.
Disavantages:
1. Can’t use this methodology for EXPDP/IMPDP.
2. Advanced compression:
From 11g, You can used advanced compression menthology to compress dumpfiles on fly. This can be used for compressing data, metadata (which is the default value), both and none.
expdp directory=DATA_PUMP_DIR1 dumpfile=exp_disc_schema_scott.dmp logfile=exp_disc_schema_scott.log schemas=scott compression=all.
Advantages:
1. Compression happens parallel with the export.
2. Compressed dumpfile can be directly used for import without decompression.
Disavantages:
1. It takes more time than normal EXPDP operation.(without compression)
2. Advanced Compression License option must be enabled which is an extra cost.
3. This option is just available from Oracle 11g
Hope so u will find this post very useful 🙂
Cheers
Regards,
Adityanath
Categories: Administration, Advanced features, backup & recovery
Adityanath's Oracle Blog 
so much wonderful info on here, : D.
some genuinely interesting details you have written.
I am really grateful to the owner of this web page
who has shared this impressive post at at this place.
Terrific article
Do you need the license to import as well in another environmnt like AWS RDS ?
Hello,
I dont feel that you require license on target side to complete import but its compulsory to have enterprise edition on target.
Better you can check with Oracle support itself on licensing part.
Regards,
Adi
Hello! I’ve been reading your site for some time now and finally got the courage to go ahead and give you a shout out from Atascocita Texas! Just wanted to tell you keep up the good work!
It’s a shame you don’t have a donate button! I’d definitely donate to this fantastic blog! I suppose for now i’ll settle for bookmarking and adding your RSS feed to my Google account. I look forward to brand new updates and will share this blog with my Facebook group. Chat soon!
Excellent post. I used to be checking continuously this blog and I am inspired! Very helpful information particularly the ultimate section 🙂 I care for such info much. I was looking for this certain info for a very lengthy time. Thanks and best of luck.
Thank you for another informative blog. Where else could I get that kind of information written in such a perfect way? I’ve a project that I am just now working on, and I’ve been on the look out for such info.
I have recently started a blog, the info you offer on this web site has helped me greatly. Thanks for all of your time & work.
Hi there I am so grateful I found your website, I really found you by mistake, while I was researching on Google for something else, Anyhow I am here now and would just like to say kudos for a fantastic post and a all round entertaining blog (I also love the theme/design), I don’t have time to read it all at the moment but I have saved it and also added in your RSS feeds, so when I have time I will be back to read much more, Please do keep up the great job.
Excellent web site you have got here.. It’s difficult to find quality writing like yours nowadays. I honestly appreciate people like you! Take care!!
thank you for sharing with us, I think this website really stands out : D.
It’s a pity you don’t have a donate button! I’d definitely donate to this excellent blog! I suppose for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to new updates and will talk about this website with my Facebook group. Talk soon!
This will be a great web page, will you be interested in doing an interview regarding just how you developed it? If so e-mail me!