Oracle数据导入导出

来自ling
跳转至: 导航搜索
#进入sqlplus创建directory,如果已有,不用此步骤
#CREATE TABLESPACE TBS_VAT_TX_DATA   DATAFILE 'C:\oracle\product\10.2.0\oradata\tmsinf2.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
alter database datafile 'C:\oracle\product\10.2.0\oradata\TMSINF2.DBF' autoextend on maxsize unlimited;
expdp system/11111111@orcl schemas=tmsinf dumpfile =tmsinf.dmp logfile=tmsinf.log version=10.2;
impdp tmsinf/tmsinf@orcl dumpfile =tmsinf.dmp logfile=tmsinf.log;    指定schemas SCHEMAS=scott
你是想在导入数据时,将同一表名的,同一表结构的,导入时,将原有表中记录清空?还是想将同一表名的,不论是否同一表结构的,导入时,      将原有表删除,导入新的?
如果是前者,在 impdp  导入时,指定 TABLE_EXISTS_ACTION= TRUNCATE。
如果是后者,在impdp 导入时,指定  TABLE_EXISTS_ACTION= REPLACE
expdp TMSPROD/TMSPROD@ORCL schemas=TMSPROD dumpfile =TMSPROD_ALL.dmp logfile=TMSPROD_ALL.log
impdp TMSPROD/TMSPROD@ORCL dumpfile =TMSPROD_ALL.dmp logfile=TMSPROD_ALL_IMPDP.log table_exists_action=replace


expdp system/11111111@orcl schemas=tmsvat dumpfile =tmsvat.dmp logfile=tmsvat.log version=10.2;
impdp tmsvat/tmsvat@orcl dumpfile =tmsvat.dmp logfile=tmsvat.log;


sqlplus system/orcl as sysdba;
create directory orcl_dp as 'D:\oracle_db_bark';
exit;
#cmd命名模式下
expdp system/orcl@orcl directory=orcl_dp schemas=program dumpfile =program_2014_08_13.dmp logfile=program_2014_08_13_exp.log;
expdp system/orcl@orcl directory=orcl_dp schemas=agent dumpfile =agent_2014_08_13.dmp logfile=agent_2014_08_13_exp.log;
expdp system/orcl@orcl directory=orcl_dp schemas=CHANNEL dumpfile =CHANNEL_2014_08_13.dmp logfile=CHANNEL_2014_08_13_exp.log;
expdp system/orcl@orcl directory=orcl_dp schemas=xingye dumpfile =xingye_2014_08_13.dmp logfile=xingye_2014_08_13_exp.log;
expdp system/orcl@orcl directory=orcl_dp schemas=fw dumpfile =fw_2014_08_13.dmp logfile=fw_2014_08_13_exp.log;
expdp system/orcl@orcl directory=orcl_dp schemas=ybtrain dumpfile =ybtrain_2014_08_13.dmp logfile=ybtrain_2014_08_13_exp.log;
#导入
impdp system/orcl@orcl directory=orcl_dp dumpfile =program_2014_08_13.dmp logfile=program_2014_08_13_imp.log;
impdp system/orcl@orcl directory=orcl_dp remap_schema=channel:agent dumpfile =channel_2014_08_13.dmp logfile=channel_2014_08_13_imp.log;
impdp system/orcl@orcl directory=orcl_dp dumpfile =xingye_2014_08_13.dmp logfile=xingye_2014_08_13_imp.log;
impdp system/orcl@orcl directory=orcl_dp dumpfile =fw_2014_08_13.dmp logfile=fw_2014_08_13_imp.log;
impdp system/orcl@orcl directory=orcl_dp dumpfile =ybtrain_2014_08_13.dmp logfile=ybtrain_2014_08_13_imp.log;
impdp system/orcl@orcl directory=orcl_dp remap_schema=agent:channel dumpfile =agent_2014_08_13.dmp logfile=agent_2014_08_13_imp.log;
;

表空间和用户创建

参考oracle表空间和用户创建

docker中导入导出

docker常用服务#oracle