It may happen that after you install or configure a new Oracle database we realize that the character set chosen during installation is not correct. What we may happen in cases like this is to delete the database and reconfigure it or worse ...But you do not. We can change the character set stopping the database, looking up strictly by changing the settings and restart the database. Howto:
- First we connect to the database
$ sqlplus sys/pwd@prod as sysdba
- We stop the database
SQL>SHUTDOWN IMMEDIATE;
- We raise strictly *
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
- Change the character map
SQL>ALTER DATABASE CHARACTER SET <new characters map>;
- Restart the database and yata
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
To verify that changes have taken effect can query the view v $ nls_parameters and check the value of nls_characterset. We know that according to changes in coding that we can lose data (if any) depending on the change.
* It is also useful to know to get up the database in restricted mode is very useful to carry out maintenance of the database that can be done faster when no user activity (such as rebuilding indexes, reduction of segments , etc).
