Bin, You're absolutely correct that we had mentioned the use of SQL*Plus as a cheap homegrown "SQL*Unloader" tool in our book, and I've posted Gary's "unload.sql" scripts on my website (http://www.EvDBT.com/library.htm) as well. But for high-volumes of data, SQL*Plus simply does too much work (it seems) and cannot compete with a stripped-down tool or utility (such as a simple PRO*C or OCI program).
This is not unique to Oracle and SQL*Plus. I recently had the task of converting a Sybase data warehouse to Oracle, and I fell into the same trap with the Sybase command-line interpreter, called ISQL. Not only is ISQL quite slow, but it has the disconcerting habit of rounding numeric data values to 2 digits, just because it feels happens to like that form of rounding. I thought, "OK. Better to use the 'bulk-copy' utility named BCP, anyway". BCP is faster but also rounds to 2 digits! Astounding! So, I ended up writing korn-shell scripts that use ISQL to describe tables (using SP_HELP), and then take that metadata to generate DBLIB "C" programs to do all of the unloading from Sybase. For all scenarios, I piped the output through a UNIX pipe and used SQL*Loader to load into Oracle. Allowed us to unload/load about 250Gb of data in about 8-9 hours elapsed, in the end... -Tim ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, April 30, 2002 5:43 AM > Tim, > > Thank you for your reply. > > There are two issues that I cannot use pipe. One is two databases are in two > different isolated network. Second is we need the flat files for archives. > BTW, whenever we > can use PIPE, we can use database link. Is it right? > > I understand that SQLPlus is just a command line interface to interactively > query Oracle databases. It's also a report writer widely used by DBA. It > isn't a proper tool for data > extraction. However, it has been treated as unload tool in some places. It > is introduced as unload tool in your book -- Essential Oracle8i Data > Warehousing. Using unload, sqlplus, Thomas Kyte to search in > groups.google.com, I also find that he uses it as a suggestion several > times. Anyway, what is a proper tool for data extraction in Oracle? exp/imp > isn't. Although there are quite a few third party tools, wouldn't it be nice > if Oracle offically issues one? > > Kind Regards, > Bin > > Original message from: "Tim Gorman" <[EMAIL PROTECTED]> > > > >It's SQL*Plus. Love it to pieces, but it just dawdles when used as > an > >"unloader". It must spend a tremendous amount of processing just > formatting > >or something... > > > >Never bother blaming the network or Net8 unless you are just trying > to get > >someone off your back to delay for time. DBAs will say "it must be > the > >network" to cause users to go hounding off after the network > administrator > >or systems administrator. While they're baying at that poor person, > you > >should have at least an hour to find out what's really going on... > > > >If you are working on UNIX, one thing you can do to speed up > >unloading/loading is put a UNIX "pipe" or FIFO between the "unloader" > >process (i.e. SQL*Plus or other) and SQL*Loader, instead of a "flat > file". > >Saves disk space but most importantly saves time -- you don't have to > wait > >for unloading to complete before loading. In fact, loading must > start with > >the unloading, so you can't help but save both time and space... > > > >----- Original Message ----- > >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > >Sent: Tuesday, April 23, 2002 10:53 PM > > > > > >> Hi, > >> Our application uses sqlplus + sqlloader to transfer data between > >> databases. It takes nearly four hours to unload to data to flat > >> files(1G), which is far too slow. In the application, the query > looks > >> like the following. All those &3,&4,&5 are for sqlldr format. > >> select ' ' || > >> '&4' || replace( replace ( ltrim(dealerid), '&4', '&4' || > >> '&4' ), CHR(10), CHR(10) || '&5' ) ||'&4'||'&3' || > >> ... > >> from table_name f > >> where eventdate >= to_date(&1) > >> and eventdate <= to_date(&2); > >> Firstly, there is nothing wrong with the query, since if I insert > into a > >table > >> it only takes less than 15 minutes. Therefore, there must be > problem with > >either > >> sqlplus or Networking. > >> With sqlplus, I increase arraysize from 1 to 2000. > >> With Networking, I put tcp.nodelay=yes on protocol.ora. > >> Both doesn't work. > >> > >> I try thrid party software which is writen by Pro*C to download > tables to > >flat > >> file. Its speed is more than 60M/minute. I monitor v$session_event > while > >it's > >> running.The only different is event > >> "SQL*Net message from client". In AVERAGE_WAIT and MAX_WAIT, the > >> different is huge. > >> sqlplus: > >> TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT > >> 49 0 5998 122.4 1004 > >> Pro*C: > >> TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT > >> 351 0 677 1.92 42 > >> What's the problem sqlplus or net8? > >> > >> BTW, dblink doesn't work since the two databases on isolated > network. > >> emp/imp is an option. However, I just try to find out what is > wrong > >> with sqlplus one. > >> I test 8.0.5 and 8.1.7 on solaris 2.6-2.8. > >> > >> Thanks in advance, > >> Bin > >> > >> -- > >> Please see the official ORACLE-L FAQ: http://www.orafaq.com > >> -- > >> Author: Bin Wang > >> INET: [EMAIL PROTECTED] > >> > >> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > >> San Diego, California -- Public Internet access / Mailing > Lists > >> > -------------------------------------------------------------------- > >> To REMOVE yourself from this mailing list, send an E-Mail message > >> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >> the message BODY, include a line containing: UNSUB ORACLE-L > >> (or the name of mailing list you want to be removed from). You may > >> also send the HELP command for other information (like > subscribing). > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: Tim Gorman > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > >San Diego, California -- Public Internet access / Mailing > Lists > >-------------------------------------------------------------------- > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(or the name of mailing list you want to be removed from). You may > >also send the HELP command for other information (like subscribing). > >. > > > > > __________________________________________________________________ > Get your free Australian email account at http://www.start.com.au > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ozemail > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
