Re: &^@#%$@#%$ stinkin' oracle7 database
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 07 May 2004 15:28:41 -0600
Matthew,
How about if you send the export to a pipe and then have import read
directly from the pipe? That way, you never run into a file size issue
because you create a file.
Runs faster too, because you're pipelining as well performing only half the
I/O. Instead of: EXP (read db, write file) then IMP (read file, write db),
you'll now be doing: EXP (read db, write memory) then IMP (read memory,
write db).
Sample korn-shell script attached below. You'll want to replace the
"<user>", "<pwd>", "<sourceTNS>", and "<targetTNS>" strings as necessary.
Also, the EXP and IMP commands are currently configured to do FULL=Y; you
might want to replace with "TABLES=xyz".
Hope this helps...
-Tim
----------- Begin sample UNIX shell script -----------------
#!/bin/ksh
#=========================================================================
# File: expimp.sh
# Date: 15jan97
# Author: Tim Gorman
# Description:
# Shell script to perform a complete database dump and rebuild using
# the EXP and IMP utilities, connected via UNIX "named pipe".
#
# Modifications:
#=========================================================================
#
# Create shell variables for important filenames...
#
_ExpPar=/tmp/expimp_exp_$$.par
_ExpLog=/tmp/expimp_exp_$$.log
_ExpOut=/tmp/expimp_exp_$$.out
_ExpErr=/tmp/expimp_exp_$$.err
_ImpPar=/tmp/expimp_imp_$$.par
_ImpLog=/tmp/expimp_imp_$$.log
_ImpOut=/tmp/expimp_imp_$$.out
_ImpErr=/tmp/expimp_imp_$$.err
_Pipe=/tmp/expimp_$$.pipe
#
# Create a protected parameter file for the EXP utility...
#
echo "commit=y" > ${_ExpPar}
chmod 600 ${_Par}
echo "userid=<user>/<pwd>@<sourceTNS>" >> ${_ExpPar}
echo "file=${_Pipe}" >> ${_ExpPar}
echo "buffer=10485760" >> ${_ExpPar}
echo "compress=n" >> ${_ExpPar}
echo "log=${_ExpLog}" >> ${_ExpPar}
echo "full=y" >> ${_ExpPar}
#
# Create a protected parameter file for the IMP utility...
#
echo "commit=y" > ${_ImpPar}
chmod 600 ${_Par}
echo "userid=userid=<user>/<pwd>@<targetTNS>" >> ${_ImpPar}
echo "file=${_Pipe}" >> ${_ImpPar}
echo "buffer=10485760" >> ${_ImpPar}
echo "ignore=y" >> ${_ImpPar}
echo "log=${_ImpLog}" >> ${_ImpPar}
echo "full=y" >> ${_ImpPar}
#
# Create the UNIX "named pipe" to act as the method of
# interprocess communication...
#
mknod ${_Pipe} p
if (( $? != 0 ))
then
echo "\"mknod ${_Pipe} p\" failed; aborting..."
exit 1
fi
#
# Start the EXP utility in "background" and the IMP
# utility in "foreground". Both will complete around
# the same time...
#
exp parfile=${_ExpPar} > ${_ExpOut} 2> ${_ExpErr} &
imp parfile=${_ImpPar} > ${_ImpOut} 2> ${_ImpErr}
#
# Clean up the temporary files, as well as any files containing
# usernames and/or passwords, and then exit...
#
rm -f ${_Pipe} ${_ExpPar} ${_ImpPar}
exit 0
----------- End sample UNIX shell script -------------------
on 5/7/04 1:37 PM, Adams, Matthew (GE Consumer & Industrial) at
MATT.ADAMS@xxxxxx wrote:
> I've got a table in an Oracle7.3 database (that I'm finally getting
> to upgrade and I'm trying to figure out how to get the data from
> the old DB over to the new DB on the new server.
>
> 1) table is too big to export (even sending the export to a pipe and =
> thence
> to a compress)
> 2) table contains a long raw, so I cannot use the sqlplus COPY command
> 3) table contains a long raw, so I cannot do INSERT INTO <DBLINKED =
> TABLE> SELECT * FROM...
> 4) table contains a long raw, so dumping to a flat file and re-inserting =
> using
> sqlplus will not work.
>
> Other than writing a Pro*C program to do it myself, how can this data be =
> moved?
>
> Matt
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
