How to view and modify character sets on the Oracle Database Server

Oracle DatabaseView and modify the serverCharacter SetThe method is described in this article. Let's take a look at this part.

A. oracle server Character Set Query

Select userenv ('language') from dual

The NLS_CHARACTERSET is the server-side character set.

NLS_LANGUAGE is the server-side character display form

B. query the character set of the oracle client

$ Echo $ NLS_LANG

If the data you select is garbled, configure the character set of the client to be the same as that of the linux operating system. If garbled characters exist, the data in the database may be faulty, or the configuration of the oracle server may be faulty.

C. server-side character set Modification

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

 
 

  
  
SQL> conn /as sysdba  
  
  
Connected.  
  
  
SQL> shutdown immediate;  
  
  
Database closed.  
  
  
Database dismounted.  
  
  
ORACLE instance shut down.  
  
  
SQL> startup mount  
  
  
ORACLE instance started.  
  
  
Total System Global Area 236000356 bytes  
  
  
Fixed Size                   451684 bytes  
  
  
Variable Size             201326592 bytes  
  
  
Database Buffers           33554432 bytes  
  
  
Redo Buffers                 667648 bytes  
  
  
Database mounted.  
  
  
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;  
  
  
System altered.  
  
  
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;  
  
  
System altered.  
  
  
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;  
  
  
System altered.  
  
  
SQL> alter database open;  
  
  
Database altered.  
  
  
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:

 
 

  
  
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
  
  
Database altered.
  
  
SQL> select * from v $ nls_parameters;
  
  
Omitted
  
  
19 rows selected.
 
 

Restart check whether the change is complete:

 
 

  
  
SQL> shutdown immediate;
  
  
Database closed.
  
  
Database dismounted.
  
  
ORACLE instance shut down.
  
  
SQL> startup
  
  
ORACLE instance started.
  
  
Total System Global Area 236000356 bytes
  
  
Fixed Size 451684 bytes
  
  
Variable Size 201326592 bytes
  
  
Database Buffers 33554432 bytes
  
  
Redo Buffers 667648 bytes
  
  
Database mounted.
  
  
Database opened.
  
  
SQL> select * from v $ nls_parameters;
  
  
Omitted
  
  
19 rows selected.
 
 

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. Everyone should be very careful when applied to the product environment. no one except you will be responsible for the consequences.

Conclusion ):

For DBA, there is a very important principle: Do not put your database in danger!

This requires us to back up the database before performing any operations that may change the database structure. Many DBAs do not have backup operations and have learned a lot.

D. client-side character set Modification

In. bash_profile under the/home/oracle and/root User Directories

Add or modify the export NLS_LANG = "AMERICAN_AMERICA.UTF8" Statement

Close the current ssh window.

Note: The NLS_LANG variable must be correctly configured; otherwise, sqlplus will become invalid.

This article introduces how to view and modify character sets on the server in the Oracle database. I hope you will get some benefits!