【Oracle篇】expdp/impdp轻松完成某个生产用户从GBK到UTF8编码的迁移（第三篇，总共四篇）

奈斯DBA

已于 2024-06-08 23:46:52 修改

阅读量1.4k

点赞数 12

分类专栏： Oracle专栏文章标签： oracle 数据库运维

于 2024-03-27 07:00:00 首次发布

本文链接：https://blog.csdn.net/naisiing/article/details/137034993

版权

Oracle专栏专栏收录该内容

28 篇文章 20 订阅

订阅专栏

💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨

💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

在之前的文章中我详细介绍了expdp/impdp逻辑迁移工具的用法（直通车👉【Oracle篇】一文搞清expdp/impdp逻辑迁移工具的用法和定时全备实例（第二篇，总共四篇）_exp并行导出-CSDN博客👈），数据泵可以完成对全库、单个生产用户、生产表、某个表空间的迁移，这些都是难度比较小的操作，如果有一个场景因为业务扩展需要存储日文、越南语，现在的Oracle字符集是ZHS16GBK，但是只对汉字、英文字符、数字，+-*/等进行编码，要想存储越南语就会有乱码，需要将Oracle的字符集改成AL32UTF8去存储日文、越南语，那么又应该怎么办呢？

既然抛出了这个问题就要解决这个问题，我先给出答案——最安全的办法就是重建一个实例，然后修改char/varchar2数据类型的长度，最后通过expdp/impdp数据泵导入进去。那么带着这个问题，让我们开始今天的内容，通过expdp/impdp轻松完成某个生产用户从GBK到UTF8编码的迁移。

关于逻辑迁移工具全部的篇幅介绍，四篇的内容分别如下：

第一篇：一文搞清exp/imp逻辑迁移工具的用法和定时全备实例
第二篇：一文搞清expdp/impdp逻辑迁移工具的用法和定时全备实例
第三篇：expdp/impdp轻松完成某个生产用户从GBK到UTF8编码的迁移（当前篇）
第四篇：expdp/impdp高效完成全部生产用户的全库迁移

源生产库（ZHS16GBK，实例orcl，RAC环境）部分

目标库（AL32UTF8，实例baj，RAC环境）部分

验证数据部分

Oracle常用字符集：

US7ASCII：只能存储美国人使用的字符，26个英文字母，数字，+-*/等等。

ZHS16GBK：存储中国人常用的字符，如汉字、英文字符、数字，+-*/等等。如果有韩文显示乱码（没有编码）

UTF8：unicode字符集，存储全世界的字符，只是不全。

AL32UTF8：unicode字符集，存储全世界的字符，比UTF8大多了，全但性能较差。适合中文、韩文、日语等不同语言的使用。

ALS16UTF16：国家字符集选择它。

字符集存储数据相关参数：

SQL> show parameter NLS_LENGTH_SEMANTICS;
NLS_LENGTH_SEMANTICS：用于指定长度语义，有两个值BYTE、CHAR。MySQL的字符集转换不同于oracle，因为oracle是字节byte所以涉及到列长度的转换，但mysql是存储是用char，所以不涉及到列的转换。但是Oracle可以通过NLS_LENGTH_SEMANTICS参数设置用BYTE还是CHAR存储数据，默认是BYTE字节存储。这个参数主要是针对char和varchar2这两种数据类型，数据存储是使用字节byte还是字符char设计的。对于NCHAR、NVARCHAR2、CLOB和NCLOB列始终基于char字符。

BYTE：以字节的形式存储数据，默认值。gbk迁移到utf8就会涉及到char和varchar2数据类型的长度转换。

CHAR：以字符的形式存储数据，gbk迁移到utf8不会涉及到长度转换。注意：Oracle强烈建议不要在运行的实例中将参数设置为CHAR，因为可能导致许多现有安装脚本或者表数据意外地创建具有字符长度语义的列，从而导致运行时错误，包括缓冲区溢出。可以考虑在新实例中修改，新实例修改参考下面的案例一，修改长度语义只对后续手动创建的表生效，现有表还是原byte。

首先了解为什么迁移字符集需要修改char/varchar2数据类型的长度，而不需要修改数值、日期等数据类型的长度。下面我们先看案例——汉字和字符在char/varchar2/nchar/nvarchar2数据类型中的占用的字节：

char(n)数据类型：存放固定长度的字符集，最大存放2000 bytes

AMERICAN_AMERICA.ZHS16GBK（一个字符1字节，一个汉字为2字节）

SQL> create  table stu01 (name char(32));  
SQL> show parameter NLS_LENGTH_SEMANTICS  
SQL> insert  into stu01  values('liufei');
SQL> insert  into stu01  values('刘一');

SQL> select  name,dump(name) from  stu01;    ---dump为展现这个行（name）的详细内容
###插入的数据liufei的详细内容，数字代表字码，105代表字母i，空格用32代码表示。插入超过字符定义的值则报错

AMERICAN_AMERICA.AL32UTF8（一个字符1字节，一个汉字为3字节）

SQL> create  table stu01 (name char(32));  
SQL> insert  into stu01  values('liufei');
SQL> insert  into stu01  values('刘一');

SQL> select  name,dump(name) from  stu01;      ---dump为展现这个行（name）的详细内容
###插入的数据liufei的详细内容，数字代表字码，105代表字母i，空格用32代码表示。插入超过字符定义的值则报错

varchar2(n)：存放可变长长度的字符集，最大可以存放4000 bytes

AMERICAN_AMERICA.ZHS16GBK（一个字符1字节，一个汉字为2字节）

SQL> create  table stu02 (name varchar2(16));
SQL> insert  into stu02(name)  values('liufei');
SQL> insert  into stu02(name)  values('刘一');

SQL> select  name,dump(name) from  stu02;    ---存放了‘liufei’的6个字符，实际存放数据库中，就只占用了6个（‘liufei’），其余的剩下的字符就被回收了。    
###如果插入数据是固定长度的，比如手机号码（11位）、身份证号（18位），则应当使用char来存放，这样的好处是查询与检索速度较快。原因为查询char类型的字段时，作为整体进行查询，而varchar2是一个个数据进行比对的。 而如果存放的字符串的长度不固定，则建议使用varchar2（size）

AMERICAN_AMERICA.AL32UTF8（一个字符1字节，一个汉字为3字节）

SQL> create table stu02 (name varchar2(16));
SQL> insert  into stu02(name)  values('liufei');
SQL> insert  into stu02(name)  values('刘一');

SQL> select  name,dump(name) from  stu02;      ---存放了‘liufei’的6个字符，实际存放数据库中，就只占用了6个（‘liufei’），其余的剩下的字符就被回收了。    
###如果插入数据是固定长度的，比如手机号码（11位）、身份证号（18位），则应当使用char来存放，这样的好处是查询与检索速度较快。原因为查询char类型的字段时，作为整体进行查询，而varchar2是一个个数据进行比对的。 而如果存放的字符串的长度不固定，则建议使用varchar2（size）

nchar(n)：根据字符集而定的固定长度字符集，nchar(n)最大存放2000 bytes

AMERICAN_AMERICA.ZHS16GBK（一个字符2字节，一个汉字为2字节）
SQL> create table qwe (name nchar(16));
SQL> insert into qwe (name) values('liufei');
SQL> insert into qwe (name) values('刘一');

SQL> select dump(name) from qwe;
AMERICAN_AMERICA.AL32UTF8（一个字符2字节，一个汉字为2字节）
SQL> create table qwe (name nchar(16));
SQL> insert into qwe (name) values('liufei');
SQL> insert into qwe (name) values('刘一');

SQL> select dump(name) from qwe;

nvarchar2(n)：根据字符集而定的固定长度字符集，nvarchar2(n)最大存放4000 bytes。

AMERICAN_AMERICA.ZHS16GBK（一个字符2字节，一个汉字为2字节）

SQL> create table qwe2 (name nvarchar2(16));
SQL> insert  into qwe2 (name) values('liufei');
SQL> insert  into qwe2 (name) values('刘一');

SQL> select dump(name) from qwe2;

AMERICAN_AMERICA.AL32UTF8（一个字符2字节，一个汉字为2字节）

SQL> create table qwe2 (name nvarchar2(16));
SQL> insert  into qwe2 (name) values('liufei');
SQL> insert  into qwe2 (name) values('刘一');

SQL> select dump(name) from qwe2;

那么现在明白了为什么修改字符集时需要增加 char/varchar2数据类型长度了吧！对于NCHAR、NVARCHAR2、CLOB和NCLOB列始终基于char字符，CLOB和NCLOB大字段的数据类型一般是不会指定长度的，所以也就不会涉及到长度的增加了，所以这里我就不演示了。

那么总结一下：

Oracle字符集的修改不要在现有的实例上直接去修改，因为oracle默认是字节byte 所以涉及到列长度的转换，转换涉及到 char和varchar2 两种数据类型，其他数据类型不涉及到转换，也有通过在线修改单机或者rac的字符集，但是会有很大的风险，所以通过expdp/impdp数据泵是最有效的方案。

源生产库（ZHS16GBK，实例orcl，RAC环境）部分

1、字符集存储数据参数

SQL> show parameter NLS_LENGTH_SEMANTICS;
###BYTE：以字节的形式存储数据，默认值。gbk迁移到utf8就会涉及到char和varchar2数据类型的长度转换。

2、创建数据泵的dmp文件存放目录

[root@rac1 ~]# mkdir /liu 
[root@rac1 ~]# chown oracle:oinstall /liu
###文件liu（路径/liu）在/dev/sdb3下挂载，将文件的所属用户和目录改为oracle：oinstall

SYS@orcl> create  directory  BACKUP20200328 as  '/liu'; 
SYS@orcl> grant  all  on  directory  BACKUP20200328  to  system ;
###创建数据泵的转储路径（在使用expdp时，指定到liu目录时，数据文件就会生成在/liu路径下）。赋予给所有用户目录liu的所有执行权限，为了以后普通用户使用expdp时有权限将dmp数据文件导入到/liu下。

3、导出的数据库为ZHS16GBK，只导出生产baj这个用户

[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ echo $ORACLE_SID
                 
[oracle@rac1 ~]$ expdp  baj/baj directory=BACKUP20200328  dumpfile=expdp_orcl_baj_%U.dmp  logfile=expdp_orcl_baj.log  schemas=baj  parallel=4  cluster=n

4、生成生产库的业务表空间SQL语句：

ps：排查掉SYSAUX、SYSTEM、TEMP、UNDOTBS1、USERS这些系统表空间的创建

SQL> set linesize 500 
     set pagesize 99
     col file_name for a70 
     col file_id for 9999999 
     col status for a10 
     col ts_name for a25 
     col cur_mb for 99999 
     select status, file_id, file_name, tablespace_name ts_name,blocks/128 tolal_mb, maxblocks/128 max_mb,AUTOEXTENSIBLE,status,online_status from dba_data_files order by file_name;
         
SQL> SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 180 
         
SQL> select dbms_metadata.get_ddl('USER','BAJ') from dual;     ###查看创建用户的语句，需要确定默认的表空间和默认的临时表空间
SQL> select dbms_metadata.get_ddl('TABLESPACE','NNC_DATA01') from dual;   ###查看创建表空间的语句
SQL> select dbms_metadata.get_ddl('TABLESPACE','NNC_INDEX01') from dual;

目标库（AL32UTF8，实例baj，RAC环境）部分

1、生产baj用户导入前需要注意的事情：

1）关注归档目录，定时进行删除，避免空间耗尽。可以考虑先关闭归档

2）baj数据量有500G以上，导入过程中undo和temp占用很多，适当扩容。

Undo表空间给7个，总大小210G，并且减少undo_retention为300秒

Temp表空间给3个，总大小90G

3）数据文件看情况增加

4）expdp/impdp导入导出优化。👉【Oracle篇】一文搞清expdp/impdp逻辑迁移工具的用法和定时全备实例（第二篇，总共四篇）_exp并行导出-CSDN博客👈

2、在服务器上创建一个AL32UTF8字符集的实例baj

实例静默安装：

[oracle@rac1 ~]$ vi /home/db/oracle/product/12.2/db_1/response/dbca.rsp

sid=实例名
gdbName=全局数据库名
sysPassword=密码
systemPassword=密码
dbsnmpPassword=密码
datafileDestination=/home/db/oracle/oradata     ---数据库文件路径
recoveryAreaDestination=/home/db/oracle/fast_recovery_area
characterSet= AL32UTF8
totalMemory=AMM内存大小        ---单位M
automaticMemoryManagement=TRUE    ---是否启用自动内存管理
templateName=General_Purpose.dbc     ---数据库模板，必填
storageType=FS          ---指定要创建的数据库的存储类型，有FS和ASM，默认FS文件系统

[oracle@rac1 ~]$ dbca -silent -createDatabase -responseFile /home/db/oracle/product/12.2/db_1/response/dbca.rsp

调整新实例的数据文件、undo、temp、redo初始大小

数据库文件和undo：
SQL> set linesize 500 
     set pagesize 99
     col file_name for a70 
     col file_id for 9999999 
     col status for a10 
     col ts_name for a25 
     col cur_mb for 99999 
     col max_mb for 99999
SQL> select status, file_id, file_name, tablespace_name ts_name,blocks/128 tolal_mb, maxblocks/128 max_mb,AUTOEXTENSIBLE from dba_data_files order by file_name;

SQL> alter database datafile 2 resize 20g;
SQL> alter database datafile 1 resize 20g;
SQL> alter database datafile 3 resize 20g;
SQL> alter database datafile 5 resize 20g;
SQL> alter database datafile 4 resize 10g;


temp临时表空间:
SQL> select username,temporary_tablespace from dba_users;
SQL> set linesize 230
     col file_name for a65
SQL> select  FILE_ID,FILE_NAME,TABLESPACE_NAME,bytes/1024/1024 tolal_mb,status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 max_mb from dba_temp_files;

SQL> alter database tempfile 1 resize 20g;


redo重做日志:生产建议3到6组，每组大小为1G左右
SQL> set linesize 230
     col member for a50
SQL> select * from v$logfile;
SQL> select * from v$log;

SQL> alter database add logfile thread 1 group 10 ('+ARCHDG') size 1024m;
SQL> alter database add logfile thread 1 group 11 ('+ARCHDG') size 1024m;
SQL> alter database add logfile thread 1 group 12 ('+ARCHDG') size 1024m;
SQL> alter database add logfile thread 1 group 13 ('+ARCHDG') size 1024m;
SQL> alter database add logfile thread 2 group 14 ('+ARCHDG') size 1024m;
SQL> alter database add logfile thread 2 group 15 ('+ARCHDG') size 1024m;
SQL> alter database add logfile thread 2 group 16 ('+ARCHDG') size 1024m;

SQL> alter system switch logfile;
SQL> alter system checkpoint;

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;

关闭密码过期（默认180天过期）:

SQL> select * from dba_profiles where profile='DEFAULT';

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
SQL> alter profile default limit PASSWORD_LOCK_TIME unlimited;
SQL> alter profile default limit PASSWORD_GRACE_TIME unlimited;

关闭审计：长时间开启审计，审计占用空间较大

SQL> alter system set audit_trail=none scope=spfile;

设置归档路径：

SQL> shutdown  immediate
SQL> startup  mount 

SQL> alter  database  archivelog; 
SQL> alter system set log_archive_dest_1='location=+ASM磁盘';

SQL> alter database open;
SQL> archive log list;
SQL> show parameter recover;

db实例参数优化：

SQL> 
alter system set sga_max_size=物理内存x0.5 scope=spfile sid='*';
alter system set sga_target=物理内存x0.5 scope=spfile sid='*';
alter system set pga_aggregate_target=物理内存x0.2 scope=spfile sid='*';    ---设置为ASMM
alter system set "_cursor_obsolete_threshold"=400 scope=spfile sid='*';
alter system set  "_rollback_segment_count" =2000 scope=spfile sid='*';
alter system set "_ktb_debug_flags"=8 sid='*';
alter system set "_datafile_write_errors_crash_instance"=false scope=spfile sid='*';
alter system set events '43822 trace name context forever,level 1';
alter system set enable_ddl_logging=true sid='*' scope=spfile;
alter system set fast_start_parallel_rollback=low sid='*';
alter system set recovery_parallelism=10 scope=spfile sid='*';
alter system set "_cleanup_rollback_entries"=10000 scope=spfile sid='*';
alter system set "_gc_bypass_readers" = false sid = '*' scope=spfile;
alter system set "_gc_override_force_cr" = false sid = '*' scope=spfile;
alter system set "_gc_read_mostly_locking" = false sid = '*' scope=spfile;
alter system set "_serial_direct_read" = false sid = '*' ;
alter system set "_high_priority_processes" = "LGWR|LMS*|VKTM" sid='*' scope=spfile;
alter system set "_use_adaptive_log_file_sync"=false sid='*' scope=spfile;
alter system set "_px_use_large_pool"=true  sid ='*' scope=spfile;
alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1','60025 trace name context forever,level 1', '10503 trace name context forever,level 128' sid='*' scope=spfile;
alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile;
alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile;
alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile;
alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile;
alter system set "_gc_defer_time"=3 scope=spfile sid='*';
alter system set result_cache_max_size=0 scope=spfile sid='*';
alter system set "_gc_policy_time"=0 scope=spfile sid='*';
alter system set "_gc_undo_affinity"=false  scope=spfile  sid='*';
alter system set control_file_record_keep_time=31 scope=spfile sid='*';
alter system set parallel_max_servers=128 scope=spfile sid='*';
alter system set parallel_adaptive_multi_user=false sid='*';
alter system set "_undo_autotune"=false scope=spfile sid='*';
alter system set undo_retention=10800 scope=spfile sid='*';
alter system set "_partition_large_extents"=false scope=spfile sid='*';
alter system set parallel_force_local=true scope=spfile sid='*';
alter system set processes=2000 scope=spfile sid='*';      ---进程数。设置完成后，session和transactions参数会自动调整（session=1.5*processes+22  ；  transactions=1.1*sessions）
alter system set open_cursors=2000 scope=spfile sid='*';   ---打开游标数。一个sql语句就会对应到一个游标（简单理解游标就是一个sql语句）
alter system set session_cached_cursors=300 scope=spfile sid='*';    ---会话缓存游标数（sql语句）。一个会话缓存多个游标（sql语句），一个sql对应一个游标，缓存游标是为了避免软解析，相同的sql再次执行就不需要打开游标了（执行sql就会打开游标，避免打开游标就是避免软解析），绑定变量解决硬解析。 
alter system set db_files=8192 scope=spfile sid='*';   ---允许创建的数据文件总数

3、确认字符集调整undo保留时间

[oracle@rac1 ~]# export ORACLE_SID=baj1
[oracle@rac1 ~]# echo $ORACLE_SID

SQL> alter system set undo_retention=300 scope=both sid='*';   --导入完成之后修改回来

SQL> select * from  nls_database_parameters; 
SQL> select * from  v$nls_parameters;  
SQL> select  userenv('language')  from  dual ;

4、创建表空间

SQL> 
set linesize 500 
set pagesize 99
col file_name for a70 
col file_id for 9999999 
col status for a10 
col ts_name for a25 
col cur_mb for 99999 
select status, file_id, file_name, tablespace_name ts_name,blocks/128 tolal_mb, maxblocks/128 max_mb,AUTOEXTENSIBLE,status,online_status from dba_data_files order by file_name;

alter  tablespace  UNDOTBS1  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS1  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS1  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS1  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS1  add  datafile '+DATADG'  size 31G autoextend  on;

alter  tablespace  UNDOTBS2  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS2  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS2  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS2  add  datafile '+DATADG'  size 31G autoextend  on;
alter  tablespace  UNDOTBS2  add  datafile '+DATADG'  size 31G autoextend  on;

alter  tablespace  temp  add  tempfile  '+DATADG'  size  31G  autoextend  on; 
alter  tablespace  temp  add  tempfile  '+DATADG'  size  31G  autoextend  on; 

CREATE TABLESPACE "NNC_DATA01" DATAFILE
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 5G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "NNC_INDEX01" DATAFILE
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE "NNC_INDEX01" DATAFILE
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on,
'+DATADG' SIZE 1G autoextend on
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

5、创建存放DMP文件夹的引用地址

SQL> create directory baj_dir as '/home/oracle/backup20200328';
SQL> grant all on directory baj_dir to system;

6、linux下使用impdp工具导入（只是导入对象）：

[oracle@rac1 ~]# export ORACLE_SID=baj1
[oracle@rac1 ~]# echo $ORACLE_SID
            
[oracle@rac1 ~]# impdp system/oracle directory=baj_dir dumpfile=expdp_orcl_baj_01.dmp,expdp_orcl_baj_02.dmp,expdp_orcl_baj_03.dmp,expdp_orcl_baj_04.dmp logfile=impdp_baj_baj_metadata_only.log schemas=baj parallel=8 CONTENT=metadata_only table_exists_action=append cluster=n
####impdp导入之前，需要在目标数据库上创建相应表空间对象即可；而对于imp导入时需要在目标数据库上创建相应的用户、权限、表空间等对象。

7、旧库迁移到新库，旧库编码是GBK，新库是UTF-8，一个汉字GBK咱2个字节UTF-8占三个字节，故对varchar2和char类型字段扩容二分之一。

注意：有些业务表是定长char数据类型，但是内容只有字符，没有汉字，如果统一进行扩容的话可能会影响程序对char数据类型的判断，所以对于char数据类型按照要求扩长或者不扩长，哪些char必须要扩容只有在导入的时候报错了再考虑对char进行扩容。

[oracle@rac1 ~]# sqlplus baj/123456
###连接到baj用户执行下面操作

扩展char小于1300的SQL语句：

SQL> set pagesize 0;
SQL> spool /home/oracle/char1.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||
             t_column.column_name || ' char(' || (t_column.data_length + ceil(t_column.data_length * 0.5)) || ');' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length <= 1300
         AND t_column.data_type = 'CHAR';
SQL> spool off

扩展char大于1300且小于2000的SQL语句：

SQL> set pagesize 0;
SQL> spool /home/oracle/char2.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||t_column.column_name || ' char(2000);' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length > 1300
         AND t_column.data_length < 2000
         AND t_column.data_type = 'CHAR';
SQL> spool off

扩展varchar2大于2600且小于4000的SQL语句：

SQL> set pagesize 0;
SQL> spool /home/oracle/char3.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||
             t_column.column_name || ' varchar2(4000);' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length > 2600
         AND t_column.data_length < 4000
         AND t_column.data_type = 'VARCHAR2';
SQL> spool off

扩展varchar2小于2600的SQL语句：

SQL> set pagesize 0;
SQL> spool /home/oracle/char4.sql
      SELECT 'alter table ' || t_column.table_name || ' modify ' ||
             t_column.column_name || ' varchar2(' || (t_column.data_length + ceil(t_column.data_length * 0.5)) || ');' AS alter_sqlstr
        FROM user_tab_columns t_column, user_tables t_tables
       WHERE t_column.table_name = t_tables.table_name
         AND t_column.data_length <= 2600
         AND t_column.data_type = 'VARCHAR2';
SQL> spool off

8、执行需要扩容的SQL文本：

[oracle@rac1 ~]# sqlplus baj/123456
###连接到baj用户执行下面操作
    
SQL> /home/oracle/char1.sql
SQL> /home/oracle/char2.sql
SQL> /home/oracle/char3.sql
SQL> /home/oracle/char4.sql
###执行char1.sql、char2.sql、char3.sql、char4.sql之前，需要删除多余的spool和spool off内容只保留alter table内容

9、如果有些表扩长到varchar2(4000)也不足，所以需要修改为clob数据类型

ps：在第一次导入的时候发现导入WA_CLASSITEM表时，VFORMULASTR字段扩容到了varchar2（4000），但数据长度需要4070，varchar2最大长度为4000，所以只能通过alter table修改数据类型为clob才能解决，那么只能将生产用户删掉，然后再来一遍哦！

SQL> ALTER TABLE WA_CLASSITEM DROP COLUMN VFORMULASTR;  
SQL> ALTER TABLE WA_CLASSITEM add VFORMULASTR CLOB;

10、linux下使用impdp工具导入（导入对象的数据）：

[oracle@rac1 ~]# export ORACLE_SID=baj1
[oracle@rac1 ~]# echo $ORACLE_SID
         
[oracle@rac1 ~]# impdp system/oracle directory=baj_dir dumpfile=expdp_orcl_baj_01.dmp,expdp_orcl_baj_02.dmp,expdp_orcl_baj_03.dmp,expdp_orcl_baj_04.dmp logfile=impdp_baj_baj_data_only.log schemas=baj parallel=8 CONTENT=data_only table_exists_action=append  cluster=n
####impdp导入之前，需要在目标数据库上创建相应表空间对象即可；而对于imp导入时需要在目标数据库上创建相应的用户、权限、表空间等对象。

验证数据部分

第一步：验证数据大小

SQL> select sum(bytes) / 1024 / 1024 / 1024 || 'G' sum
                   from dba_segments
              where owner in ('BAJ')

第二步：验证有无失效的对象（目标数据库上执行）

SQL> select * from dba_objects where status!='VALID' and owner in('BAJ');
SQL> @?/rdbms/admin/utlrp.sql    ---有无效对象的话，进行无效对象的编译（最大可能自动修复无效对象）。

第三步：收集统计信息（目标数据库上执行）

[oracle@rac1 ~]# vi status.sql 

begin 
dbms_stats.gather_database_stats; 
end; 
/ 

[oracle@rac1 ~]# nohup sqlplus / as sysdba @status.sql &    ---因为收集统计信息时间长，所以写个sh后台运行

第四步：查看哪些表的统计信息被锁定（stattype_locked字段为ALL的表示锁定了表的统计信息，默认stattype_locked字段为空表示可以收集统计信息）：

SQL> select * from dba_ind_statistics where stattype_locked='ALL' AND OWNER='BAJ';
SQL> select * from dba_tab_statistics where stattype_locked='ALL' AND OWNER='BAJ';

SQL> begin
       dbms_stats.unlock_schema_stats(ownname  => 'BAJ');
     end;
     /

第五步：验证对象（目标数据库上执行）

SQL> select object_type t_object_type, count(*) t_count
            from dba_objects
         where owner in('BAJ')
         group by object_type
###注：oracle的对象类型可以分的很详细，表、表分区、表子分区是不同的类型。

第六步：对比导入和导出日志

第七步：将undo时间修改回最佳值

SQL> alter system set undo_retention=10800 scope=both sid='*';

兄弟们终于写完了！！！这篇文章是我在下班后写了3个小时奋战到深夜23点才搞定的，之所以分享是因为大家之后肯定会用到的，所以别吝啬你的小手，点赞、收藏、加关注。给我来点动力呗。那么我们下一篇文章见——expdp/impdp高效完成全部生产用户的全库迁移。

奈斯DBA

关注

12
点赞
踩
22

收藏

觉得还不错? 一键收藏
打赏
0
评论
【Oracle篇】expdp/impdp轻松完成某个生产用户从GBK到UTF8编码的迁移（第三篇，总共四篇）

通过expdp/impdp完成生产用户从GBK到UTF8编码的迁移。
复制链接

扫一扫

专栏目录