• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

Creating/Exporting to a text File from Oracle

I want to know if there is a way to export tables (or the result from a Select statement) from Oracle to a text file in fast way (a table of millions of records).

I tried in the past UTL_FILE functions and PRO*C/C++ routines that writes each row after opening a cursor.

SQL Plus spool option I think it´s no good idea because I need that this file generation got to be made in automatic way (called from a Oracle Form or PL/SQL Block or PRO*C/C++)

Is there a export option for this ? or maybe a better way to make this porcess ?
  • 7
  • 2
  • 2
  • +4
1 Solution
Ron WarshawskyCommented:
You'll need 3rd party tool. Like Platinum or BMC.

PLATINUM Fast Unload for Oracle is a high performance data export utility.

It provides full support for all Oracle7 type objects as well as partitioned tables in Oracle8, and unloads data from data files larger than 2GB.

Fast Unload exports data substantially faster than Oracle Export by directly accessing Oracle database files, completely bypassing the SQL engine and Oracle kernel.

On a multiprocessor host, Fast Unload provides even more significant increases in throughput via asynchronous I/O and faster data handling.

This data export utility is integrated with PLATINUM TSreorg to speed up the reorganization of Oracle tablespaces, tables, and indexes.
                                                                                  Fast Unload for Oracle allows users to tailor the format of the data output, ensuring that the file is ready for its intended purpose without further manipulation.
                                          The user can also determine the rows and columns being extracted.



NetoManAuthor Commented:

then you think there is no other way than buy a 3rd party tool ?

I´ll leave open this question for another comments. If there are not more likely comments, I´ll give you the points of the answer.

Ron WarshawskyCommented:
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ron WarshawskyCommented:
P.S. This is the reason 3td party tools exists. They do something, original tools doesn't.
rwarsh is right.
There is no way you can do that with the tools/utilities oracle offer.

Oracle's only data downloading utility is the export utility.

Of-course 3rd party companies can use the database tables or even the datafiles themselves to create other fast exporting utilities.

If you still don't want to use 3rd party tools, and the spooling data from sqlplus is good for you, you can still automate the spooling process in sqlplus using  simple scripts.

Before you try 3rd party tools, you should try to squeeze the most out of Oracle's export utility.

Try doing the export with the DIRECT=Y directive and set RECORDLENGTH appropriately.

Note that there are restrictions for using direct mode export.

From Oracle Documentation:

Conventional path Export uses the SQL SELECT statement to extract data from tables.
Data is read from disk into a buffer cache, and rows are transferred to the evaluation
buffer. The data, after passing expression evaluation, is transferred to the
Export client, which then writes the data into the export file.

Direct path Export extracts data much faster than a conventional path export.
Direct path Export achieves this performance gain by reading data directly,
bypassing the SQL Command Processing layer and saves on data copies whenever possible.

For added performance, you can set the database to direct read mode. This eliminates
contention with other users for database resources because database blocks are read
into the Export session's private buffer, rather than into a public buffer cache.
For more information about direct read mode, see the Oracle8 Administrator's Guide.

In a direct path Export, data is read from disk into the buffer cache and rows are
transferred directly to the Export client. The Evaluating Buffer is bypassed.
The data is already in the format that Export expects, thus avoiding unnecessary
data conversion. The data is transferred to the Export client, which then writes
the data into the export file.

To reduce contention with other users for database resources during a direct path Export,
you can use database direct read mode. To enable the database direct read mode, enter the
following in the INIT.ORA file:

  compatible = <db_version_number>,
  The db_version_number must be 7.1.5 or higher.

You may improve performance by increasing the value of the RECORDLENGTH
The following values are generally recommended for RECORDLENGTH:

  multiples of the file system I/O block size
  multiples of DB_BLOCK_SIZE


You cannot use direct path Export to export rows that contain LOB, BFILE, REF, or
object type columns, including VARRAY columns and nested tables.
Only the data definition to create the table is exported, not the data.
Ron WarshawskyCommented:

 How this answers original question?

i.e "export tables (or the result from a Select statement) from Oracle to a text file"
Oops, didn't notice the specification was explictly for *text* file.
Jury will disregard my previous comment ...

Unleash the 3rd party tools.
Well.. you still could use the spool option, but formating the data at the same time that you list it. ie:

SELECT "" || DsName || "", "" || DsLastName FROM Users

(I'm not sure if the amounts of " is rigth)

It should leave in your file something like this:

<--- File content -->

SQL>SELECT "" || DsName || "", "" || DsLastName FROM Users

"" || DsName || "", "" || DsLastName
"Heinz", "Frentzen"
"Ramiro", "Reyes"

(2 rows retrieved)

<--- End of File -->

So you only have to delete the info that u don't want and keep the rest of it, having the fields delimited by " and the rows by ENTERs.

I hope it might be usefull :-)
Sorry, the SQL should be:

SELECT '"' || DsName || '", "' || DsLastName FROM Users
For a faster and economic way,

you can use a free third party tools

TOAD - Tool for Oracle application Developer


It is very user friendly tools to export table's data from database.

Ron WarshawskyCommented:

 Use of TOAD is sililar to spooling. To get fast unload TOAD is useless.

Mark GeerlingsDatabase AdministratorCommented:
I think you can see by now that there isn't an easy, cheap way to export Oracle data to an ASCII file.  I've had very good results with utl_file with tables of up to 1 - 2 million rows.  (I haven't needed to with larger tables).  That only works though with "standard" data types (text, number and/or date and even with dates you have to be careful of the format mask).

There are at least a couple of advantages with utl_file approach:
1. you can sort the data, if you want to, when you build the ASCII file

2. your main cursor can be based on just a small range of primary key values and that can be inside a loop that increments the range, then gets the next set, so you avoid the "snapshot too old..." error if the table has other activity at the time.

I've also heard of good results with the third-party tools from Platinum and BMC, but they are not cheap.
NetoManAuthor Commented:
Alex.. I already try TOAD, but it´s not faster and the main problem with TOAD is that I can´t export data in automatic way (like a call from a program or something like that).


To other friends:

Well I think the 3dParty Tool can give me speed but can´t give me automatic generation from a "program-call". Then I conclude based on your comments, that the very fast way is make (like Im doing right now) a PRO*C/C++ program for my purposes. This is faster than UTL functions for reading/writing (based on my tests).

I want still now if what sbenyo says about make a script to spool can be called from an "outside" program (PL/SQL, PRO*C, etc).

Ron WarshawskyCommented:


  I beleive, my information fully answer your original question.

  As you told yourself: "If there are not more likely comments, I´ll give you the points of the answer."


P.S. You defenitely can call 3rd party tools from your program.
Ron WarshawskyCommented:
Thanks for assigning points to me, but why grade B?



Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 7
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now