How to copy data encrypted by dbms_obfuscation_toolkit.DESEncrypt

Question

Hi,

I have an Oracle (10.2.0.4) database table with a column which is encrypted by dbms_obfuscation_toolkit.DESEncrypt tool kit.

Some of our data has been messed up by it getting re-encrypted with another key.

I want to do some testing on this data to try and recover it. Therefore, I want to copy the data from our live system and into a test system.

I've tried simply exporting the data from SQL Developer (in various text based formats), but the "binary" nature of the encrypted data seems to break the file format.

I tried exp, but this reported errors (although I'm not sure if this is to do with the encrypted data or not).

How can I copy just this one table's data from one database to another?

Thanks.

---

The errors I got when exporting the table are below. I was doing this from my local machine connecting to a remote database:

c:\>exp <user>/<password>@<sid> FILE=export.dmp TABLES=(TABLE1)

Export: Release 11.1.0.6.0 - Production on Thu Oct 14 20:46:51 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                   TABLE1
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier

Answer 1

Hi A_M,

I would try with a database link. If you can't create a database link, you could try the COPY command of SQL*Plus, although I'm not sure if it would work with encrypted columns (it looks like this command is deprecated in the newest releases).

If this fails, the best tool to export/import data from Oracle to Oracle would probably be Data Pump (included in the DB).

Answer 2

It turned out that my Windows test database had a slightly different character set encoding when compared to our live (unix) system - WE8ISO8859P1 -v- WE8ISO8859P15. I did a character set conversion on my test database, using the instructions here and then I was able to import the data.