How to View and Change Oracle Server and Client Character Sets Safely

Viewing Oracle Server and Client Character Sets

To inspect the database character set, run: select * from nls_database_parameters; To view the client character set: select userenv('language') from dual; Instance‑level settings can be queried with: select * from nls_instance_parameters; Session‑level settings are obtained via: select * from nls_session_parameters; The NLS_LANG parameter controls the client environment and follows the format language_territory.client_charset, e.g., AMERICAN_AMERICA.ZHS16GBK. The third part (character set) is what actually affects data encoding.

Modifying the Server Character Set (Caution)

Changing a database’s character set after creation normally requires rebuilding the database via export/import. Oracle also provides the command: ALTER DATABASE CHARACTER SET <new_charset>; This operation updates dictionary metadata without converting existing data. It works only when the new charset is a strict superset of the old one (e.g., US7ASCII → UTF8). If not, Oracle returns an error such as:

ORA-12712: new character set must be a superset of old character set

Attempting the change in an 11g test environment with ALTER DATABASE CHARACTER SET ZHS16CGB231280; produces the above error because the new charset is not a superset.

Oracle also offers an undocumented internal option to bypass the superset check: ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8; Using this method is discouraged in production because it can corrupt the data dictionary.

Incorrect Direct Update Method (Why It Fails)

Some online guides suggest updating the props$ table directly:

update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';

update props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';

This only changes one of the twelve dictionary tables that Oracle updates when the proper ALTER DATABASE CHARACTER SET command is used, leaving the database in an inconsistent state. Therefore, the proper method should always be used.

Modifying Client Character Set on Linux

Check the current client charset with: echo $NLS_LANG Set a new client charset by exporting the variable before launching sqlplus: export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" After setting the variable, start sqlplus / as sysdba and the client will use the specified charset.

Modifying Client Character Set on Windows

On Windows, the client charset is defined in the registry under the Oracle home key, e.g.: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExx\ Modify the NLS_LANG value to the desired language and charset.

Dealing with Garbled Text

When inserting Chinese characters on Linux, the data may appear garbled in a Windows PL/SQL client if the client’s charset does not match the server’s. The issue is often caused by the Linux LANG environment variable. Setting LANG=zh_CN.GB2312 (or another appropriate locale) and ensuring the SSH terminal uses the same encoding (e.g., UTF‑8 or GB2312) resolves the display problem.

Example workflow:

Create a table and insert Chinese text.

Verify the stored bytes with SELECT name, DUMP(name) FROM table;.

If the client shows garbled characters, adjust LANG and the terminal encoding, then re‑export NLS_LANG with the matching charset.

Summary

To view Oracle character sets, query the appropriate NLS_* views. Changing the server charset should be done with ALTER DATABASE CHARACTER SET only when the new charset is a superset; otherwise, use export/import. Direct updates to props$ are unsafe. Client charset adjustments differ between Linux (environment variable) and Windows (registry). Proper locale and terminal settings prevent garbled Chinese text.