Alter Database Character Set (Optional)

Shut down the database either by using the SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL command. Take a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back. This activity may cause data corruption, as character set should be set at installation time. Complete the following statements:

  1. Connect to Linux server using ORACLE operating system user name and password.
  2. Execute the below commands to connect to the sys user:
    • sqlplus / as sysdba
    • SQL> Shutdown immediate;
    • SQL> Startup Mount;
    • SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
    • SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
    • SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
    • SQL> ALTER DATABASE OPEN;
    • SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
    • SQL> SHUTDOWN IMMEDIATE;
    • SQL> STARTUP;
  1. To set the OS user environment variable, edit the bash profile and add the NLS_LANG parameter. Follow the below commands:
    • Connect to Linux ORACLE user name and password
    • vi .bash_profile (from shell)
    • add NLS_LANG parameter at the end of batch profile 
      NLS_LANG='american_america.we8iso8859p1'; export NLS_LANG
    • now save the settings with :wq



See also