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
Posted by at
Labels: , , ,

7 comments:

  1. PolossatikJuly 29, 2009 at 3:49 AM

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

    ReplyDelete
  2. Tushar ThakkerFebruary 8, 2010 at 10:09 PM

    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. UnknownNovember 22, 2011 at 2:27 AM

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

    ReplyDelete
  4. karthikeyan pandianMarch 20, 2012 at 11:22 PM

    now does this work or not ?

    ReplyDelete
  5. canes57April 17, 2012 at 5:43 AM

    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
  6. som3a 3lla toolDecember 6, 2012 at 2:59 AM

    how can i set NLS language with arabic ?

    ReplyDelete
  7. GayathriApril 1, 2013 at 5:03 AM

    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

Subscribe to: Post Comments (Atom)