DBA Tools

    When export oracle data to different database platform, text file is always a good choice and may be the only choice, if they cannot connect directly. And some time I was required to provide some rows in excel CSV file (a "," separated text file) or excel xls file (a tab separated text file) to business people. And also provide large volume data for data warehouse team to load them into Teradata system.

    So we need a good tool to unload rows to these files from oracle database, I wrote one few years ago. It's ociuldr (Binary/Source), OCI is the short name of Oracle Call Interface means high performance, uldr is the short name of unloader. I already wrote a very detailed document for this utility. I will introduce how to generate these kinds of text file here briefly.

    We want to list the table name of schema test into CSV file.

ociuldr user=test/test query="select * from tab" field=0x2c file=test_tables.csv

    We want to do the same job, but save it as a excel xls file.

ociuldr user=test/test query="select * from tab" field=0x09 file=test_tables.xls

    Now we need to unload lot's of data (from table SALES, and the query is really complex, so I save it as sales.sql) to data warehouse team, the asked to seperate the column value with line char ("|").

ociuldr user=sales/sales sql=sales.sql field=0x7c file=sales_for_dw.dat

    The tools provide a lot of option for performance control, and can generate a sqlldr control file for you to easily load the unloaded data to another oracle database.