How to convert character set in oracle

In Oracle, you can use the "ALTER DATABASE" character set statement to convert the character set. The syntax format is "ALTER DATABASE [db_name] CHARACTER SET new_character_set;".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Oracle-Character Set Conversion

Character set is to assign different values ​​​​to a specific set of symbols according to a certain character encoding scheme. Encoded collection.

During daily use, the character sets of the server and the client must be consistent.

--服务端
select * from nls_database_parameters;
--客户端
SELECT USERENV('LANGUAGE') FROM DUAL;

The most commonly used database character set adjustments are the ALTER command and complete export and import.

First, the ALTER DATABASE character set statement is the fastest way to migrate character sets, but it can only be used in special circumstances.

ALTER DATABASE character set statement does not perform any data conversion, so it can be used if and only if the new character set is a strict superset of the current character set.

--ALTER DATABASE字符集语句的语法如下,db_name为可选项:
ALTER DATABASE [db_name] CHARACTER SET new_character_set;

The second

data pump (EXPDP/IMPDP) method is more expensive, time-consuming and takes up a lot of resources, and needs to scan the data during migration , confirm whether data truncation and character set conversion errors occur during migration.

Database character set migration has two stages: data scanning and data conversion.

Before changing the database character set, use the character set scanning tool to determine the amount of work required before changing the database character set.

Use Data Pump to import and export script examples. The script needs to be executed on the database server:

--查看管理员目录 确认导出文件位置路径 
SELECT * FROM dba_directories;
--expdp导出 带有条件
expdp 用户/密码@服务名  directory=DATA_PUMP_DIR dumpfile=数据文件文件名.dmp logfile=日志文件文件名.log tables=导出表名 query='" where lsh in (select lsh from table_1 where code !='1' and flag='1')"'
CONTENT选项可选导出结构或数据 导出格式默认为.dmp--impdp导入 导入时重命名 存在表则增加数据 主键重复数据忽略 导入时也可以限定导入时所属表空间
impdp 用户/密码@服务名  directory=DATA_PUMP_DIR dumpfile=数据文件文件名.dmp REMAP_TABLE =导出用户名.导出表名:导入表名 TABLE_EXISTS_ACTION=append data_options=skip_constraint_errors

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to convert character set in oracle. For more information, please follow other related articles on the PHP Chinese website!