Solved

EXP-00041 after changing database character set.

Posted on 2001-08-02
10
1,778 Views
Last Modified: 2007-11-27
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/ocommon/nls/admin/data
ORA_NLS32=/u01/app/oracle/product/8.1.6/ocommon/nls/admin/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
0
Comment
Question by:joelpatt
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 3

Accepted Solution

by:
ramkb earned 50 total points
ID: 6346545

jeolpatt,

Check for NLS_LANG.  This should be the same as DB Character set while doing export.  In your case for eg.
AMERICAN_AMERICA.WE8ISO8859P1.

Hope this helps!
- Ramesh
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6347043
Ypur database is of US7ASCII so set NLS_LANG to that

If you are using  Ksh , update .profie  as

NLS_LANG=AMERICAN_AMERICA.US7ASCII;export NLS_LANG

for csh update .login as

setenv NLS_LANG AMERICAN_AMERIC.US7ASCII

relogin and start the export now

Hope this solve the problem

Regards
0
 
LVL 1

Expert Comment

by:selvamsm
ID: 6348402
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
0
 
LVL 3

Expert Comment

by:ramkb
ID: 6349210

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
0
 
LVL 1

Author Comment

by:joelpatt
ID: 6355508
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/joelp> echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1                    

select * from v$nls_parameters
  2  where parameter like '%CHARACTERSET';

PARAMETER                   VALUE
--------------------------- ---------------
NLS_CHARACTERSET            WE8ISO8859P1
NLS_NCHAR_CHARACTERSET      WE8ISO8859P1

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:Wadhwa
ID: 6356104
Try to run export without direct=Y , hopefully this will solve your problem

Regards
Sameer
0
 
LVL 1

Author Comment

by:joelpatt
ID: 6356504
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.
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6356523
You can test with direct =Y option if you set
NLS_LANG=AMERICAN_AMERICA.US7ASCII;export NLS_LANG

if it  still gives you error , only option is not to use direct=Y


0
 
LVL 1

Author Comment

by:joelpatt
ID: 6356588
I found the problem, silly me.
I had been typing NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
which only set it for my current telnet session.

When I typed:
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1, 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).

:)
0
 
LVL 3

Expert Comment

by:ramkb
ID: 6357156

Hi,

Sorry, i couldn't attend this earlier today to resolve your issues.  But i'm glad that you resolved this issue.

Cheers,
Ramesh
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle RMAN Database Restore 5 51
query in Oracle forms Builder 2 41
PL/SQL Display based on value 4 17
use lov values 2 24
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now