oracle EXP and IMP

 

select * from dba_directories where directory_name='DATA_PUMP_DIR';
expdp tecdoc_2021_2/tecdoc007@YIPARTS tablespaces=tecdoc_2021_2 dumpfile=exp_tecdoc_2021_2.dmp directory=DATA_PUMP_DIR
impdp tecdoc_2021_3/tecdoc007@YIPARTS directory=DATA_PUMP_DIR dumpfile=exp_tecodc_2021_2.dmp REMAP_SCHEMA=tecdoc_2021_2:tecdoc_2021_3 remap_tablespace=tecdoc_2021_2:tecdoc_2021_3,EXAMPLE_TEMP:tmp_tecodc_2021_3

查看DATA_PUMP_DIR 目录

select * from dba_directories

exp user/password@SID file=e:\file.dmp owner(user)

imp user/password file=e:\file.dmp fromuser=user touser=user

只导出结构

exp username/pwd@orcl file=f:/backup.dmp owner=user rows=n 

换表空间与用户名

按表空间导出；
expdp sys/passwd@orcl tablespaces=tbs1,tbs2 dumpfile=expdp.dmp directory=DATA_PUMP_DIR logfile=expdp.log;

schemas按用户导出；
expdp user/passwd@orcl schemas=user dumpfile=expdp.dmp directory=data_dir logfile=expdp.log;

导出表；
expdp user/passwd@orcl tables=table1,table2 dumpfile=expdp.dmp directory=data_dir logfile=expdp.log;

按查询条件导；
expdp user/passwd@orcl tables=table1='where number=1234' dumpfile=expdp.dmp directory=data_dir logfile=expdp.log;

“full=y”，全量导出数据库；
expdp user/passwd@orcl dumpfile=expdp.dmp directory=data_dir full=y logfile=expdp.log;

文件拷到oracle安装路径下即可：\admin\orcl\dpdump (可查看 select * from dba_directories where directory_name='DATA_PUMP_DIR';)

换表空间，用户名
impdp user/pass@SID directory=DATA_PUMP_DIR dumpfile=data.dmp REMAP_SCHEMA=olduser:newuser remap_tablespace=oldspace:newspace,EXAMPLE_TEMP:temp_newspace

“full=y”，全量导入数据库；
impdp user/passwd directory=data_dir dumpfile=expdp.dmp full=y;

同名用户导入，从用户A导入到用户A；
impdp A/passwd schemas=A directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;

从A用户中把表table1和table2导入到B用户中；
impdp B/passwd tables=A.table1,A.table2 remap_schema=A:B directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;

将表空间TBS01、TBS02、TBS03导入到表空间A_TBS，将用户B的数据导入到A，并生成新的oid防止冲突；
impdp A/passwd remap_tablespace=TBS01:A_TBS,TBS02:A_TBS,TBS03:A_TBS remap_schema=B:A FULL=Y transform=oid:n directory=data_dir dumpfile=expdp.dmp logfile=impdp.log

导入表空间；
impdp sys/passwd tablespaces=tbs1 directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;

追加数据；
impdp sys/passwd directory=data_dir dumpfile=expdp.dmp schemas=system table_exists_action=replace logfile=impdp.log;
--table_exists_action:导入对象已存在时执行的操作。有效关键字:SKIP,APPEND,REPLACE和TRUNCATE

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

换用户按空间导表

impdp aaa_2018_2/pass7 tables=TEST.article_number remap_schema=test:aaa2018_2 directory=DATA_PUMP_DIR dumpfile=ARTNUMBER.DMP logfile=ARTNUMBER.log remap_tablespace=test:aaa_2018_2,EXAMPLE_TEMP:tmp_aaa_2018_2;