Re: Character set is wrong after exp/imp operation (?'s show as #'s)

On 21 Dec 2001 03:20:35 -0800, davidg_at_bluewave.com (David Goodall) wrote:

>"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<u24lka47dgtk76_at_corp.supernews.com>...
>> "David Goodall" <davidg_at_bluewave.com> wrote in message
>> news:2a9f8213.0112201015.693e70d8_at_posting.google.com...
>> > Hi,
>> >
>> > I am trying to export an Oracle database from a Linux box to a WinNT
>> > box (only a temporary measure ;-) by the following method:
>> >
>> > ./exp system/password full=Y file=filename.dmp
>> >
>> > imp userid=system/password_at_dbname full=y file=filename.dmp
>> >
>> > The import is into a newly created database, with the characterset
>> > WE8ISO8859P1.
>> >
>> > Everything works fine except that all instances of "£" are replaced
>> > with "#"
>> >
>> > I then tried again with a fresh database, using the WE8DEC
>> > characterset, but got the same result.
>> >
>> > Any help would be much appreciated.
>> >
>> > Cheers,
>> > David.
>>
>> You need to set NLS_LANG on both sides as
>> "<your language>_<your territory>.<your characterset>"
>> of course the source database must have the correct characterset.
>> If you look at exp's log output you should notice characterset conversion
>> occurs when not setting NLS_LANG
>>
>> Hth
>
>
>Thanks for that, you are absolutely correct. Having now set the
>NLS_LANG properly on both sides, a glance at the exp log shows the
>following (as you predicted):
>
>Export done in WE8ISO8859P1 character set and US7ASCII NCHAR character
>set
>server uses US7ASCII character set (possible charset conversion)
>
>Which, I think, shows that the source database wasn't set up with the
>correct characterset? Is there anything I can do to change this?
>
>Thanks for your time, and for putting up with a newbie...
>David.

8.0 and higher: alter database character set or alter database national characterset
The database must be mounted in *exclusive* mode : the job_queue_processes and advanced_queue_processes parameter must be set to 0. IIRC correctly I posted the Metalink note on this subject to this group already (and archives are kept at http://groups.google.com )
7.3 and before: full database export, recreate the database, full database import.
Do not believe people who tell you you can update sys.props$ or mangle the first byte of the export.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Dec 21 2001 - 09:12:58 CST