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;
;