RE: Dumping contents of a view to CSV file
Date: Thu, 27 Oct 2011 11:21:02 -0400
Message-ID: <013601cc94bc$0ac2c1c0$20484540$_at_rsiz.com>
sqlplus run directly on the server where both the database and the output file are to be created is probably as good as anything else. If you're on something from the UNIX family with pipes and redirection you can avoid the screen formatting and go directly to an output file in background, possibly using compression in the pipe flow to the final destination. Of course if this is intended to be a final flat file preservation that might imply you're not supposed to rely on anything other than simple ASCII read back. Software of all varieties varies over time and the future uncompressability of a current save might come into question. And of course there is the question of output media. Anyone try to read in a 9-track tape recently?
The other thing you should think about carefully is that you have varchar2
columns. If you're creating a comma separated values (CSV) file and you
really are using commas as the field separator, you need to verify that zero
commas exist in your output columns.
If they do, then you need to get the chain of command that created the task
to understand this complication. A different separator value is appropriate
for some purposes. A value that is rarely actually found in text is the
actual field separator character in ASCII, but again you'll have to check
whether that does NOT appear in your actual values. Another candidate
character useful if you need something visible is vertical bar, but again it
cannot be in the data and you have to check.
It is entirely possible to have a data set for which no meaningful CSV can be created, even with allowance for the C being defined as something other than comma. Then you need to pass that up the chain. From an information preservation context delimiter separated files with quoting rules for when the delimiter appears in the data can be accomplished, but it is stretching the definition of CSV to even use something different from a comma for the separator, let alone required field parsing.
If your data simply contains no commas, then fine, but part of your task should be to check.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Steve Wales
Sent: Wednesday, October 26, 2011 6:00 PM
To: oracle-l_at_freelists.org
Subject: Dumping contents of a view to CSV file
Hello,
I've been tasked with generating a CSV file with the contents of a view in an old database as an extra copy before the DB is decommissioned.
I've done this in the past for smaller tables or views without issue.
There are 80 million rows in the view, full row length is 5500 bytes (most probably shorter since the columns on the base tables are mostly varchar2).
Doing the math on that, that's a maximum possible size of 440GB.
I had seen a stored procedure on a website somewhere that read through a cursor and dumped the records out to a text file via utl_file.put. Anyone have a better method or is that my best option ? I've had some TEMP tablespace failures, I may just need to allocate a bunch of space in temp, kick it off one afternoon and let it run overnight.
I've could shrink the overall output size of the file by running "trim" on the columns too.
Any ideas appreciated.
Environment: Oracle 9.2.0.7 on HP-UX 11.11
Thanks
Steve
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 27 2011 - 10:21:02 CDT