hi aidan, the following script will create .ctl and .dat files for each table of OWNER. These files can then be used with sqlldr. HTH alex #!/bin/ksh ############################################################################### # # Desc: create CTL and DAT files for sqlldr usage # ############################################################################### #set -x USR=user/password echo "set feedback off set pagesize 0 select table_name from all_tables where owner like 'OWNER';" | sqlplus -s $USR | while read TABL do STR1="nothing" STR="nothing" COLUM="nothing" TYPE="nothing" echo "desc $TABL" | sqlplus -s $USR | awk '{print $1 " " $2}' | tail +3 | grep "[A-Z]" | while read COLUM TYPE do if [ "$STR" = "nothing" ] then STR=$COLUM STR1=$COLUM else if [ "$TYPE" = "DATE" ] then STR="$STR || '|' || to_char($COLUM,'DD-MON-YY:HH24:MM:SS')" STR1="$STR1,$COLUM DATE 'DD-MON-YY:HH24:MM:SS'" else STR="$STR || '|' || $COLUM" STR1="$STR1,$COLUM" fi echo $STR > STR echo $STR1 > STR1 fi done echo "set feedback off set pagesize 0 set linesize 3000 select `cat STR`|| '|' from $TABL;" | sqlplus -s $USR >> $TABL.dat echo "LOAD DATA" > $TABL.ctl echo "INFILE '$TABL'" >> $TABL.ctl echo "INTO TABLE $TABL" >> $TABL.ctl echo "FIELDS TERMINATED BY '|'" >> $TABL.ctl echo "(`cat STR1`)" >> $TABL.ctl done # END OF SCRIPT Daniel Wisser <daniel.wisser@isis-p An: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> apyrus.com> Kopie: Gesendet von: Thema: Re: Dump table to file, edit and reimport [EMAIL PROTECTED] 06.02.2003 16:28 Bitte antworten an ORACLE-L hi! unload and load work like that SQL> exp myuser/********@mydb file=C:\temp\mydump.dmp tables=(mytable) SQL> imp myuser/********@mydb file=C:\temp\mydump.dmp tables=(mytable) but i don't think you will have a good time editing the file. if you want a | delimited file, you should use spool and gerenate the file you need. daniel Aidan Whitehall wrote: > > In Sybase's ASA there was an "unload" command which wrote to a text file > the SQL to recreate a table, along with all it's data which running the > SQL then imported. This allowed you to very easily dump a table, edit > it's structure and suck the data back in. > > The closest thing I've found in Oracle is right-clicking on a table | > Data Management | Export. However, we're not running the Oracle > Management Server (just because we don't know how to set that up yet), > so this functionality isn't available. > > Barring installing OMS, is there a quick and dirty way to do this? > > And, if the answer is "no", is OMS easy to set up? Thanks! > > -- > Aidan Whitehall <[EMAIL PROTECTED]> > Macromedia ColdFusion Developer > Fairbanks Environmental Ltd +44 (0)1695 51775 > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Aidan Whitehall > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > 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.net -- Author: Daniel Wisser INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).