Home » SQL & PL/SQL » SQL & PL/SQL » dumping data to text file(urgent)
dumping data to text file(urgent) [message #358439] Tue, 11 November 2008 05:59 Go to next message
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
Junior Member
I have a table in oracle server whose size is around 15 gb. I want to dump this table to text file. I tried using spool. But the result is not good because the resulting text file is found to be only of 2 gb and also the field delimination and the row delimination was not properly set.
so is there any other options to dump data into text file with proper field delimination like "|" or tab and "\n" as row terminator.

Re: dumping data to text file(urgent) [message #358440 is a reply to message #358439] Tue, 11 November 2008 06:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If it's urgent, why do you post here and don't look in the documentation or the help?

SQL> help set

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session, for example:
     -   display width for data
     -   turn on HTML formatting
     -   enabling or disabling printing of column headings
     -   number of lines per page

 In iSQL*Plus, you can also use the System Variables screen to set
 system variables.

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                  *NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n}
   AUTORECOVERY {ON|OFF}                    PAGES[IZE] {24|n}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}        *PAU[SE] {OFF|ON|text}
     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED] |
   BLO[CKTERMINATOR] {.|c}                    EA[CH]|OFF}
   CMDS[EP] {;|c|OFF|ON}                    RECSEPCHAR {_|c}
   COLSEP {_|text}                          SERVEROUT[PUT] {OFF|ON}
   COM[PATIBILITY] {V7|V8|NATIVE}             [SIZE n] [FOR[MAT]
   CON[CAT] {.|c|OFF|ON}                      {WRA[PPED] |
   COPYC[OMMIT] {0|n}                         WOR[D_WRAPPED] |
   COPYTYPECHECK {OFF|ON}                     TRU[NCATED]}]
   DEF[INE] {&|c|OFF|ON}                   *SHIFT[INOUT] {VIS[IBLE] |
   DESCRIBE [DEPTH {1|n|ALL}]                 INV[ISIBLE]}
     [LINENUM {ON|OFF}] [INDENT {ON|OFF}]  *SHOW[MODE] {OFF|ON}
   ECHO {OFF|ON}                           *SQLBL[ANKLINES] {ON|OFF}
  *EDITF[ILE] file_name[.ext]               SQLC[ASE] {MIX[ED] |
   EMB[EDDED] {OFF|ON}                        LO[WER] | UP[PER]}
   ESC[APE] {\|c|OFF|ON}                   *SQLCO[NTINUE] {> | text}
   FEED[BACK] {6|n|OFF|ON}                 *SQLN[UMBER] {OFF|ON}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
  *FLU[SH] {OFF|ON}                        *SQLPRE[FIX] {#|c}
   HEA[DING] {OFF|ON}                      *SQLP[ROMPT] {SQL>|text}
   HEADS[EP] {||c|OFF|ON}                   SQLT[ERMINATOR]
   INSTANCE [instance_path|LOCAL]             {;|c|OFF|ON}
   LIN[ESIZE] {80|n} ({150|n} iSQL*Plus)   *SUF[FIX] {SQL|text}
   LOBOF[FSET] {n|1}                        TAB {OFF|ON}
   LOGSOURCE [pathname]                     TERM[OUT] {OFF|ON}
   LONG {80|n}                              TI[ME] {OFF|ON}
   LONGC[HUNKSIZE] {80|n}                   TIMI[NG] {OFF|ON}
   MARK[UP] HTML [ON|OFF]                   TRIM[OUT] {OFF|ON}
     [HEAD text] [BODY text] [TABLE text]   TRIMS[POOL] {ON|OFF}
     [ENTMAP {ON|OFF}]                      UND[ERLINE] {-|c|ON|OFF}
     [SPOOL {ON|OFF}]                       VER[IFY] {OFF|ON}
     [PRE[FORMAT] {ON|OFF}]                 WRA[P] {OFF|ON}

 An asterisk (*) indicates the SET option is not supported in iSQL*Plus.


SQL>


Look at the COLSEP and RECSEP parameter.

Also, the 2GB limit is most likely a limit of your filesystem, but you didn't even give us the OS and Database version, so we have no way of knowing.
Re: dumping data to text file(urgent) [message #358445 is a reply to message #358439] Tue, 11 November 2008 06:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link for more information.

http://asktom.oracle.com/tkyte/flat/

May I know why you have deemed your request as URGENT?

Regards

Raj
dumping data to text file(urgent) [message #358448 is a reply to message #358439] Tue, 11 November 2008 06:58 Go to previous messageGo to next message
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
Junior Member
it is urgent because i have the deadline constraint to finish the work.
Re: dumping data to text file(urgent) [message #358455 is a reply to message #358448] Tue, 11 November 2008 07:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
For you it may be urgent but not for us. It's not a good practice to use keywords like urgent, asap etc. on a public forum. It attracts wrong attention. So could you please kindly avoid using these words in the future.

Regards

Raj
Re: dumping data to text file(urgent) [message #358571 is a reply to message #358455] Tue, 11 November 2008 21:20 Go to previous message
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
Junior Member
ok, this is the first time i have posted into this forum...i will take care of these things onwards...thanks for the help.
regards,
sameer
Previous Topic: Sql Queries Performance Problem
Next Topic: unusable index (merged)
Goto Forum:
  


Current Time: Sun Dec 11 02:40:54 CST 2016

Total time taken to generate the page: 0.18857 seconds