Best way to export data from multiple tables to delimited text file

Hi all,

I have several large tables (holding between 2 and 5 million records each) in an oracle (8i) database on solaris 8.

These tables are being updated frequently and are related to each other through foreign keys.

I want to export the data from all of these tables to a text file (one text file per table) on the file system. I also need the data to be in a consistent state (in the same way that the CONSISTENT=Y parameter works in EXP). This is important because as I said above, the tables are being updated regularly.

I will be doing this export on a monthly basis, and I will be scheduling it to run late in the evening.

What is the best way to go here?

Options I have considered ...

1. Create a DMP file using EXP with CONSISTENT=Y and then IMP into a temporary schema. I will then write some PL/SQL using UTL_FILE to extract the data from the tables in the temporary schema (which will be in a consistent state) into text files on the file system.
2. Write some PL/SQL which will initially "lock" the tables against update, then use UTL_FILE to extract the data (which will be in a consistent state) into text files on the file system.
3. Create "snapshots" of the tables into temporary schema first, then write some PL/SQL using UTL_FILE to extract the data from the tables in the temporary schema (which will be in a consistent state) into text files on the file system.

If option 2., what would be the best method of locking the tables against update at the start of the PL/SQL script?

The reason for doing this is to load the data into a SQL Server database (I do not have a direct link between the databases), and I believe that I cannot achieve this using the DMP file directly - is this correct?

I would greatly appreciate help from anyone who has experience in this area.

Thanks in advance.

P. Received on Thu Sep 26 2002 - 07:59:25 CDT