整个用户导出

导出

expdp  \'system/oracle@127.0.0.1:1521/dcdb AS SYSDBA\' nologfile=y dumpfile=expdp.dmp directory=ORACLE_BASE schemas=dsp,dspmgr,security  query='where rownum < 1000'

导入

impdp  \'sys/Oradoc_db1@127.0.0.1:1521/orclpdb1.localdomain AS SYSDBA\' nologfile=y dumpfile=expdp.dmp directory=ORACLE_BASE schemas=dsp,dspmgr,security remap_schema=dspmgr:dspmgr,dsp:dsp,security:security remap_tablespace=TBS_DSP:users,TBS_DSP_SECURITY:users,TBS_DSP_MGR:users,TBS_DSP_TEMP:temp,TBS_DSP_IDX:users;

导入指定的表并指定where条件

导出

expdp  \'sys/caresoracle@172.28.11.1:1521/dcdb AS SYSDBA\' nologfile=y dumpfile=expdp.dmp directory=DPDATA1 tables=dsp.DW_FLIGHT_ACTIVITY,dsp.ODS_IMF_FLIGHT,dsp.DIM_PASSENGER query=dsp.DW_FLIGHT_ACTIVITY:\"where date_plan_flight \> to_date\(\'2020-04-06\',\'yyyy-MM-dd\'\) and date_plan_flight \< to_date\(\'2020-04-20\',\'yyyy-MM-dd\'\)\",query=dsp.ODS_IMF_FLIGHT:\"where flightscheduleddate \> to_date\(\'2020-04-06\',\'yyyy-MM-dd\'\) and flightscheduleddate \< to_date\(\'2020-04-20\',\'yyyy-MM-dd\'\)\",dsp.DIM_PASSENGER:\"where flightdate \> to_date\(\'2020-04-06\',\'yyyy-MM-dd\'\) and flightdate \< to_date\(\'2020-04-20\',\'yyyy-MM-dd\'\)\"

导入

impdp  \'sys/Oradoc_db1@127.0.0.1:1521/dcdb AS SYSDBA\' nologfile=y dumpfile=expdp.dmp directory=ORACLE_BASE table_exists_action=replace  remap_tablespace=TBS_DSP:users,TBS_DSP_SECURITY:users,TBS_DSP_MGR:users,TBS_DSP_TEMP:temp,TBS_DSP_IDX:users;

table_exists_action：

skip 如果已存在表，则跳过并处理下一个对象；

append 为表增加数据;

truncate 截断表，然后为其增加新数据；

replace 删除已存在表，重新建表并追加数据;

---