排除某些表的expdp/impdp
expdp system/123456 directory=DATA_PUMP_DIR dumpfile=wf.dmp logfile=wf.log schemas=WINWF EXCLUDE=TABLE:\"IN\(\'TABLE_NAME1\'\)\"
expdp system/123456 directory=DATA_PUMP_DIR dumpfile=wf.dmp logfile=wf.log schemas=WINWF EXCLUDE=TABLE:\"IN\(\'TABLE_NAME1\',\'TABLE_NAME2\',\'TABLE_NAME3\'\)\"
例子
expdp用户dh,用户没有expdp权限,tablespace为dh,但是排除IERP_WO_SN_MATERIAL_INFO等5张表,知道dh用户密码,不知道system的密码,只能使用as system来替代
expdp \"db/dahai_2013 as sysdba\" schemas=dh directory=DUMPDIR dumpfile=2015dh.dmp EXCLUDE=TABLE:\"IN\(\'IERP_WO_SN_MATERIAL_INFO\',\'IERP_WO_SN_USER\',\'WIP_SN_COLLECTION_INFO\',\'IERP_ERROR_TRACKING_INFO\',\'COMMON_MAIL_NOTICE\'\)\"
impdp到schema为dahai_demo,tablespace为dahaidata_demo的用户,已经排除某些表的2015dh.dmp文件的impdp导入,和一般的导入一样
impdp SYSTEM/mds_ets.2009 remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo dumpfile=2015dh.dmp directory=DUMPDIR
query查询条件的expdp/impdp
按查询条件只导出一张表,发现不需要加schema=的条件,只需要在tables=前面加上schema即可
expdp \"db/dahai_2013 as sysdba\" directory=DUMPDIR dumpfile=2015table1.dmp tables=DH.IERP_WO_SN_MATERIAL_INFO query=DH.IERP_WO_SN_MATERIAL_INFO:\" where creation_date\>"'23-SEP-15'"\"
按查询条件也可以同时导出两张表,只需要在tables=中逗号隔开两表,query=中逗号隔开两张表的查询条件即可
expdp \"db/dahai_2013 as sysdba\" directory=DUMPDIR dumpfile=2015table2.dmp tables=DH.IERP_WO_SN_USER,DH.WIP_SN_COLLECTION_INFO query=DH.IERP_WO_SN_USER:\" where creation_date\>"'23-SEP-15'"\",DH.WIP_SN_COLLECTION_INFO:\" where creation_date\>"'23-SEP-15'"\"
例子
导出A服务器cux用户下面的两张表,再导入到B服务器cux用户下同样的表名,内容追加进去,tablespace不变
expdp \"system/Q2O0racl4syst4m as sysdba\" directory=ODPDIR dumpfile=201611182.dmp tables=cux.cux_msc_woip_supplys,cux.cux_msc_woip_plans query=cux.cux_msc_woip_supplys:\" where request_id = 20852081\",cux.cux_msc_woip_plans:\" where request_id = 20852081\"
impdp SYSTEM/manager dumpfile=201611182.dmp directory=ODPDIR table_exists_action=append
按查询条件的dmp文件导入,和一般的导入一样(加不加tables=都无所谓)
以下导入一张表DH.IERP_WO_SN_MATERIAL_INFO含有查询条件的dmp文件,加tables=DH.IERP_WO_SN_MATERIAL_INFO正常导入
impdp SYSTEM/mds_ets.2009 remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo dumpfile=2015table1.dmp directory=DUMPDIR tables=DH.IERP_WO_SN_MATERIAL_INFO
以下导入两张张表DH.IERP_WO_SN_USER,DH.WIP_SN_COLLECTION_INFO含有查询条件的dmp文件,不加tables=发现也可以导入
impdp SYSTEM/mds_ets.2009 remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo dumpfile=2015table2.dmp directory=DUMPDIR
expdp system/123456 directory=DATA_PUMP_DIR dumpfile=wf.dmp logfile=wf.log schemas=WINWF EXCLUDE=TABLE:\"IN\(\'TABLE_NAME1\'\)\"
expdp system/123456 directory=DATA_PUMP_DIR dumpfile=wf.dmp logfile=wf.log schemas=WINWF EXCLUDE=TABLE:\"IN\(\'TABLE_NAME1\',\'TABLE_NAME2\',\'TABLE_NAME3\'\)\"
例子
expdp用户dh,用户没有expdp权限,tablespace为dh,但是排除IERP_WO_SN_MATERIAL_INFO等5张表,知道dh用户密码,不知道system的密码,只能使用as system来替代
expdp \"db/dahai_2013 as sysdba\" schemas=dh directory=DUMPDIR dumpfile=2015dh.dmp EXCLUDE=TABLE:\"IN\(\'IERP_WO_SN_MATERIAL_INFO\',\'IERP_WO_SN_USER\',\'WIP_SN_COLLECTION_INFO\',\'IERP_ERROR_TRACKING_INFO\',\'COMMON_MAIL_NOTICE\'\)\"
impdp到schema为dahai_demo,tablespace为dahaidata_demo的用户,已经排除某些表的2015dh.dmp文件的impdp导入,和一般的导入一样
impdp SYSTEM/mds_ets.2009 remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo dumpfile=2015dh.dmp directory=DUMPDIR
query查询条件的expdp/impdp
按查询条件只导出一张表,发现不需要加schema=的条件,只需要在tables=前面加上schema即可
expdp \"db/dahai_2013 as sysdba\" directory=DUMPDIR dumpfile=2015table1.dmp tables=DH.IERP_WO_SN_MATERIAL_INFO query=DH.IERP_WO_SN_MATERIAL_INFO:\" where creation_date\>"'23-SEP-15'"\"
按查询条件也可以同时导出两张表,只需要在tables=中逗号隔开两表,query=中逗号隔开两张表的查询条件即可
expdp \"db/dahai_2013 as sysdba\" directory=DUMPDIR dumpfile=2015table2.dmp tables=DH.IERP_WO_SN_USER,DH.WIP_SN_COLLECTION_INFO query=DH.IERP_WO_SN_USER:\" where creation_date\>"'23-SEP-15'"\",DH.WIP_SN_COLLECTION_INFO:\" where creation_date\>"'23-SEP-15'"\"
例子
导出A服务器cux用户下面的两张表,再导入到B服务器cux用户下同样的表名,内容追加进去,tablespace不变
expdp \"system/Q2O0racl4syst4m as sysdba\" directory=ODPDIR dumpfile=201611182.dmp tables=cux.cux_msc_woip_supplys,cux.cux_msc_woip_plans query=cux.cux_msc_woip_supplys:\" where request_id = 20852081\",cux.cux_msc_woip_plans:\" where request_id = 20852081\"
impdp SYSTEM/manager dumpfile=201611182.dmp directory=ODPDIR table_exists_action=append
按查询条件的dmp文件导入,和一般的导入一样(加不加tables=都无所谓)
以下导入一张表DH.IERP_WO_SN_MATERIAL_INFO含有查询条件的dmp文件,加tables=DH.IERP_WO_SN_MATERIAL_INFO正常导入
impdp SYSTEM/mds_ets.2009 remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo dumpfile=2015table1.dmp directory=DUMPDIR tables=DH.IERP_WO_SN_MATERIAL_INFO
以下导入两张张表DH.IERP_WO_SN_USER,DH.WIP_SN_COLLECTION_INFO含有查询条件的dmp文件,不加tables=发现也可以导入
impdp SYSTEM/mds_ets.2009 remap_schema=dh:dahai_demo remap_tablespace=dh:dahaidata_demo dumpfile=2015table2.dmp directory=DUMPDIR