Oracle view encoding and modify encoding

Oracle view encoding and modify encoding

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 that represents the database.


Oracle Client-side encoding


Sql> SELECT * from nls_instance_parameters where parameter= ' nls_language ';


PARAMETER
--------------------
VALUE
--------------------
Nls_language
Simplified Chinese


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


SELECT * from Nls_session_parameters, which originates from V$nls_parameters, represents the session's own settings, which may be the session's environment variable or alter session completion, if the session does not have a special setting, will be associated with the NLS _instance_parameters consistent.


And then, how to modify the Oracle character set:


Currently my database environment character set is Al32utf8, then change it to ZHS16GBK


1. First log in as Sysdba conn/as SYSDBA


2. Close the database shutdown immediate;


3. Use mount to hit the database, 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;


5. Start the database


ALTER DATABASE open;


6. Modifying the character Set


ALTER DATABASE CHARACTER SET ZHS16GBK;


This may cause an error, prompting our character set: The new character set must be a superset of the old character set, and we can skip over the superset check to make the change:


ALTER DATABASE Character Set Internal_use ZHS16GBK;


This statement is available, and the help provided by Ternal_use will allow Oracle to bypass the validation of subsets and superset, which is exactly the same as the one inside the statement above.


7. Shutdown, restart


Sql>shutdown immediate;
Sql> Startup


Of course, the character set is best not to easily modify, because this will have a direct impact on the data of the database, if it is a production environment, can cause unpredictable loss.

Oracle view encoding and change encoding (RPM)