ORACLE数据库导入导出——JSHY/HN

一、1.49导出操作

修改oracle客户端字符集和数据库字符集一致，UTF8

expdp system/****@mesorcl DIRECTORY=backup_prd DUMPFILE=172.18.1.49-20210303_full-3.dmpdp logfile=172.18.1.49-20210303_full_expdp-3.log full=y compression=all

二、JSHY导入操作

正式机：

global_name:mesorcl

SID:mesorcl

1、创建表空间和数据文件

（1）主文件

create tablespace MES_PRD datafile '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA.dbf' size 4000M  autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
ALTER TABLESPACE MES_PRD ADD DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA01.dbf' SIZE 4000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
ALTER TABLESPACE MES_PRD ADD DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA02.dbf' SIZE 4000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
ALTER TABLESPACE MES_PRD ADD DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA03.dbf' SIZE 4000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
ALTER TABLESPACE MES_PRD ADD DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA04.dbf' SIZE 4000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
ALTER TABLESPACE MES_PRD ADD DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA05.dbf' SIZE 4000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
ALTER TABLESPACE MES_PRD ADD DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA06.dbf' SIZE 4000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 

create temporary tablespace MES_PRD_TEMP_NEW TEMPFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_TEMP_NEW.dbf' size 1000M reuse autoextend on next 200M maxsize unlimited;
create user MES_PRD identified by **** default tablespace MES_PRD temporary tablespace MES_PRD_TEMP_NEW;
grant connect,dba,resource to MES_PRD ;
create directory backup_PRD as '/backup/expdp';
GRANT READ,WRITE ON DIRECTORY backup_PRD to MES_PRD;
GRANT READ,WRITE ON DIRECTORY backup_PRD to system;

（2）MM表空间文件

create tablespace MES_MM datafile '/oracle/app/oracle/oradata/mesorcl/MES_MM_DATA.dbf' size 4000M autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
create temporary tablespace MES_MM_TEMP TEMPFILE '/oracle/app/oracle/oradata/mesorcl/MES_MM_TEMP.dbf' size 500M reuse autoextend on next 20M maxsize unlimited;

2、导入，导出命令

导入命令：
impdp system/**** dumpfile=172.18.1.49-20210303_FULL-3.DMPDP logfile=1.49.dmpdp.log DIRECTORY=backup_mes SCHEMAS=MES_PRD EXCLUDE=STATISTICS

导出命令：
expdp system/**** DIRECTORY=backup_mes DUMPFILE=MESPRD_TBSP_KSSY_20201104.dmpdp logfile=MESPRD_TBSP_20201104.log full=y compression=all

3、处理编译错误的问题

JSHY
alter function MES_PRD.MES_DZSTEPDATA compile;
alter function MES_PRD.MES_PRDGDCXML compile;
alter function MES_PRD.MES_PRDJCX compile;
alter function MES_PRD.MES_PRDMK compile;
alter function MES_PRD.MES_PRDPBCWXML compile;
alter function MES_PRD.MES_PRDUNITXML compile;
alter function MES_PRD.MES_PRDXML compile;
alter function MES_PRD.MES_PRDXUHAOXML compile;

alter procedure MES_PRD.HD_SP_SD_GETGONGYILIST  compile;
alter procedure MES_PRD.MES_DRAWPRDXML compile;

HN:

alter function MES_PRD.MES_DZSTEPDATA compile;
alter function MES_PRD.MES_PRDGDCXML compile;
alter function MES_PRD.MES_PRDJCX compile;
alter function MES_PRD.MES_PRDMK compile;
alter function MES_PRD.MES_PRDPBCWXML compile;
alter function MES_PRD.MES_PRDUNITXML compile;
alter function MES_PRD.MES_PRDXML compile;
alter function MES_PRD.MES_PRDXUHAOXML compile;

alter procedure MES_PRD.HD_SP_HR_COLLECTPIECEWORK compile;
alter procedure MES_PRD.HD_SP_SD_GETGONGYILIST compile;
alter procedure MES_PRD.MES_DEPT_STATICFORPRODUCT compile;
alter procedure MES_PRD.MES_DRAWPRDXML compile;
alter procedure MES_PRD.MES_INTEGRATEDKANBAN01 compile;
alter procedure MES_PRD.MES_PROCESS_QUANTITY compile;
alter procedure MES_PRD.MES_UPDATE_BANPGW compile;
alter procedure MES_PRD.PROC_KANBAN_RIGHT compile;
alter procedure MES_PRD.PROC_KANBAN_RIGHT_ZM compile;
alter procedure MES_PRD.TRANSPORTFEE_PRO compile;

alter trigger MES_PRD.TR_Blogs compile;
alter trigger MES_PRD.TR_Blogs compile;
alter trigger MES_PRD.TRG_SA_ROLECONFIG_INSERT compile;
alter trigger MES_PRD.TRG_SA_ROLECONFIG_INSERT compile;
alter trigger MES_PRD.TRG_MES_DUTOU_INSERT compile;
alter trigger MES_PRD.TRG_MES_DUTOU_INSERT compile;
alter trigger MES_PRD.TR_Posts compile;
alter trigger MES_PRD.TR_Posts compile;