Tuesday, April 21, 2009

Oracle Tips : How to change database character set

Oracle Tips : How to change database character set

This article gives a overview of methods to change the database character set .

1. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

Parameter Value
NLS_CHARACTERSET AL32UTF8

If you want to change NLS_CHARACTERSET from AL32UTF8 to TH8TISASCII

2. Connect to user SYS

3. Update value

UPDATE PROPS$
SET VALUE$ = 'TH8TISASCII'
WHERE NAME = 'NLS_CHARACTERSET';

COMMIT;

4. Restart database

SHUTDOWN IMMEDIATE;

STARTUP;

5. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT * FROM
V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

Parameter Value
NLS_CHARACTERSET TH8TISASCII

7 comments:

  1. congratulations, you have just totally corrupted your database.
    updating props$ is the best way to kill a database.

    ReplyDelete
  2. My friend how can you change character set of database by just updating props$. You may "feel" that the character set is changed but u just updated only metadata.

    - Tushar

    ReplyDelete
  3. This will suck your Database buddy. Never try this way. :)

    ReplyDelete
  4. really it is working very fine. thank's. Because i was exp in oracle 10g and i want to imp oracle 11g r2 but when i want to imp DMP file oracle was give me character set error. and i tried this method's and it is working very fine.

    ReplyDelete
  5. how can i set NLS language with arabic ?

    ReplyDelete
  6. ITs Very Risky i see after i ran this..its not updated for all datatype.Still some of the applications packages will fail for that.
    No other option we need to create again new database or export/import affected objects.

    Please find the metalink note for more details - PLS-553 when calling or compiling pl/sql objects [ID 286964.1]

    SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET, decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'), 9,
    decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'), 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'), 8,
    decode(charsetform, 1, 'LONG', 'UNKNOWN'), 112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN from sys.col$
    where charsetform in (1,2) and type# in (1, 8, 9, 96, 112) order by CHARACTERSET,
    TYPES_USED_IN;

    CHARACTERSET TYPES_USED_IN
    ---------------------------------------- -------------
    AL16UTF16 NCHAR
    AL16UTF16 NCLOB
    AL16UTF16 NVARCHAR2
    AL32UTF8 CHAR
    AL32UTF8 CLOB
    AL32UTF8 VARCHAR2
    WE8MSWIN1252 CHAR
    WE8MSWIN1252 CLOB
    WE8MSWIN1252 LONG
    WE8MSWIN1252 VARCHAR2

    ReplyDelete