记一次某医院数据泵迁移完整步骤

迁移步骤如下：

########### 创建5个PDB和一个非CDB的数据库
## HIS Lis库
asmcmd
ASMCMD> cd +DATADG/ZDATADB
ASMCMD> mkdir LIS

sqlplus / as sysdba
SQL> CREATE PLUGGABLE DATABASE lis ADMIN USER lisadmin IDENTIFIED BY oracle roles=(DBA)
DEFAULT TABLESPACE users DATAFILE '+DATADG/ZDATADB/lis/DATAFILE/users.dbf' SIZE 20G AUTOEXTEND ON
PATH_PREFIX = '/u01/app/oracle/19300/dbs'
FILE_NAME_CONVERT = ('+DATADG/ZDATADB/B3BE6592DC21512FE0531F1F10AC5BF0/DATAFILE/undotbs1.265.1056130527','+DATADG/ZDATADB/LIS/undotbs1.dbf','+DATADG/ZDATADB/B3BE6592DC21512FE0531F1F10AC5BF0/DATAFILE/system.261.1056130523','+DATADG/ZDATADB/LIS/system.dbf','+DATADG/ZDATADB/B3BE6592DC21512FE0531F1F10AC5BF0/DATAFILE/sysaux.263.1056130525','+DATADG/ZDATADB/LIS/sysaux.dbf','+DATADG/ZDATADB/B3BE6592DC21512FE0531F1F10AC5BF0/TEMPFILE/temp.267.1056130527','+DATADG/ZDATADB/LIS/temp.dbf');
create tablespace users datafile '+DATADG/ZDATADB/LIS/users.dbf' size 20G autoextend on; --不知道什么原因上面的命令没有创建users表空间，这里单独创建

## HIS PORTAL
asmcmd
ASMCMD> cd +DATADG/ZDATADB
ASMCMD> mkdir PORTAL

SQL> conn / as sysdba --返回到cdb
SQL> alter session set container=lis;
SQL> shutdown immediate
SQL> alter database open read only;
SQL> conn / as sysdba
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 LIS READ ONLY NO
--根据lis这个pdb创建portal pdb
CREATE PLUGGABLE DATABASE portal from lis
PATH_PREFIX = '/u01/app/oracle/19300/dbs'
FILE_NAME_CONVERT = ('+DATADG/ZDATADB/LIS','+DATADG/ZDATADB/PORTAL')
SERVICE_NAME_CONVERT=('lis','PORTAL');


## HIS YDHL
asmcmd
ASMCMD> cd +DATADG/ZDATADB
ASMCMD> mkdir YDHL

--根据lis这个pdb创建ydhl pdb
CREATE PLUGGABLE DATABASE ydhl from lis
PATH_PREFIX = '/u01/app/oracle/19300/dbs'
FILE_NAME_CONVERT = ('+DATADG/ZDATADB/LIS','+DATADG/ZDATADB/YDHL')
SERVICE_NAME_CONVERT=('lis','YDHL');


## HIS TIJIAN
asmcmd
ASMCMD> cd +DATADG/ZDATADB
ASMCMD> mkdir TIJIAN

--根据lis这个pdb创建tijian pdb
CREATE PLUGGABLE DATABASE tijian from lis
PATH_PREFIX = '/u01/app/oracle/19300/dbs'
FILE_NAME_CONVERT = ('+DATADG/ZDATADB/LIS','+DATADG/ZDATADB/TIJIAN')
SERVICE_NAME_CONVERT=('lis','TIJIAN');

## HIS HIS
asmcmd
ASMCMD> cd +DATADG/ZDATADB
ASMCMD> mkdir HIS

--根据lis这个pdb创建his pdb
CREATE PLUGGABLE DATABASE his from lis
PATH_PREFIX = '/u01/app/oracle/19300/dbs'
FILE_NAME_CONVERT = ('+DATADG/ZDATADB/LIS','+DATADG/ZDATADB/HIS')
SERVICE_NAME_CONVERT=('lis','HIS');


## 创建非CDB的histemp数据库，用来做XTTS
先创建一个hisdg磁盘组


########### 将所有的Pdb 全部打开
SQL> alter session set container=lis;
SQL> shutdown immediate
SQL> startup --因为lis库是read only状态，所以要重启

SQL> alter session set container=portal;
SQL> alter database open;

SQL> alter session set container=tijian;
SQL> alter database open;

SQL> alter session set container=ydhl;
SQL> alter database open;

SQL> alter session set container=his;
SQL> alter database open;

########### 配置两个实例oracle用户下的tnsnames.ora
LIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lis)
)
)

PORTAL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = portal)
)
)

YDHL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ydhl)
)
)

TIJIAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tijian)
)
)

HIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = his)
)
)

--连接测试
sqlplus sys/oracle@lis as sysdba <<EOF
show con_name;
exit
EOF

sqlplus sys/oracle@portal as sysdba <<EOF
show con_name;
exit
EOF

sqlplus sys/oracle@ydhl as sysdba <<EOF
show con_name;
exit
EOF

sqlplus sys/oracle@tijian as sysdba <<EOF
show con_name;
exit
EOF

sqlplus sys/oracle@his as sysdba <<EOF
show con_name;
exit
EOF


########### 数据泵迁移步骤
1. 在目标端创建源端的表空间(生成脚本方便后面使用)
--体检 tijian
alter session set container=tijian;
create tablespace BSTJ datafile '+DATADG/ZDATADB/TIJIAN/bstj01.dbf' size 10G autoextend on;

--门户 portal
alter session set container=portal;
create tablespace PORTAL_PORTAL_NYEY datafile '+DATADG/ZDATADB/PORTAL/portal_nyey1.dbf' size 20G autoextend on;
create tablespace PORTAL_NYEY datafile '+DATADG/ZDATADB/PORTAL/portal_nyey2.dbf' size 20G autoextend on;

--lis
alter session set container=lis;
create tablespace TS_PATIENT datafile '+DATADG/ZDATADB/LIS/ts_patient01.dbf' size 5G autoextend on;
create tablespace ZJHIS datafile '+DATADG/ZDATADB/LIS/zjhis.dbf' size 3G autoextend on;
create tablespace TS_SJGL datafile '+DATADG/ZDATADB/LIS/tj_sjgl.dbf' size 550M autoextend on;
create tablespace TS_INDEX1 datafile '+DATADG/ZDATADB/LIS/ts_index1.dbf' size 200M autoextend on;
create tablespace TS_INDEX2 datafile '+DATADG/ZDATADB/LIS/ts_index2.dbf' size 20M autoextend on;
create tablespace TS_JYK datafile
'+DATADG/ZDATADB/LIS/ts_jyk01.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/LIS/ts_jyk02.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/LIS/ts_jyk03.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/LIS/ts_jyk04.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/LIS/ts_jyk05.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/LIS/ts_jyk06.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/LIS/ts_jyk07.dbf' size 30G autoextend on;
create tablespace TS_RESULT datafile '+DATADG/ZDATADB/LIS/ts_result.dbf' size 100M autoextend on;

--移动护理 ydhl
alter session set container=ydhl;
create tablespace TNS_LUNA datafile
'+DATADG/ZDATADB/YDHL/tns_luna01.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna02.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna03.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna04.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna05.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna06.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna07.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna08.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna09.dbf' size 30G autoextend on,
'+DATADG/ZDATADB/YDHL/tns_luna010.dbf' size 30G autoextend on;


2. 创建directory
sqlplus / as sysdba
alter session set container=lis;
create or replace directory dumplis as 'lis'; --这里只能指定相对路径，是相对于建立PDB时定义的PATH_PREFIX = '/u01/app/oracle/19300/dbs'而言
grant read,write on directory dumplis to public;

alter session set container=portal;
create or replace directory dumpportal as 'portal'; --这里只能指定相对路径，是相对于建立PDB时定义的PATH_PREFIX = '/u01/app/oracle/19300/dbs'而言
grant read,write on directory dumpportal to public;

alter session set container=tijian;
create or replace directory dumptijian as 'tijian'; --这里只能指定相对路径，是相对于建立PDB时定义的PATH_PREFIX = '/u01/app/oracle/19300/dbs'而言
grant read,write on directory dumptijian to public;

alter session set container=ydhl;
create or replace directory dumpydhl as 'ydhl'; --这里只能指定相对路径，是相对于建立PDB时定义的PATH_PREFIX = '/u01/app/oracle/19300/dbs'而言
grant read,write on directory dumpydhl to public;


########### 导出数据
--导出ydhl库
expdp "'sys/oracle as sysdba'" directory=movedata schemas=LUNA_MCS_NANYANG,WZHBL,LCF,MINHUI_HLDP,LUNA_MCS_HLDP,HANGCHUANG,HIS,WZH,ZABBIX,HIS_YZ,YGXT dumpfile=ydhl_%u.dmp parallel=4 logfile=exp_ydhl.log
expdp "'sys/oracle as sysdba'" directory=movedata schemas=LUNA_MCS_NANYANG,WZHBL,LCF,MINHUI_HLDP,LUNA_MCS_HLDP,HANGCHUANG,HIS,WZH,ZABBIX,HIS_YZ,YGXT dumpfile=ydhl_public.dmp logfile=exp_ydhl_public.log full=y include=DB_LINK,SYNONYM,TRIGGER,SEQUENCE,VIEW,MATERIALIZED_VIEW

--导出体检库
expdp "'sys/oracle as sysdba'" directory=movedata schemas=YDHL_SF_TJ,XDT,PORTAL_HSS,PACS dumpfile=histj.dmp logfile=exp_histj.log
expdp "'sys/oracle as sysdba'" directory=movedata schemas=YDHL_SF_TJ,XDT,PORTAL_HSS,PACS dumpfile=histj_public.dmp logfile=exp_histj_public.log full=y include=DB_LINK,SYNONYM,TRIGGER,SEQUENCE,VIEW,MATERIALIZED_VIEW

--导出potral库
expdp "'sys/oracle as sysdba'" directory=movedata schemas=portal dumpfile=portal.dmp logfile=exp_portal.log
expdp "'sys/oracle as sysdba'" directory=movedata schemas=portal dumpfile=portal_public.dmp logfile=exp_portal_public.log full=y include=DB_LINK,SYNONYM,TRIGGER,SEQUENCE,VIEW,MATERIALIZED_VIEW

--导出lis库
expdp "'sys/oracle as sysdba'" directory=movedata schemas=PORTAL_LIS,PROVINCE_INTERFACE_LIS,SM_LIS,YDHL_SF_LIS,SHPS_PDJH dumpfile=his_lis_%u.dmp parallel=4 logfile=exp_his_lis.log
expdp "'sys/oracle as sysdba'" directory=movedata schemas=PORTAL_LIS,PROVINCE_INTERFACE_LIS,SM_LIS,YDHL_SF_LIS,SHPS_PDJH dumpfile=his_lis_public.dmp logfile=exp_his_lis_public.log full=y include=DB_LINK,SYNONYM,TRIGGER,SEQUENCE,VIEW,MATERIALIZED_VIEW

--导出his库
exp_emr = /backupnew/portal_emr_bak
expdp "'sys/oracle as sysdba'" directory=exp_emr schemas=PORTAL_EMR,PROVINCE_INTERFACE_EMR,ZSYY_APP,HIS_DLL,BAOHUA,YDHL_SF,PASS,XLS_BYJ,OPERATOR1,PIVAS,PROVINCE_INTERFACE_HIS,YDCF,HIS_INTERFACE,ZDBYJ,OPERATOR2,VIEWER,MMC_USER,XTGL_NEW,SMUSER,YDHL_HIS,LQT,QSUSER,MHIS,NALONG,ZRYHJL,WANXI_JYJ,PACS_USER,BSOFT_TFY dumpfile=portal_emr_other_%u.dmp parallel=4 filesize=20g EXCLUDE=TABLE:\"IN\(\'EMR_BL03\',\'EMR_BL_BL02\'\)\" cluster=N logfile=exp_portal_emr_other.log

exp_his=/backupnew/portal_his_bak
expdp "'sys/oracle as sysdba'" directory=exp_his schemas=PORTAL_HIS dumpfile=portal_his_%u.dmp parallel=4 filesize=20g cluster=N logfile=exp_portal_his.log


########### 将导出的数据文件复制到zdata
--体检 tijian
winscp 复制到zdata的/u01/app/oracle/19300/dbs/tijian

--门户 portal
scp *.log *.dmp result.txt 172.16.31.33:/u01/app/oracle/19300/dbs/portal

--lis
winscp
ping 不同zdata，通过岳工主机中转
（在lis服务器中访问\\198.100.100.76\g\oradata\lis，把数据都复制到这里，然后再岳工机器上通过winscp复制到zdata的/home/oracle/lis中，
等移动护理库导入完成后将/u01/app/oracle/19300/dbs/ydhl中的数据删除，然后再将/home/oracle/lis中的内容复制到/u01/app/oracle/19300/dbs/lis中）

--移动护理 ydhl
winscp
能ping同zdata，但是tcp端口被占满导致ftp无法连接
netstat -ano > e:\1.txt
tasklist | findstr "0"
taskkill /f /im <进程名>
通过岳工主机中转
（在岳工机器上访问\\172.20.10.186\move,把其中的数据都复制到岳工主机的g:\oradata\ydhl目录下，然后再岳工机器上通过winscp复制到zdata的/u01/app/oracle/19300/dbs/ydhl中）

--his(emr除去)

########### 导入数据

--导入ydhl库
cd /u01/app/oracle/19300/dbs/ydhl
impdp "'sys/oracle@ydhl as sysdba'" directory=dumpydhl dumpfile=YDHL_%u.DMP parallel=4 schemas=LUNA_MCS_NANYANG,WZHBL,LCF,MINHUI_HLDP,LUNA_MCS_HLDP,HANGCHUANG,HIS,WZH,ZABBIX,HIS_YZ,YGXT table_exists_action=replace cluster=no logfile=imp_ydhl.log
impdp "'sys/oracle@ydhl as sysdba'" directory=dumpydhl dumpfile=YDHL_PUBLIC.DMP schemas=LUNA_MCS_NANYANG,WZHBL,LCF,MINHUI_HLDP,LUNA_MCS_HLDP,HANGCHUANG,HIS,WZH,ZABBIX,HIS_YZ,YGXT LOGFILE=imp_ydhl_public.log table_exists_action=replace cluster=no

--导入体检库
cd /u01/app/oracle/19300/dbs/tijian
impdp "'sys/oracle@tijian as sysdba'" directory=dumptijian dumpfile=HISTJ.DMP schemas=YDHL_SF_TJ,XDT,PORTAL_HSS,PACS table_exists_action=replace cluster=no logfile=imp_histj.log
impdp "'sys/oracle@tijian as sysdba'" directory=dumptijian dumpfile=HISTJ_PUBLIC.DMP schemas=YDHL_SF_TJ,XDT,PORTAL_HSS,PACS LOGFILE=imp_histj_public.log table_exists_action=replace cluster=no

--导入potral库
cd /u01/app/oracle/19300/dbs/portal
impdp "'sys/oracle@portal as sysdba'" directory=dumpportal dumpfile=portal.dmp schemas=portal table_exists_action=replace cluster=no logfile=imp_portal.log
impdp "'sys/oracle@portal as sysdba'" directory=dumpportal dumpfile=portal_public.dmp schemas=portal LOGFILE=imp_portal_public.log table_exists_action=replace cluster=no

在导入时遇到下面错误
ORA-31685: Object type TRIGGER:"PORTAL"."LOGIN_ON" failed due to insufficient privileges. Failing sql is:
CREATE TRIGGER "PORTAL".login_on
after logon on database
解决办法：grant administer database trigger to portal;

--导入lis库
rm -rf /u01/app/oracle/19300/dbs/ydhl/*
mv /home/oracle/lis/* /u01/app/oracle/19300/dbs/lis/
cd /u01/app/oracle/19300/dbs/lis
impdp "'sys/oracle@lis as sysdba'" directory=dumplis dumpfile=HIS_LIS_%u.DMP parallel=4 schemas=PORTAL_LIS,PROVINCE_INTERFACE_LIS,SM_LIS,YDHL_SF_LIS,SHPS_PDJH table_exists_action=replace cluster=no logfile=imp_his_lis.log
impdp "'sys/oracle@lis as sysdba'" directory=dumplis dumpfile=HIS_LIS_PUBLIC.DMP schemas=PORTAL_LIS,PROVINCE_INTERFACE_LIS,SM_LIS,YDHL_SF_LIS,SHPS_PDJH logfile=imp_his_lis_public.log table_exists_action=replace cluster=no




3. 在目标库上创建源库自建角色及角色拥有的权限脚本，expdp/impdb会导入用户拥有的角色、权限，但是如果用户拥有的角色是自己创建的(非oracle默认的)那么则不会创建，所以需要提前建好。
select 'create role ' || role || ';' from dba_roles where role not in
('CONNECT',
'RESOURCE',
'DBA',
'SELECT_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'RECOVERY_CATALOG_OWNER',
'GATHER_SYSTEM_STATISTICS',
'LOGSTDBY_ADMINISTRATOR',
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'GLOBAL_AQ_USER_ROLE',
'SCHEDULER_ADMIN',
'HS_ADMIN_ROLE',
'OEM_ADVISOR',
'OEM_MONITOR',
'WM_ADMIN_ROLE',
'DBFS_ROLE',
'DATAPUMP_EXP_FULL_DATABASE',
'DATAPUMP_IMP_FULL_DATABASE',
'ADM_PARALLEL_EXECUTE_TASK',
'HS_ADMIN_SELECT_ROLE',
'HS_ADMIN_EXECUTE_ROLE',
'JAVA_DEPLOY',
'JAVAUSERPRIV',
'JAVAIDPRIV',
'JAVASYSPRIV',
'JAVADEBUGPRIV',
'EJBCLIENT',
'JMXSERVER',
'JAVA_ADMIN',
'CTXAPP',
'XDBADMIN',
'XDB_SET_INVOKER',
'AUTHENTICATEDUSER',
'XDB_WEBSERVICES',
'XDB_WEBSERVICES_WITH_PUBLIC',
'XDB_WEBSERVICES_OVER_HTTP',
'OLAP_DBA',
'ORDADMIN',
'OLAP_XS_ADMIN',
'CWM_USER',
'OLAP_USER',
'SPATIAL_WFS_ADMIN',
'WFS_USR_ROLE',
'SPATIAL_CSW_ADMIN',
'CSW_USR_ROLE',
'APEX_ADMINISTRATOR_ROLE',
'OWB$CLIENT',
'OWB_DESIGNCENTER_VIEW',
'OWB_USER',
'OLAPI_TRACE_USER',
'MGMT_USER');


角色拥有的角色权限
select 'GRANT ' || GRANTED_ROLE || ' TO ' || ROLE || DECODE(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','') || ';'
FROM role_role_privs
WHERE role = 'EDWIN1ROLE'
ORDER BY role;

角色拥有的系统权限
SELECT 'GRANT ' || PRIVILEGE || ' TO ' || ROLE || DECODE(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','') || ';'
FROM ROLE_SYS_PRIVS
WHERE ROLE = 'EDWIN1ROLE'
ORDER BY ROLE;

角色拥有的对象权限
SELECT 'GRANT ' || PRIVILEGE || ' ON ' || TABLE_NAME || ' TO ' || ROLE || DECODE(GRANTABLE,'YES',' WITH GRANT OPTION','') || ';'
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'EDWIN1ROLE';



4. 导出源库数据
select username from dba_users where username not in
('SYS',
'SYSTEM',
'OUTLN',
'DIP',
'ORACLE_OCM',
'DBSNMP',
'APPQOSSYS',
'WMSYS',
'EXFSYS',
'CTXSYS',
'XDB',
'ANONYMOUS',
'XS$NULL',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'ORDDATA',
'ORDSYS',
'MDSYS',
'OLAPSYS',
'MDDATA',
'SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_USR',
'FLOWS_FILES',
'APEX_PUBLIC_USER',
'APEX_030200',
'OWBSYS',
'OWBSYS_AUDIT',
'SCOTT',
'SYSMAN',
'MGMT_VIEW'
);

--查看用户的默认表空间
set lines 132
col username for a25
col default_tablespace for a15
col temporary_tablespace for a15
select username,default_tablespace,temporary_tablespace from dba_users
where username in ();

create or replace directory movedata as '/home/oracle/move';
grant read,write on directory movedata to public;

--会同时导出用户拥有的角色、权限
expdp system/oracle directory=dumpdir schemas=edwin,edwin1 dumpfile=expdata.dmp logfile=expdata.log
--会导出public对象
expdp system/oracle directory=dumpdir dumpfile=expdata_public.dmp logfile=expdata_public.log full=y include=DB_LINK,SYNONYM,TRIGGER,SEQUENCE,VIEW,MATERIALIZED_VIEW


或者用sys用户导出
expdp "'sys/oracle as sysdba'" directory=DUMP_FILE schemas=PORTAL_LIS,PROVINCE_INTERFACE_LIS,SM_LIS,YDHL_SF_LIS,SHPS_PDJH dumpfile=his_lis_%u.dmp parallel=4 logfile=exp_his_lis.log
expdp "'sys/oracle as sysdba'" directory=DUMP_FILE dumpfile=his_lis_public.dmp logfile=exp_his_lis_public.log full=y include=DB_LINK,SYNONYM,TRIGGER,SEQUENCE,VIEW,MATERIALIZED_VIEW

5. 在目标端导入数据
--会自动创建用户,并且同时导入用户的角色、权限，用户密码和导出用户hr的密码一样
impdp system/oracle directory=dumpdir dumpfile=expdata.dmp schemas=edwin,edwin1 table_exists_action=truncate logfile=impdata.log
--导入public对象
impdp system/oracle directory=dumpdir dumpfile=expdata_public.dmp LOGFILE=impdata_public.log table_exists_action=replace

或者用sys用户导入
impdp "'sys/oracle as sysdba'" directory=dumpdir dumpfile=his_lis_%u.dmp parallel=4 schemas=PORTAL_LIS,PROVINCE_INTERFACE_LIS,SM_LIS,YDHL_SF_LIS,SHPS_PDJH table_exists_action=truncate logfile=imp_his_lis.log
impdp "'sys/oracle as sysdba'" directory=dumpdir dumpfile=his_lis_public.dmp LOGFILE=imp_his_lis_public.log table_exists_action=replace

--验证用户的默认表空间
set lines 132
col username for a25
col default_tablespace for a15
col temporary_tablespace for a15
select username,default_tablespace,temporary_tablespace from dba_users
where username in ();

6. 编译失效对象
@?/rdbms/admin/utlrp.sql

3. 创建用户及赋权(不用在执行，可以作为验证源端和目标端权限是否一致时使用)
set lines 132
col username for a10
col account_status for a30
col profile for a10
col INITIAL_RSRC_CONSUMER_GROUP for a30
col external_name for a10
set pages 50000
select username,account_status,lock_date,PROFILE,INITIAL_RSRC_CONSUMER_GROUP,EXTERNAL_NAME from dba_users;

SELECT 'CREATE USER ' || USERNAME || ' IDENTIFIED BY ' || LOWER(USERNAME) || ' DEFAULT TABLESPACE ' || DEFAULT_TABLESPACE || ';'
FROM dba_users
WHERE USERNAME IN ('ORASGUEST','ORASUSER','CCMSWEB','PATROL','RMANBAK');

select 'alter user ' || username || ' account lock' || ';' from dba_users where ACCOUNT_STATUS like 'EXPIRED % LOCKED';

用户拥有的角色
select 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || DECODE(ADMIN_OPTION,'YES','WITH ADMIN OPTION','') || ';'
FROM dba_role_privs
WHERE GRANTEE IN ('PORTAL')
ORDER BY GRANTEE;

用户拥有的系统权限
SELECT 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || DECODE(ADMIN_OPTION,'YES','WITH ADMIN OPTION','') || ';'
FROM DBA_sys_privs
WHERE GRANTEE IN ('PORTAL')
ORDER BY GRANTEE;

用户拥有的对象权限
SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || DECODE(GRANTABLE,'YES','WITH GRANT OPTION','') || ';'
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN ('PORTAL')
AND TABLE_NAME NOT IN (SELECT DIRECTORY_NAME FROM DBA_DIRECTORIES)
union all
SELECT 'GRANT ' || PRIVILEGE || ' ON DIRECTORY ' || TABLE_NAME || ' TO ' || GRANTEE || DECODE(GRANTABLE,'YES','WITH GRANT OPTION','') || ';'
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN ('PORTAL')
AND TABLE_NAME IN (SELECT DIRECTORY_NAME FROM DBA_DIRECTORIES);

6. 比较源端和目标端SYS用户下创建的对象，如果差了需要创建
--directory
col DIRECTORY_PATH for a60
set lines 200
select 'create or replace directory ' || directory_name || ' as ''' || directory_path || ''';'
from DBA_DIRECTORIES
where directory_name not in ('DUMPDIR','ORACLE_OCM_CONFIG_DIR2','ORACLE_OCM_CONFIG_DIR','DATA_PUMP_DIR');

select 'grant read,write on directory ' || directory_name || ' to public;' from DBA_DIRECTORIES;

--dblink
set lines 132
col owner for a10
col db_link for a20
col username for a20
col host for a30
col created for a20
set pages 50000
select owner,db_link,username,host,created from dba_db_links

select 'create public database link ' || db_link || ' connect to ' || username || ' identified by ' || lower(username) || ' using ''' || host || ''';'
from dba_db_links;

--synonym/trigger/MATERIALIZED_VIEW
set heading off
select 'select dbms_metadata.get_ddl(''SYNONYM'','''||SYNONYM_NAME||''','''||OWNER||''') from dual;'
from dba_synonyms
where owner='PUBLIC'
and table_owner not in ('SYS','SYSTEM','WMSYS','OUTLN','DIP','APPQOSSYS','ORACLE_OCM','DIP','XDB','SYSMAN','DBSNMP');

set heading off
select 'select dbms_metadata.get_ddl(''TRIGGER'','''||TRIGGER_NAME||''','''||OWNER||''') from dual;'
from dba_triggers
where owner not in ('SYS','SYSTEM','WMSYS','OUTLN','DIP','APPQOSSYS','ORACLE_OCM','DIP','XDB','SYSMAN','DBSNMP');

Set long 9999999999
Set lines 132
Set pages 300
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BA_BRSY$REORG','PORTAL_HIS') from dual;


7. 复制 listener.ora、tnsnames.ora 到目标端