数据泵：同步异库之间的用户数据

麻烦把生产环境

数据库地址 10.8.6.127

数据库名称：nppmdb

数据库用户名：ecc_cfs

备份到 10.66.220.44 （新建一个用户：ecc_cfs_20200417

10.8.6.127

查询当前实例

select instance_name from v$instance;

nppmdb

查看是否存在用户

select username from dba_users order by created;

ECC_CFS

查看dumpfile位置

select * from dba_directories;

导出

expdp "'/ as sysdba'" dumpfile=ecc_cfs20200417.dmp directory=DATA_PUMP_DIR schemas=ecc_cfs logfile=20200417.logfile

scp传输：scp /oracle/product/10.2.0/rdbms/logfile=20200417.logfile root@10.66.220.44:/oracle/admin/thousepp/dpdump/

10.66.220.44

查看当前实例

select instance_name from v$instance;

thousepp

查看dumpfile位置

select * from dba_directories;

导入

impdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=ecc_cfs20200417.dmp REMAP_TABLESPACE=ECC_CFS:THOUSEPP REMAP_SCHEMA=ecc_cfs:ecc_cfs_20200417 logfile=20200417.logfile

修改用户密码

alter user ecc_cfs_20200417 identified by 123456;

更换表空间

impdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=ecc_cfs20200417.dmp REMAP_TABLESPACE=ECC_CFS:THOUSEPP REMAP_SCHEMA=ecc_cfs:ecc_cfs_20200417 logfile=20200417.logfile

扩容表空间语句

ALTER TABLESPACE THOUSEPP ADD DATAFILE '/oracle/oradata/thousepp/thousepp06.dbf' SIZE 31G;

遇到的问题：

ORA-39112: dependent object type comment skipped

导入的新用户不存在ecc.cfs表空间，需要remap到thousepp表空间中