Export Dump file Size

[pinakpatel]

Hi,

I am not able to create export dump file more than 2GB. I Have one table XXX (size 20 GB). for reorg. I tried to export the table but when it reach at 2GB file size export was terminated.

I checked on OS (AIX 5) level.

ulimit -a

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 262144
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

Is their any parameter or env. variable I need to set ..?

Help me..!!!!

[pravin]

boss u have to give the parameter FILE as file=file1.dmp,file2.dmp,file3.dmp and so on..

[thanigai]

Hi,
create a pipe and compress the dump file.
You will be all right.
Thanks.
Thanigaivasan.

[puneet]

can you give the syntax for the pipe

[gandhi]

Hi,

What is your Oracle Version. If it is 8 then the dump file size is 2 GB maximum.

Go for file=a.dmp,b.dmp,c.dmp etc...


Cheers
gandhi
OCP8-DBA.

[SANJAY_G]

Originally Posted by: gandhi
What is your Oracle Version. If it is 8 then the dump file size is 2 GB maximum.

Go for file=a.dmp,b.dmp,c.dmp etc...

Oracle doesn't have the limit of 2GB file size some of the unix OS are having a limit of 2GB.
I did export/import over 2GB on Oracle7 also, that was on Compaq Tru64

Sanjay

[jovery]

He's an example of exporting to a pipe (it's a Solaris script but you should be able to modify it for your own env.)

# set -x
#
# Check syntax of command.
#
if [ $# -eq 0 ] ; then
echo "[ExpPipe] Usage :ExpPipe ORACLE_SID EXPORT_DIRECTORY"
exit 2
fi

#
# Define funtions
#
check_logfile ( )
{
grep "Export terminated unsuccessfully" $1
return $?
}

#
# Define variables
#
NOW=`date "+%y%m%d.%H:%M:%S"`
CURRENTDIR=`dirname $0`
if [ ${CURRENTDIR} = "." ] ; then
CURRENTDIR=`pwd`
fi
EXPORT_DIR=$2
if [ $2 -eq 0 ] ; then
EXPORT_DIR=`pwd`
fi
ORG_ORACLE_SID=$ORACLE_SID
OPTIONS1="consistent=yes buffer=1000000 statistics=NONE full=yes"
OPTIONS2="buffer=1000000 statistics=NONE full=yes"
PIPENAME=/tmp/ExpPipe.${ORACLE_SID}.$$
LOGFILE=./ExpPipe_log.${ORACLE_SID}.${NOW}
EXPORTFILE=${EXPORT_DIR}/exp.${ORACLE_SID}.${NOW}

#
# Set up oracle environment
#
export ORACLE_SID=$1
ORAENV_ASK=NO
. oraenv

#
# Perform export
#
/usr/sbin/mknod $PIPENAME p
exp userid=/ file=${PIPENAME} ${OPTIONS1} log=$LOGFILE.1 1>>/dev/null 2>&1 &
dd if=${PIPENAME} 2>>/dev/null | compress -c > ${EXPORTFILE}.consistent.Z

check_logfile ${LOGFILE}.1

if [ $? -eq 0 ]
then
rm ${EXPORTFILE}.consistent
else
rm ${PIPENAME}
exit 0
fi

exp userid=/ file=${PIPENAME} ${OPTIONS2} log=$LOGFILE.2 1>>/dev/null 2>&1 &
dd if=${PIPENAME} 2>>/dev/null | compress -c > ${EXPORTFILE}.Z

rm ${PIPENAME}

#
# Reset environment
#
export ORACLE_SID=$ORG_ORACLE_SID
ORAENV_ASK=NO
. oraenv


Regards

[Mr.Hanky]

I think the 2 gig file limit is more a product of the OS, Unix can have this limitation unless you have large files enabled. This is how it works in my shop with HP/UX.

The only heads up I want to throw out there is that if you do choose to compress the .DMP file during export, you will need to know how to uncompress it during an import. If the uncompressed .DMP file is larger that 2 gigs uncompressed, you will not be able to open it via uncompress since your mount point will puke on you.

There is a way to read and use the compressed .DMP file without opening it but I am not sure how this works.
Anyone?
Do you just reverse the pipe?

MH

[pinakpatel]

I'm doing reorg. using SAP's SAPDBA tool, Oracle Ver. is 7.3
And during export of table it will not allow for file size more than 2GB.


?????

[gandhi]

Hi Patel,

Could tell us the O/S you are using with Version.



bye
gandhi
OCP8-DBA