| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Antwort: Re: Dump table to file, edit and reimport
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
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
Daniel Wisser
<daniel.wisser_at_isis-p An: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
apyrus.com> Kopie:
Gesendet von: Thema: Re: Dump table to file, edit and reimport
root_at_fatcity.com
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 <aidanwhitehall_at_fairbanks.co.uk>
> 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: AidanWhitehall_at_Fairbanks.co.uk
>
> 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: ListGuru_at_fatcity.com (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: daniel.wisser_at_isis-papyrus.com 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: ListGuru_at_fatcity.com (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: Alex.Apostolopoulos_at_secartis.com 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: ListGuru_at_fatcity.com (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).Received on Thu Feb 06 2003 - 10:33:54 CST
 |
 |