oracle字符的sc,Oracle数据库字符集ZHS16GBK转AL32UTF8

CONTENT

该选项用于指定要导出的内容.默认值为ALL

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

当设置 CONTENT为ALL时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义

导入对象定义：impdp system/123456@cams DIRECTORY=dpdata1 DUMPFILE=bce.dmp LOGFILE=import_bceTable.log SCHEMAS=cams_bceCONTENT=METADATA_ONLY

步骤4：

使用csscan辅助字符集转换。如果要确保数据的完整性，应该使用csscan扫描数据库，找出所有不兼容的字符，然后通过编写相应的脚本及代码，在转换之后进行更新，确保数据的正确性。

在使用csscan之前，需要以sys用户身份创建相应的数据字典对象：

[oracle@scdb2 ~]$ export ORACLE_SID=bce

[oracle@scdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 23 13:32:46 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

bce

SQL> @?/rdbms/admin/csminst.sql

User created.

Grant succeeded.

……

这个脚本创建相应用户(csmig)及数据字典对象，扫描信息会记录在相应的数据字典里。

[oracle@scdb2 ~]$ csscan help=y

csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

检查metalink，在文档ID 742070.1中提到，导致这个问题的原因是没有正确的设置LD_LIBRARY_PATH环境变量，将$ORACLE_HOME/lib添加到这个环境变量中可以避免错误的产生

[oracle@scdb2 lib]$ pwd

/oradata/oracle/product/11.2.0/db_1/lib

[oracle@scdb2 lib]$ env | grep PATH

PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/oradata/oracle/product/11.2.0/db_1/bin:/home/oracle/bin

[oracle@scdb2 lib]$ cd

[oracle@scdb2 ~]$ vi .bash_profile

添加语句LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH至最后

[oracle@scdb2 ~]$ source .bash_profile

[oracle@scdb2 ~]$ env | grep PATH

LD_LIBRARY_PATH=/oradata/oracle/product/11.2.0/db_1/lib

PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/oradata/oracle/product/11.2.0/db_1/bin:/home/oracle/bin:/home/oracle/bin:/oradata/oracle/product/11.2.0/db_1/bin:/home/oracle/bin

再次查看csscan是否可用：

[oracle@scdb2 ~]$ csscan help=y

Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Tue Aug 23 13:48:15 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

You can let Scanner prompt you for parameters by entering the CSSCAN

command followed by your username/password:

Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"

Or, you can control how Scanner runs by entering the CSSCAN command

followed by various parameters. To specify parameters, you use keywords:

Example:

CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Keyword    Default Prompt Description

---------- ------- ------ -------------------------------------------------

USERID             yes    username/password

FULL       N       yes    scan entire database

USER               yes    owner of tables to be scanned

TABLE              yes    list of tables to scan

COLUMN             yes    list of columns to scan

EXCLUDE                   list of tables to exclude from scan

TOCHAR             yes    new database character set name

FROMCHAR                  current database character set name

TONCHAR                   new national character set name

FROMNCHAR                 current national character set name

ARRAY      1024000 yes    size of array fetch buffer

PROCESS    1       yes    number of concurrent scan process

MAXBLOCKS                 split table if block size exceed MAXBLOCKS

CAPTURE    N              capture convertible data

SUPPRESS                  maximum number of exceptions logged for each table

FEEDBACK                  report progress every N rows

BOUNDARIES                list of column size boundaries for summary report

LASTRPT    N              generate report of the last database scan

LOG        scan           base file name of report files

PARFILE                   parameter file name

PRESERVE   N              preserve existing scan results

LCSD       N       no     enable language and character set detection

LCSDDATA   LOSSY   no     define the scope of the detection

HELP       N              show help screen (this screen)

QUERY      N              select clause to scan subset of tables or columns

---------- ------- ------ -------------------------------------------------

Scanner terminated successfully.

在命令行调用该工具对数据库进行扫描：

[oracle@scdb2 ~]$ export ORACLE_SID=bce

[oracle@scdb2 ~]$ csscan user=cams_bce fromchar=ZHS16GBK tochar=AL32UTF8 log=GBK2UTF8check.log capture=y array=1024000 process=4