Before oracle 10g, there was exp and imp tools. They are good export and import tools but I had only one problem with them.

In Thailand, people tend to put all tables in the same schema even if it’s a different application. That is simply because they don’t know any better.

For better administration and security, I want my tables splitted per application in different schemas with limited access to other schemas if required. Additionally to that, I would like to have those tables in a separated tablespace (and datafile).

exp/imp tool allows to move tables from one schema to another schema and the option is logically called fromuser/touser.

The problem is that if I wanted to move the tables to another tabelspace it was a little more tricky. It involves generating the script of the tables using the exp tool. Modify them manually by changing the targetted tablespace and then run them in sqlplus to generate the tables in the new schema and new tablespace.

With expdp and impdp, I can first select the tables I want to move (that will be the same as before using tables option).

The greatness comes with impdp where I can mention the following options: REMAP_SCHEMA (a bit like the fromuser/touser of imp) and REMAP_TABLESPACE. That is really cool.

I had a bit of trouble getting expdp and impdp to work. The thing is that before using them , the DBA must create a directory as follow:

C:\ sqlplus / as sysdba

SQL>CREATE DIRECTORY DATA_PUMP_DIR AS ‘A VALID DIRECTORY’;

SQL>COMMIT;

In my opinion, the commit is not required because create directory is a ddl command. But I was struggling and I saw an example doing like this. I’m not too sure what worked because I had to drop the directory first (just like we drop a table but we mention directory instead) and recreate it. So it might be it or might not. If anyone got any idea, I’m happy to know and the comments would do marvel for this : )

lLet’s get back to work.

It is now possible to call expdp

C:\ expdp system\acomplexepassword@tnsname directory=data_pump_dir dumpfile=Mydump%U.dmp tables=my,big,list,of,tables

And here you go. Your data are exported.

Then use the impdp command as follow:

C:\ impdp system\acomplexepassword@tnsname directory=data_pump_dir dumpfile=mydump01.dmp remap_schema=source_schema:dest_schema remap_tablespace=source_tablespace:dest_tablespace

And this is it.

In 2 command lines, what would have been otherwise a complexe situation becomes very easy.

note:

1) If you don’t have the data_pump_dir directory specified the you’ll get error messages

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

2) The dumpfile name in impdp is called dump01.dmp because I specified the variable %U in the name at export time. It’s an increment. It means you can keep the same name, if the file already exists in the directory then dump02.dmp will be created etc…

3) A valid directory in the example above is a directory that exists on your server (such as D:\dumps)