To modify the character set of oracle server and client after checking the character set

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.