Get & set NLS_LANG_XXX from / to Oracle DB

Posted: April 13, 2009 in HowTo, Oracle, Oracle Database, Oracle9i, Tips

1. To get currently used character set, issue:

SELECT * FROM nls_database_parameters WHERE parameter LIKE ‘%CHARACTERSET%’.

nls_database_parameters is a view.

2. Check and update, for example, to set nls_characterset = UTF8,nls_nchar_characterset = UTF8, use table props$:

UPDATE props$ SET value$ = ‘UTF8’ WHERE NAME LIKE ‘%CHARACTERSET%;

commit;

There you go…

Minh Tran

Comments
  1. gunther vermeir says:

    Kindly do not do this very wrong update. seen Note 225912.1 on metalink
    I quote: “There are still “dba’s” out there who try to change the NLS_CHARACTERSET or NLS_NCHAR_CHARACTERSET by updating props$ . This is NOT supported and WILL corrupt your database. This is one of the best way’s to destroy a complete dataset. Oracle Support will TRY to help you out of this but Oracle will NOT warrant that the data can be recoverd or recovered data is correct. You WILL be asked to do a FULL export and a complete rebuild of the database.

    Please, do NOT update props$.”

  2. oldlight says:

    Thank you Gunther,

    Actually, my DB was not destroyed, since I have a full-backup. It was problem of installation times.
    Yes, I confirmed that updating prop$ didn’t help you out in some situations…

    ^^

    Bests

    Minh Tran

  3. Hi Minh Tran,

    Using this tip, I have updated my database and present it is running well. Not getting any problem. Thanks a great tip. Keep it buddy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s