How to fix the error orA-29275 when exporting data

Get this error when exporting full database

———————
……
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
EXP-00056: orACLE error 29275 encountered
orA-29275: partial multibyte character
EXP-00000: Export terminated unsuccessfully
———————
it seems it can not export the Store Procedure since it get the orA-29275: partial multibyte character error.

Try to figure out what has happened.

–STEP 1: find out what’s the meaning of error orA-29275
from metalink
———————————————
orA-29275: partial multibyte character

Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.

Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. or read the partial multibyte character as RAW.
———————————————

it doesn’t tell me the whole action, but I can find out that this error must have something to do with character set.

–STEP 2: Read the export log again
————————-

Connected to: oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export the entire database …
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
…….
————————-
So you can find that export use WE8MSWIN1252 character set and AL16UTF16 NCHAR character set, but server use AL32UTF8 character set. maybe this is the root cause and charset conversion may be needed

–STEP 3: find out the database character set.
Select * FROM v$nls_parameters;
or
select * from nls_database_parameters;
or
select userenv(‘language’) from dual;
—————————
SQL> select userenv(‘language’) from dual;

USERENV(‘LANGUAGE’)
—————————————————-
AMERICAN_AMERICA.AL32UTF8
—————————

–STEP 4: set the server character to the same one with database. and export again.

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8   ==> this is the KEY
EXP ‘/ as sysdba’ full=y file=J:\dbawork\NAI3119\Synchro\SYNCHRO_FULL_081202_3.dmp log=J:\dbawork\NAI3119\Synchro\SYNCHRO_FULL_081202_3.log statistics=NONE
–log file
——————-
Connected to: oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export the entire database …
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
………
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.
———————————-
everything is great now!!

Conclusion:
I think the key point is the character set, setting the character to be the same as database character will resolve this problem.

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>