joelpatt
asked on
EXP-00041 after changing database character set.
How can I get a sucessful direct export? How do I change the user's character set to make this work?
Command run on unix server connecting to $ORACLE_SID environment variable. (all environment variables set including ORACLE_NLS=/u01/app/oracle /product/8 .1.6/ocomm on/nls/adm in/data
ORA_NLS32=/u01/app/oracle/ product/8. 1.6/ocommo n/nls/admi n/data)
exp file=myfile.dat log=myfile.log userid=batch/password full=y buffer=819200 indexes=y rows=n constraints=y compress=n direct=y
EXP-00041: Export done in server's WE8ISO8859P1, different from user's character set US7ASCII
EXP-00000: Export terminated unsuccessfully
Command run on unix server connecting to $ORACLE_SID environment variable. (all environment variables set including ORACLE_NLS=/u01/app/oracle
ORA_NLS32=/u01/app/oracle/
exp file=myfile.dat log=myfile.log userid=batch/password full=y buffer=819200 indexes=y rows=n constraints=y compress=n direct=y
EXP-00041: Export done in server's WE8ISO8859P1, different from user's character set US7ASCII
EXP-00000: Export terminated unsuccessfully
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
In u'r question u had given the export file as myfile1.dat and it want to be myfile1.dmp.Just for correction.
thanks
selva
In u'r question u had given the export file as myfile1.dat and it want to be myfile1.dmp.Just for correction.
thanks
selva
To clarify things (no offense intended), your Database characterset i suppose is WE8ISO8859P1 and not US7ASCII.
As this is not the point of discussion here, i am just adding this as a comment to the earlier posts.
This is very apparent from the error message from Oracle.
EXP-00041: Export done in server's WE8ISO8859P1, different from user's character set US7ASCII
You can verify this by logging into the database and querying as follows:
select * from v$nls_parameters
where parameter like '%CHARACTERSET'
/
This will give you both your Database characterset and National Characterset.
For the purposes of your Export with Direct=y, you need to set the NLS_LANG environment variable in the client side where you are running to the same as DB Character set.
Note that this restriction is only for Direct Exports. You will still be able to do a regular export with NLS_LANG set to US7ASCII.
Your export file also can have any name or extension doesn't have to be dmp extension.
Hope this helps
- Ramesh
ASKER
Thanks ramkb, but...
I'm not getting it guys.
exp file=myfile.dmp log=myfile.log userid=batch/password full=y buffer=819200 indexes=y rows=n constraints=y
compress=n direct=y
EXP-00041: Export done in server's WE8ISO8859P1, different from user's character set US7ASCII
EXP-00000: Export terminated unsuccessfully
@abrdev:/u01/app/oracle/jo elp> echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO885 9P1
select * from v$nls_parameters
2 where parameter like '%CHARACTERSET';
PARAMETER VALUE
-------------------------- - ---------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET WE8ISO8859P1
I'm not getting it guys.
exp file=myfile.dmp log=myfile.log userid=batch/password full=y buffer=819200 indexes=y rows=n constraints=y
compress=n direct=y
EXP-00041: Export done in server's WE8ISO8859P1, different from user's character set US7ASCII
EXP-00000: Export terminated unsuccessfully
@abrdev:/u01/app/oracle/jo
AMERICAN_AMERICA.WE8ISO885
select * from v$nls_parameters
2 where parameter like '%CHARACTERSET';
PARAMETER VALUE
--------------------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET WE8ISO8859P1
Try to run export without direct=Y , hopefully this will solve your problem
Regards
Sameer
Regards
Sameer
ASKER
exp file=myfile.dmp log=myfile.log userid=batch/bhlod3 owner=JOELP buffer=819200 indexes=y rows=y constraints=y direct=n compress=n
Yes Wadhwa, that works. However, I am not confident that it solves my problem. I still get exp-00041 when using direct=y, and we use direct=y for speed.
Is what you are saying that with this character set, that direct exports are not possible (safely)?
Because, I still would like to see an export without errors, (and and import), unless direct=y is not allowed.
Yes Wadhwa, that works. However, I am not confident that it solves my problem. I still get exp-00041 when using direct=y, and we use direct=y for speed.
Is what you are saying that with this character set, that direct exports are not possible (safely)?
Because, I still would like to see an export without errors, (and and import), unless direct=y is not allowed.
You can test with direct =Y option if you set
NLS_LANG=AMERICAN_AMERICA. US7ASCII;e xport NLS_LANG
if it still gives you error , only option is not to use direct=Y
NLS_LANG=AMERICAN_AMERICA.
if it still gives you error , only option is not to use direct=Y
ASKER
I found the problem, silly me.
I had been typing NLS_LANG=AMERICAN_AMERICA. WE8ISO8859 P1
which only set it for my current telnet session.
When I typed:
export NLS_LANG=AMERICAN_AMERICA. WE8ISO8859 P1, direct=y worked.
Thanks for all your help. I'll probably have more questions later.
I award the points to Ramesh for answering first, and also for clarifying other points.
Wadhwa answered second and mentioned the 'export' part of setting the environment variable, and followed up to try and get me going, but he exported the wrong characterset, (which by the way didn't confuse me).
I appreciate all your help but I have found that I can only award points to one person without a second fake question. If I wasn't so 'into' setting my NLS_LANG variable for 'my' session and then runing exp, I might have gotten it right away. But it's the little things that count sometimes. (It's the other session silly).
:)
I had been typing NLS_LANG=AMERICAN_AMERICA.
which only set it for my current telnet session.
When I typed:
export NLS_LANG=AMERICAN_AMERICA.
Thanks for all your help. I'll probably have more questions later.
I award the points to Ramesh for answering first, and also for clarifying other points.
Wadhwa answered second and mentioned the 'export' part of setting the environment variable, and followed up to try and get me going, but he exported the wrong characterset, (which by the way didn't confuse me).
I appreciate all your help but I have found that I can only award points to one person without a second fake question. If I wasn't so 'into' setting my NLS_LANG variable for 'my' session and then runing exp, I might have gotten it right away. But it's the little things that count sometimes. (It's the other session silly).
:)
Hi,
Sorry, i couldn't attend this earlier today to resolve your issues. But i'm glad that you resolved this issue.
Cheers,
Ramesh
If you are using Ksh , update .profie as
NLS_LANG=AMERICAN_AMERICA.
for csh update .login as
setenv NLS_LANG AMERICAN_AMERIC.US7ASCII
relogin and start the export now
Hope this solve the problem
Regards