数据库同步方案

1. 利用数据泵导出每表前500行数据

expdp tvpay2/tvpay directory=dmp dumpfile=20170508.dmp include=table query="‘where rownum<=500‘"

备注：利用exp导入每表前几行有限制

exp tvpay2/tvpay file=t.dmp tables=(emp,emp1) query="‘where rownum<=100‘"

查询该用户下所有表

SELECT object_name||‘,‘ as user_object from user_objects where object_type=‘TABLE‘;

###该方法如果表多的话，比较浪费时间

2. 查看数据泵目录

SELECT * FROM dba_directories;

OWNER                          DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS                            DMP

/u01/backup/dmp

SYS                            XMLDIR

/u01/app/oracle/product/11.2.0/db_1/rdbms/xml

SYS                            ORACLE_OCM_CONFIG_DIR

/u01/app/oracle/product/11.2.0/db_1/ccr/hosts/mt_pdb/state

OWNER                          DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS                            DATA_PUMP_DIR

/u01/app/oracle/admin/sumapay/dpdump/

SYS                            ORACLE_OCM_CONFIG_DIR2

/u01/app/oracle/product/11.2.0/db_1/ccr/state

3. 赋予expdp,impdp的权限

grant imp_full_database,exp_full_database to tvpay2;

4. 利用数据泵导入数据

impdp tvpay2/tvpay directory=DATA_PUMP_DIR dumpfile=20170508.dmp

###备注directory目录，以及dumpfile无目录

5. 参考

#使用数据泵导入、导出http://blog.csdn.net/xyz846/article/details/7649950

#数据泵参数详解http://blog.csdn.net/jojo52013145/article/details/7966047

数据库同步方案