1.Oracle server Character Set Query
Copy codeThe Code is as follows:
Select userenv ('language') from dual;
Server Character Set Modification:
Start the database to RESTRICTED mode and make character set changes:
Copy codeThe Code is as follows:
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
Note: The new character set must be a superset of the old one. In this case, we can skip the superset check and make changes:
Copy codeThe Code is as follows:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
SQL> select * from v $ nls_parameters;
Restart check whether the change is complete:
Copy codeThe Code is as follows:
SQL> shutdown immediate;
SQL> startup
SQL> select * from v $ nls_parameters;
We can see that this process is exactly the same as the internal process of the alter database character set operation. That is to say, the help provided by INTERNAL_USE is that the Oracle DATABASE bypasses the validation of the subset and superset.
This method is useful in some aspects, such as testing. when applied to the product environment, everyone should be careful and there may be some unexpected problems.
2.Oracle client Character Set Modification
Copy codeThe Code is as follows:
$ Echo $ NLS_LANG
Client Character Set modification:
Add or modify the export NLS_LANG = "AMERICAN_AMERICA.UTF8" statement in. bash_profile under the/home/oracle and/root User Directories.
Close the current ssh window.
Note: The NLS_LANG variable must be correctly configured; otherwise, sqlplus will become invalid.
3. Modify database character set to UTF-8
1. Log On As A DBA
2. Execute the conversion statement:
Copy codeThe Code is as follows:
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, language conversion is not affected during use!
Possible ORA-12717: cannot alter database national character set when nclobdataexists, work und
Use the INTERNAL_USE keyword to modify region settings
Copy codeThe Code is as follows:
Alter database national character set INTERNAL_USE UTF8;