Archive for January, 2007


expdp and impdp are great

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



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.


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)


Oracle sucks on windows

Ok. I hate to say that because I prefer Oracle more than others.

But Oracle has a lot of memory leaks which won’t show until you build it on your live Server. The problem is that to starting observing the problem, the database needs to get some workload so it won’t show on a test or train server.

The reason is that there are bugs into this particular release that will threaten your critical systems.

The symptoms where we installed it are as follow:

1) Inability for new users to login to Oracle after a while because new sessions are not properly closed.

This problem can be solved with a tool such as currport. You should be able to see that you got a lot of sessions without a session ID. Oracle is not even able to kill them. Currports fortunately allows us to look into the sessions at OS level and kill the Close_wait sessions.

There are a few actions to take on the server which involve addind new keys in the registry.

2) Impossibility to perform a proper shutdown  without Oracle shutting down the service.

3) Oracle shutting down without warning

After looking into the problem and searching on Metalink, I found a list of bug full of memory leaks.

These issues appeared only on this particular release. This means that if you are on Oracle, you should skip this version and upgrade to Oracle

Oracle get one problem with bind variable but it’s minor compared to the bugs of