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

> 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.

I like this option because it uses the system's interal mechanisms for supporting consistency. If at all possible, use the system's internal mechanisms rather than create your own. You'll save your self lots of time and headaches.

> 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.

I don't like this option because you will have to lock every table! What about other concurrent processes? They will come to a standstill if they have to update the tables and will wait until you are done. It would probably be better to just shutdown the database, bring it up in restricted mode, export the data, and then open the database to everyone, rather than lock all of your application schema's tables.

> 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.

You could do this, but unless you lock the tables like Option 2, you won't guarantee consistency between all of the tables.  

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

Issue the LOCK TABLE command would be an easy way.  

HTH,
Brian Received on Thu Sep 26 2002 - 13:31:41 CDT