It’s been a while since i wrote anything on my blog. Not because I am lazy, but because I’ve been doing a bunch of proof of concepts for various customers. database machine, audit vault, data masking, rac and securefiles etc. Its been loads of fun.
I wanted to write about a a couple of neat things i came across .
Exporting from an oracle database in parallel
Imagine that you have a fairly large database and you want to export the database onto two different devices in parallel (Let us say you have two usb devices attached to the server and you want to leverage the write throughput you get to both simultaneously). You can do this in two steps
- Define 2 different oracle directories
- Let us say for eg: the drives you want to use are mounted at /u01/firstusb and /u01/secondusb
- create directory exp1 as ‘/u01/firstusb’;
- create directory exp2 as ‘/u01/secondusb’;
- While exporting use the directories in the dumpfile keyword
- expdp system/manager directory=exp1 dumpfile=exp1:exp_test_%U.dmp,exp2:exp_test_%U.dmp schemas=AAA,BBB,CCC,DDD parallel=8 logfile=exp.log
So since you are using exp1:exp_test_%U.dmp,exp2:exp_test_%U.dmp and a parallel=8, datapump creates 4 dump files each on exp1 and exp2 which points to /u01/firstusb and /u01/secondusb respectively.
Importing and Enabling Compression (OLTP or Exadata Hybrid Columnar Compression)
Let us say you want to export from a database that does not have compression turned on, and want to import into one with compression turned ON. Since the table is created with the NOCOMPRESS (defautlt) keyword, the expdp statement actually gathers this info and uses it to create the “create table” statement when it creates the table during the import. So the default is for the imported table also to be NOCOMPRESS.
If you only have a hand full of tables you want to enable compression on, you can pre-create the table (And its indexes and such) using the Compress for Oltp clause and then run the datapump import specifying the parameter table_exists_option=APPEND
If you want to do it for all the tables in a tablespace.
- Create the tablespace with compression enabled at the tablespace level.
- Then while importing using datapump specify the transform=SEGMENT_ATTRIBUTES:n:table parameter.
This causes import to ignore the segment attributes for the table while creating it, which will cause the table to inherit the attributes specified at the tablespace level and will be created with OLTP compression enabled.
