Modifying the Oracle Character set

The newly installed database, after importing the data, found that inserting the kanji part was garbled. Because the local data is displayed normally, there is no garbled characters. Therefore, the assertion is a problem with the database character set.

1 . View the character set of the database:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/4D/D0/wKiom1RaRoLCSN_wAAHLr3mj9Nw039.jpg "title=" 1111111.png "alt=" Wkiom1rarolcsn_waahlr3mj9nw039.jpg "/>

found to be Western European code,8 bits (one byte),ISO standard 8859p1 encoding. His coding scheme is suitable for most countries in Europe.

also queried the local database encoding format, found to be ZHS16GBK encoded format.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/4D/D0/wKioL1RaRxXA4zHQAAG7DxXAbNI019.jpg "title=" 22222222222222.png "alt=" Wkiol1rarxxa4zhqaag7dxxabni019.jpg "/>

explain the character set here, The earliest supported encoding scheme for Oracel is us7ascii.

explain it again . the naming of Oracle character sets follows the following naming conventions:

<language><bit size><encoding>

namely: < language >< Number of bits >< Coding >

For example: The ZHS16GBK represents a simplified Chinese character set with GBK encoding and A (two-byte).

now that the cause of the problem has been found, here's how to modify the character set of Oracle.

2. Modifying the database character set

The database character set cannot be modified in principle after it is created, because the character set is specified when the data is created. can specify

Nls_characterset ZHS16GBK represents a character set

Nls_nchar_characterset al16utf16 National Character Set

Here are two ways to summarize:

(1) It is usually necessary to export the database data, rebuild the database, and then import the database data in a way to convert.

(2) Modifying the character set through the ALTER database CHARACTER set statement, but modifying the character set after the database is created is limited, and only the new character set is a superset of the current character set to modify the character set, for example UTF8 is a superset of US7ASCII, modifying the database character set can be used by the ALTER DB CHARACTER set UTF8. Now only method two is introduced.

First, connect with the database with the administrator user.

sql> shutdown immediate;

Databaseclosed.

Databasedismounted.

Oracleinstance shut down.

Sql> startup Mount;

Oracleinstance started.

Totalsystem Global Area 1140850688 bytes

Fixedsize 2020224 bytes

Variablesize 301993088 bytes

Databasebuffers 822083584 bytes

Redobuffers 14753792 bytes

Databasemounted.

sql> ALTER SESSION SET sql_trace=true;

Sessionaltered.

sql> ALTER SYSTEM ENABLE RESTRICTED SESSION;

Systemaltered.

sql> ALTER SYSTEM SET job_queue_processes=0;

Systemaltered.

sql> ALTER SYSTEM SET aq_tm_processes=0;

Systemaltered.

sql> ALTER DATABASE open;

Databasealtered.

sql> ALTER DATABASE CHARACTER SET zhs16gbk;

Alterdatabase CHARACTER SET ZHS16GBK

*

ERROR Atline 1:

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

Encountering this problem is inevitable, you can ignore this error, you can pass the super-set check.

Sql>alter DATABASE Character Set Internal_use ZHS16GBK;

Databasealtered.

sql>shutdown immediate;

Databaseclosed.

Databasedismounted.

Oracleinstance shut down.

Sql> Startup

Oracleinstance started.

Totalsystem Global Area 1140850688 bytes

Fixedsize 2020224 bytes

Variablesize 301993088 bytes

Databasebuffers 822083584 bytes

Redobuffers 14753792 bytes

Databasemounted.

databaseopened.

3. Verify after modification

(1) Pre-modification status:

Sql> select * from V$nls_parameters; PARAMETER VALUE --------------------------------------------------------------------------------------------------------------- ----------------- Nls_language Simplified Chinese Nls_territory China Nls_currency￥ Nls_iso_currency China Nls_numeric_characters., Nls_calendar Gregorian Nls_date_format DD-MON-RR Nls_date_language Simplified Chinese Nls_characterset ZHS16GBK Nls_sort BINARY Nls_time_format HH.MI. Ssxffam Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM Nls_time_tz_format HH.MI. Ssxff AM TZR Nls_timestamp_tz_format DD-MON-RRHH.MI. Ssxff AM TZR nls_dual_currency ￥ Nls_nchar_characterset AL16UTF16 Nls_comp BINARY Nls_length_semantics BYTE NLS_NCHAR_CONV_EXCP FALSE rows Selecte

4, Delete the original data, re-import new data

After modifying the character set, the data inside is still garbled, so you need to re-import the data.

This article is from the "Zsls" blog, make sure to keep this source http://qqianwan.blog.51cto.com/8015812/1572750

Modifying the Oracle Character set