problems with exp and expdb in 10gR2

Hello,

We have noticed (in a recently upgraded to 10gR2 database) some problems with the exp and expdp tools.

Our backup scritps use the traditional exp tool... and we found this in the log...

EXP-00104: datatype (BINARY_FLOAT) of column M_SUBSET_STD_PAIR_UNSIGNED_I_1 in table ATLAS_NFIUZA.LARCONDITIONSSUBSE_6 is not supported, table will not be exported

conclusion : EXP can not export tables that contain BINARY_FLOAT and BINARY_DOUBLE datatype columns

No good... ok. let's try with expdp..

ORA-39139: Data Pump does not support XMLSchema objects. TABLE:"STRIDE"."equipment-model285_TAB" will be skipped.

oops!

Conclusion : EXPD can not export tables that contain columns with XMLSchema info, you get something like

Summary: you need to run both tools if you want to have something similar to a complete export, but as you can guess this is quite ..er... "inconvenient"...

Potential big problem: you may end up with tables that have both the BINARY_FLOAT|BINARY_DOUBLE and the XMLSchema datatypes in the same table, then you are done... those tables can not be exported by any method.

EXP/IMP tools must be able to dump the contents of the database and restore them, if they do not then it is a serious bug.

I have opened a SR in Metalink but so far, the support person does not seem to understand how serious the problem is. The way I see it, the Oracle policy is something like, "this situation is unlikely to happen, so we will not fix it now, eventually in the future" This is like driving a car without an insurance, it is fine provided that you do not have an accident. The analyst said that I should write a script to unload the table data into a format compatible with SQL*Loader to restore it in case of need :( , that there could be technical problems that prevented Oracle from doing this... oh boy...

It would be nice if everybody would open a SR asking Oracle to fix their broken tools....the more the better, just to increase the chances of getting a fix.

I have not seen this problem mentioned anywhere, so probably not many people are aware of it. I know that this was previously filed as an internal bug before our findings, but apparently not much progress since.

regards.

Nilo Segura Chinchilla
Oracle Support - IT/DES
CERN - Geneva
Switzerland

--
http://www.freelists.org/webpage/oracle-l