vineri, 28 iunie 2013

Don't try to expdp with include option before read this, you will live a sadistic game of errors

Now, i will imagine how  an expdp command should look, at the first touch of keyboard, for any of us, mortal human beings. Maybe somehow like bellow?

expdp schemas=myname include=TABLE:"IN ('table1','table2')" dumpfile=some1.dmp

As you can see, i try to export, using datapump, two tables. Just two tables.

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:"IN ('table1','table2')" dumpfile=some1.dmp




You can use the "as sysdba" option or not, if not, then you can type "as sysdba" when requested.
Don't get astonished, the expdp command requests escape characters, that's it. First of all, you will get the following error:

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

and this is just the beginning. I will short your efforts, the problem is: missing escape characters. After correction:

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:\""IN ('table1','table2')\"" dumpfile=some1.dmp

As you can notice, UNIX demands about all the characters, except letters, being escaped. But don't cheer yet, you will see, of course, an error:


ORA-39165: Schema MYNAME was not found.
ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_02" completed with 3 error(s) at 05:28:00

Now, this is what i call to be frustrated. Thehis error message is more than ambigous. Useless to say, the user exists, the tables also. Well, you wanna know what is the problem? The lowercase letters composing the tables names. Anyway,

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:\""IN ('table1','table2')\"" dumpfile=some1.dmp


won't do the work either, because the name of tables should be uppercase.

expdp   \'/ as sysdba\'  schemas=myname include=TABLE:\""IN ('TABLE1','TABLE2')\"" dumpfile=some1.dmp

Now, everything is ok.

There is another way to acomplish the task: parameter file. The command is:

expdp   \'/ as sysdba\' parfile=myfile.par

and an example of a posible content of the parameter file is:

DUMPFILE=myfile.dmp
SCHEMAS =myname
INCLUDE=TABLE:"IN ('COM_BODIES', 'COM_REGIONS')"

Notice two things: the uppercase tables names is still mandatory and you don't need, anymore, escape characters. This can be a reason to preffer this approach, be aware about the number of tables, can be tens of them, and you should escape a lot of commas if you choose the alternative without parameter file.