oracle to modify the oracle server and client character sets after viewing the character set

1. oracle server side character set query

select userenv ( 'language' )  from dual;

server Character Set Modification:

Start the database in RESTRICTED mode to make character set changes:

SQL>conn /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 ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK  ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Prompt our character set: The new character set must be a superset of the old character set. At this time, we can skip the superset check and make changes:

SQL>ALTER DATABASE character set INTERNAL_USE ZHS16GBK;   
SQL>select * from v$nls_parameters;  

Restart to check whether the change is complete:

SQL>shutdown immediate;
SQL>startup 
SQL>select * from v$nls_parameters;

We see that this process is exactly the same as the internal process of the previous ALTER DATABASE CHARACTER SET operation, which means that the help provided by INTERNAL_USE is to make the Oracle database bypass the validation of subsets and supersets

This 1 method is useful in some aspects, such as testing; You should be careful when applying it to the product environment, and there may be some unexpected problems.

2. oracle client character set modification

$echo$NLS_LANG

client Character Set Modification:

Add or modify the export NLS_LANG= "AMERICAN_AMERICA. UTF8" statement in. bash_profile under /home/oracle and/root user directories

Close the current ssh window.

Note: NLS_LANG Variable 1 must be configured correctly or sqlplus will fail.

3. Modify the database character set to UTF-8

1. Log in as DBA

2. Execute the conversion statement:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;

Note: If there are no large objects, there is no effect on language conversion during use!

ORA-12717: CANNOT ALTER DATABASE NATIONAL CHARACTER SET WHEN NCLOBDATAEXISTS may appear, the solution to this problem

Using the INTERNAL_USE keyword to modify the locale

ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;