We want to depersonalize our data when we copy it from a production environment to a test environment. This is for privacy and security reasons. We have made a batch program that is able to change data from a flat file. We use it on unloads of DB2 production databases and, afterwards, we are able to load the data into test databases. But Oracle only has the extract facility, which delivers binary files. Is there a similar way to do this or is there any other way to depersonalize data in a standard way?
Oracle's export utility creates a dump of data in a proprietary binary
format. However, you can still create flat files from the Oracle
database, but not with the export utility. You can use SQL*Plus as
set echo off
set heading off
set feadback off
set pagesize 0
The other option is to use Oracle's DBMS_UTILITY.TABLE_TO_COMMA
supplied procedure. It assists in making comma-delimited flat files.
This was first published in November 2003