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

What's wrong with...

set doc off
set echo off
set termout off
set feed off
set head off
set pages 0
set lines (you decide)
spool your_path_and_file_name_here.txt
SELECT column1||','||column2||','||etc... FROM your_table
;
spool off

It's fast, easy, the MVCM will ensure consistency, and you have a comma-delimited text file. I would use a pipe ( | ) instead of a comma, but it depends on what the intake system can handle.

"Brian Peasland" <oracle_dba_at_peasland.com> wrote in message news:3D93528D.ABD83B65_at_peasland.com...
> > 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 - 14:42:10 CDT