oracle 转换数据库编码 AL32UTF8 到 ZHS16GBK
把通过数据泵的方式导出备份文件恢复到 Oracle 时，报错：
|
1 2 3 |
ORA-02374: conversion error loading table "TSTMD"."STK" ORA-12899: value too large for column B_NAME (actual: 55, maximum: 50) ORA-02372: data for row: B_NAME : 0X'3135BDADCBD5C6F4B6ABC5A9B4E5C9CCD2B5D2F8D0D0B9C9B7' |
提示字段的值超长，源库能够存储的数据到了恢复库不能存储，起初怀疑 impdp 参数不对导致的问题，但是这些参数是一直使用的，之前有执行成功的例子，只是这次的数据库是新装的。问题的差异可能出现在数据，通过比较发现数据的编码不一致，源库是 GBK 编码，新库是 UTF8 编码，UTF8 中文占 3 个字节，GBK 中文使用 2 个字节编码，这样带有中文的字段长度可能会超过字段定义的最大长度。，解决的办法是把恢复库的编码从 AL32UTF8 改为 ZHS16GBK。
查看目标 Oracle 数据库的字符集并格式化
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SQL> column VALUE format a30; SQL> column PARAMETER format a30; SQL> select * from V$NLS_PARAMETERS; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET AL32UTF8 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM PARAMETER VALUE ------------------------------ ------------------------------ 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-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 19 rows selected. |
用上面的方式分别查看生产库和测试库的字符集，可以看到测试库是NLS_CHARACTERSET AL32UTF8生产库是NLS_CHARACTERSET ZHS16GBK
修改字符集一般来说是不推荐的，最好的方式是重新装数据库并选择正确的字符集，特别是已有数据的库，显然生产库既有数据又不能重装。
我们只能对测试数据库下手，试试能否修改字符集。
1. 关闭数据库并重新启动到 MOUNT 状态
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 2.0110E+10 bytes Fixed Size 2261888 bytes Variable Size 3422555264 bytes Database Buffers 1.6643E+10 bytes Redo Buffers 41762816 bytes Database mounted. |
2. 更改编码的过程中防止其他用户登陆操作（重新打开使用disable参数）
|
1 2 3 |
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; System altered. |
3. 禁止数据库 job 自动执行
|
1 2 3 |
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; System altered. |
4. 禁用队列监控（恢复参数 reset）
|
1 2 3 |
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; System altered. |
5. 启动数据库并修改编码
|
1 2 3 4 5 6 7 8 9 |
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 |
6.强制不检查字符集
|
1 2 3 |
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; Database altered. |
7.重启数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2.0110E+10 bytes Fixed Size 2261888 bytes Variable Size 3422555264 bytes Database Buffers 1.6643E+10 bytes Redo Buffers 41762816 bytes Database mounted. SQL> ALTER DATABASE OPEN; Database altered. SQL> exit; |
8. 重新执行导入命令 impdp 成功。
最后，重启数据库后需要恢复上面禁用的参数。
|
1 2 3 |
ALTER SYSTEM DISABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1000; ALTER SYSTEM RESET AQ_TM_PROCESSES; |
快速查看数据库字符集和国家字符集，国际字符集多为 UTF8，一般是 AL16UTF16
|
1 |
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); |