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

麻烦把生产环境

数据库地址 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表空间中

 

 

 

 

 

REMAP_TABLESPACE=ECC_CFS:THOUSEPP

 

恢复用户的时候表空间盛满了

 

 

 

扩容表空间语句

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