PDB SOLUTIONS

CHANGING DATABSAE CHARACTER SET FROM AL32UTF8 TO WE8MSWIN1252

1. Check current database character set.

SQL> set line 1000

SQL> set pages 1000

SQL> SELECT * FROM V$NLS_PARAMETERS;

PARAMETER VALUE CON_ID

----------------------------------------------------------------

NLS_LANGUAGE AMERICAN 1

NLS_TERRITORY AMERICA 1

NLS_CURRENCY $ 1

NLS_ISO_CURRENCY AMERICA 1

NLS_NUMERIC_CHARACTERS ., 1

NLS_CALENDAR GREGORIAN 1

NLS_DATE_FORMAT DD-MON-RR 1

NLS_DATE_LANGUAGE AMERICAN 1

NLS_CHARACTERSET AL32UTF8 1

NLS_SORT BINARY 1

NLS_TIME_FORMAT HH.MI.SSXFF AM 1

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 1

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 1

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 1

NLS_DUAL_CURRENCY $ 1

NLS_NCHAR_CHARACTERSET AL16UTF16 1

NLS_COMP BINARY 1

NLS_LENGTH_SEMANTICS BYTE 1

NLS_NCHAR_CONV_EXCP FALSE 1

19 rows selected.

2. For changing character set we need to bring database to restriction mode so firstly we shut down the database.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Startup the database in mount state and change to restricted mode.

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

4. Open the database in restricted mode and change the character set.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE = MEMORY;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE = MEMORY;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET internal_use WE8MSWIN1252;

Database altered.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3204445920 bytes

Fixed Size 9139936 bytes

Variable Size 671088640 bytes

Database Buffers 2516582400 bytes

Redo Buffers 7634944 bytes

Database mounted.

Database opened.

5. Check the character set