Viewing the coding of oracle database and the method of modifying the coding format

First look at the encoding of the oracle database

SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

PARAMETER
--------------------
VALUE
--------------------
NLS_CHARACTERSET
AL32UTF8

This is derived from props $, which is the character set representing the database.

oracle Client Encoding

SQL> select * from nls_instance_parameters where parameter='NLS_LANGUAGE';

PARAMETER
--------------------
VALUE
--------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE

It comes from v $parameter and represents the setting of the client's character set, which may be a parameter file, environment variable or registry session character set environment

select * from nls_session_parameters, which comes from v $nls_parameters, indicates the session's own settings, which may be the session's environment variable or alter session completion. If the session has no special settings, it will be related to nls_instance_parameters1.

Let's talk about how to modify the character set of oracle:

The current character set for my database environment is AL32UTF8, so change it to ZHS16GBK

1. First log in as sysdba conn/as sysdba

2. Close the database shutdown immediate;

3. Call the database as mount, startup mount

4. Set session

SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

Step 5 Start the database

alter database open;

6. Modify the character set

ALTER DATABASE CHARACTER SET ZHS16GBK;

This may report an error, prompting our character set that 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:

ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

This statement will do, and the help provided by TERNAL_USE will enable oracle to bypass subset and superset validation, which is exactly the same as the above statement when operating internally.

7. Shut down, restart

SQL > shutdown immediate;
SQL > startup

Of course, it is best not to modify the character set easily, because it will have a direct impact on the data of the database, and if it is a production environment, it may cause incalculable losses.