Changing the Oracle Database Characterset

Hi,
This is how you can change the character set of your Oracle DB.

Checking the actual values:

SQL> set lin 1000
SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE                                                                CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
........
NLS_DATE_LANGUAGE                                                AMERICAN                                                                  0
NLS_CHARACTERSET                                                 WE8MSWIN1252                                                              0
.........
19 rows selected.

We want to change NLS_CHARACTERSET to WE8ISO8859P1 which is now WE8MSWIN1252.

SQL> alter database character set WE8ISO8859P1;
alter database character set WE8ISO8859P1
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL> alter database character set INTERNAL_USE WE8ISO8859P1;
alter database character set INTERNAL_USE WE8ISO8859P1
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size                 15847856 bytes
Variable Size            7482638336 bytes
Database Buffers         5368709120 bytes
Redo Buffers               17702912 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER DATABASE OPEN;


Database altered.

SQL> SQL> ALTER DATABASE CHARACTER SET internal_use WE8ISO8859P1;

Database altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size                 15847856 bytes
Variable Size            7482638336 bytes
Database Buffers         5368709120 bytes
Redo Buffers               17702912 bytes
Database mounted.
Database opened.

I checked again and:

SQL> select * from v$nls_parameters where PARAMETER='NLS_CHARACTERSET';

PARAMETER                                                        VALUE                                                                CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_CHARACTERSET                                                 WE8ISO8859P1                                                              0

SQL>

Leave Comment

Your email address will not be published. Required fields are marked *