| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Import 8.1.6 to 9.2.0.1 weirdness
Please note the MetaLink doc states that this is for CONVENTIONAL PATH
ONLY.
So direct=y should not be affected.
There may also be a patch available, depending on platform and version.
There is a workaround supplied, but it looks suspiciously like something
that
will make exp slower than it already is.
ie. set ORA_OCI_NO_OPTIMIZED_FETCH=1 in the environment.
This script was run as a cursory test. It doesn't appear that setting ORA_OCI_NO_OPTIMIZED_FETCH=1 has an adverse affect.
# test export speed with workaround from doc ID 223399.1 # this is the export corruption bug. When more than 32k # rows are fetched at once, corruption can occur. This is # supposed to affect only conventional path, not direct. DATABASE=dv01
BUFFER=10485760 # 10 meg RECORDLENGTH=65535 # maximum allowed - see note 1028959.6TABLE=PDBAREP.PDBA_TAB_COLUMNS unset ORA_OCI_NO_OPTIMIZED_FETCH
echo Conventional Path export with normal optimization
time exp userid=$USERNAME/$PASSWORD \
file=conventional_opt.dmp \
file=conventional_opt.log \
buffer=$BUFFER \
compress=n \
tables=$TABLE
export ORA_OCI_NO_OPTIMIZED_FETCH=1
echo Conventional Path export with no optimization
time exp userid=$USERNAME/$PASSWORD \
file=conventional_no_opt.dmp \
file=conventional_no_opt.log \
buffer=$BUFFER \
compress=n \
tables=$TABLE
unset ORA_OCI_NO_OPTIMIZED_FETCH
echo Direct Path export
time exp userid=$USERNAME/$PASSWORD \
file=direct.dmp \
file=direct.log \
direct=y \
recordlength=$RECORDLENGTH \
compress=n \
tables=$TABLE
=============================================================
Conventional Path export with normal optimization
Export: Release 8.1.7.2.0 - Production on Fri May 16 10:29:30 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character
set
About to export specified tables via Conventional Path ...
Current user changed to PDBAREP
. . exporting table PDBA_TAB_COLUMNS 281353 rows
exported
Export terminated successfully without warnings.
real 0m6.227s user 0m5.710s sys 0m0.370s
Export: Release 8.1.7.2.0 - Production on Fri May 16 10:29:36 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character
set
About to export specified tables via Conventional Path ...
Current user changed to PDBAREP
. . exporting table PDBA_TAB_COLUMNS 281353 rows
exported
Export terminated successfully without warnings.
real 0m6.232s user 0m5.870s sys 0m0.240s
Export: Release 8.1.7.2.0 - Production on Fri May 16 10:29:43 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character
set
About to export specified tables via Direct Path ...
Current user changed to PDBAREP
. . exporting table PDBA_TAB_COLUMNS 281353 rows
exported
Export terminated successfully without warnings.
real 0m1.826s user 0m1.120s sys 0m0.930s
Rajesh.Rao_at_jpmchase.com
Sent by: root_at_fatcity.com
05/16/2003 08:36 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: Import 8.1.6 to 9.2.0.1 weirdness
Refer Note: 223399.1. A data corruption can occur if the buffer parameter has been set to a value so more than 32,768 rows will be fetched.
Effects version 8.1.7.3 and above. Has been fixed in 9.2.0.3. For other versions theres a workaround.
Regards
Raj
Prem Khanna J
<jprem_at_kssnet. To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
co.jp> cc:
Sent by: Subject: Re: Import 8.1.6 to
9.2.0.1 weirdness
root_at_fatcity.c
om
05/15/2003
11:28 PM
Please respond
to ORACLE-L
Jared,
sorry to drag away from the topic.
i read in a metalink doc that large BUFFER size during EXP may
result in corrupt DMP file.
is it no so ? is 10M (compared to 32768) not large ?
just curious to know !
Regards,
Jp.
16-5-2003 09:37:02, Jared.Still_at_radisys.com wrote:
>I don't know if this may have any bearing on the problem you are
>experiencing,
>but a 64k buffer is rather small. I like to use 10 meg on imports
eg.
>buffer=10485760
>When it is hanging, have you logged in to the target database and
checked
>to
>see what the import session is waiting on via v$session_wait?
>Jared
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: jprem_at_kssnet.co.jp 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: Rajesh.Rao_at_jpmchase.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: Jared.Still_at_radisys.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 Fri May 16 2003 - 13:33:32 CDT
 |
 |