合 Oracle数据泵(expdp和impdp)导出导入常用写法
Tags： Oracle数据泵expdpimpdp逻辑导出导入常用SQLimpEXCLUDEexp排除表常用写法
👉 本文共约349个字，系统预计阅读时间或需2分钟。
Oracle 系统默认用户
1 2 3 4 5 6 7 8 9 | SELECT d.username, d.default_tablespace, d.account_status, 'create user '|| d.username|| ' identified by '|| d.username ||' default tablespace '||d.default_tablespace||';' FROM dba_users d WHERE d.username not in ('SYS','SYSTEM','PUBLIC','MDSYS','TSMSYS','DMSYS','DBSNMP','SCOTT','LHR','LHR2','DB_MONITOR','OUTLN','MGMT_VIEW','FLOWS_FILES','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_030200','APEX_050000','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','HR','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','APEX_040200','DVSYS','LBACSYS','GSMADMIN_INTERNAL','AUDSYS','OJVMSYS','SYS$UMF','GGSYS','DBSFWUSER','DVF','GSMCATUSER','SYSBACKUP','REMOTE_SCHEDULER_AGENT','GSMUSER','SYSRAC','SYSKM','SYSDG','PDBADMIN','WKSYS','GSMROOTUSER','CSMIG','WKPROXY','WK_TEST','SI_INFORMATN_SCHEMA'); |
常用SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | date +%Y%m%d %date:~0,4%%date:~5,2%%date:~8,2% set CurDate=%date:~0,4%%date:~5,2%%date:~8,2% set hh=%time:~0,2% if /i %hh% LSS 10 ( set hh=0%time:~1,1%) set ms=%time:~3,2%%time:~6,2% set my_date=%CurDate%%hh%%ms% grant read,write on directory DATA_PUMP_DIR to LHR; grant ALL on directory DATA_PUMP_DIR to LHR; windows下用：expdp "/ AS SYSDBA" -- -- 导出到服务端 expdp scott/tiger@orclasm directory=DATA_PUMP_DIR TABLES=EMP,DEPT dumpfile=expdp_by_lhrdate +%Y%m%d.dmp LOGFILE=expdp_by_lhrdate +%Y%m%d.log expdp scott/tiger@orclasm directory=DATA_PUMP_DIR TABLES=EMP,DEPT dumpfile=expdp_by_lhr%date:~0,4%%date:~5,2%%date:~8,2%.dmp LOGFILE=expdp_by_lhr%date:~0,4%%date:~5,2%%date:~8,2%.log -- 表级别 expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=TEST_TSPITR2,TEST_TSPITR3 LOGFILE=expdp_table.log expdp scott/tiger@orclasm directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=EMP,DEPT LOGFILE=expdp_table.log expdp system/lhr@orclasm DIRECTORY=DATA_PUMP_DIR DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:"IN ('EMP', 'DEPT')" impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log -- schema级别 expdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER LOGFILE=HR.log expdp system/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=test_20140324.DMP SCHEMAS=test logfile=test_expdp_20111014.log status=10 parallel=4 CONTENT=ALL COMPRESSION=ALL impdp lhr/lhr@orclasm directory=DATA_PUMP_DIR dumpfile=HR.dmp SCHEMAS=HR,SCOTT,TESTUSER parallel=4 LOGFILE=HR_20151125.log -- 整个数据库 expdp SYSTEM/ORACLE@ORCL DIRECTORY=DATA_PUMP_DIR DUMPFILE=TEST_20140324.DMP SCHEMAS=TEST LOGFILE=TEST_EXPDP_20111014.LOG STATUS=10 PARALLEL=1 CONTENT=ALL FLASHBACK_SCN=18341888 COMPRESSION=ALL expdp '/ AS SYSDBA' DIRECTORY=DATA_PUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLEXP.LOG PARALLEL=2 impdp '/ AS SYSDBA' DIRECTORY=DATA_PUMP_DIR FULL=Y DUMPFILE=FULLEXP.DMP LOGFILE=FULLIMP.LOG PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE EXCLUDE=STATISTICS,SCHEMA,TABLESPACE,ROLE,DIRECTORY,CONTEXT,PROFILE impdp LHR/LHR@ORCLASM DIRECTORY=DATA_PUMP_DIR DUMPFILE=HR.DMP SCHEMAS=HR,SCOTT,TESTUSER PARALLEL=4 LOGFILE=HR_20151125.LOG expdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=EXPDP_FULL_20150417.LOG EXCLUDE=STATISTICS impdp SYSTEM/LHR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG PARALLEL=4 EXCLUDE=STATISTICS:"IN('')" ORACLE_SID=ORA1024G impdp "/ AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG JOB_NAME=IMPDP_LHR EXCLUDE=SCHEMA,TABLESPACE,ROLE,DIRECTORY, CONTEXT,PROFILE PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE impdp "/ AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_FULL_20150417.DMP FULL=Y LOGFILE=IMPDP_FULL_20150417.LOG JOB_NAME=IMPDP_LHR EXCLUDE=TABLESPACE,ROLE,DIRECTORY, CONTEXT,PROFILE,USER,SCHEMA:"='SYS'",SCHEMA:"='IX'" PARALLEL=2 TABLE_EXISTS_ACTION=REPLACE -- 全库导出时的排除信息 more /tmp/exclude_schema.par EXCLUDE=STATISTICS,SCHEMA:"in ('SYSTEM','MDSYS','DBSNMP','SCOTT','OUTLN','MGMT_VIEW','FLOWS_FILES','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','HR','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')" -- 排除表时不能加用户名，否则会失效 EXCLUDE=STATISTICS,TABLE:"in('TIGER','TEST','TB2')" -- -- query选项 [ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par query=SCOTT.EMP:"WHERE DEPTNO=20",SCOTT.DEPT:"WHERE DNAME='SALES'" [ZFZHLHRDB1:oracle]:/oracle> expdp '/ AS SYSDBA' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log -- - include expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_03.dmp logfile=test_include_lhr_scott_03.log job_name=my_job_lhr include=procedure,function,sequence:"like '%TEST%'" include=procedure,function,sequence:"like '%TEST%'" include=procedure include=function include=sequence:"like '%TEST%'" expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_05.dmp logfile=test_include_lhr_scott_05.log job_name=my_job_lhr parfile=/tmp/parfile.par expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile='expdp_by_lhr%date:~0,4%%date:~5,2%%date:~8,2%.dmp' LOGFILE='expdp_by_lhr%date:~0,4%%date:~5,2%%date:~8,2%.log' job_name=my_job_lhr parfile=/tmp/parfile.par -- -- trace expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log trace=4a0300 --DATABASE_EXPORT_OBJECTS --SCHEMA_EXPORT_OBJECTS --TABLE_EXPORT_OBJECTS --SELECT * FROM DBA_EXPORT_OBJECTS D WHERE D.OBJECT_PATH LIKE '%DB_LINK%' ; -- -导出job expdp system/lhr dumpfile=dmplhr_JOB.dmp directory=data_pump_dir full=y include=JOB impdp system/lhr dumpfile=dmplhr_JOB.dmp directory=data_pump_dir full=y include=JOB SQLFILE=expddl_lhr.sql -- -导出USER expdp system/lhr dumpfile=dmplhr_USER.dmp directory=data_pump_dir full=y include=USER impdp system/lhr dumpfile=dmplhr_USER.dmp directory=data_pump_dir full=y include=USER SQLFILE=expddl_lhr.sql -- 导出dblink --所有dblink expdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir full=y include=db_link --私有dblink expdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir schemas=SYS,LHR include=db_link --公共dblink expdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir full=y include=db_link:"IN (SELECT DB_LINK FROM DBA_DB_LINKS WHERE OWNER = 'PUBLIC')" impdp system/lhr dumpfile=dmplhr_dblink.dmp directory=data_pump_dir full=y include=db_link SQLFILE=expddl_lhr.sql --filesize数据泵示例 expdp system/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416%U.dmp FILESIZE=1024000 impdp lhr/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416%U.dmp expdp system/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416%U.dmp FILESIZE=10g impdp lhr/lhr SCHEMAS=scott DIRECTORY=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:expdp_20190416%U.dmp -- -- -- -- 导出和导入物化视图 --不同步数据 TEST_MV是基表 TEST_MV_LHR是物化视图 expdp system/lhr dumpfile=mview4.dmp schemas=lhr include=TABLE:"IN ('TEST_MV_LHR')",materialized_view:"IN ('TEST_MV_LHR')" impdp system/lhr dumpfile=mview4.dmp --TEST_MV是基表 同步数据 expdp system/lhr dumpfile=mview5.dmp schemas=lhr include=TABLE:"IN ('TEST_MV_LHR','TEST_MV')",materialized_view:"IN ('TEST_MV_LHR')" impdp system/lhr dumpfile=mview4.dmp exec dbms_mview.refresh('TEST_MV_LHR','C'); ---导出HR用户下定义的公共同义词 单引号、双引号、小括号 都需要进行转义 expdp system/lhr dumpfile=dmplhr_syn.dmp directory=data_pump_dir full=y include=DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM DBA_SYNONYMS WHERE OWNER='PUBLIC' AND TABLE_OWNER='HR')" -- -- - 导出到本地 expdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp TABLES=xb_log_lhr network_link=dblk_orclasm LOGFILE=expdp_table.log impdp lhr/lhr@orclxp directory=DATA_PUMP_DIR dumpfile=exptable.dmp LOGFILE=impdp_table.log -- 直接导入 不生成文件 impdp lhr/lhr@orclxp network_link=dblk_orclasm directory=DATA_PUMP_DIR TABLES=xb_log_lhr PARALLEL=2 LOGFILE=impdp_table.log -- 生成ddl语句 不会导入数据 metadata_only表示不导出表中数据，但是其它元数据会导出的，包括存储过程、函数定义等 --expdp '/ AS SYSDBA' tables=lhr.exptest directory=DATA_PUMP_DIR dumpfile=exptest.dmp logfile=exp_exptest.dmp EXCLUDE=STATISTICS --expdp '/ AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=lhrsql20161215.log content=metadata_only schemas=SCOTT,HR EXCLUDE=STATISTICS --expdp '/ AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=lhrsql20161215.log content=metadata_only FULL=Y EXCLUDE=STATISTICS impdp '/ AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=lhrsql20161215.dmp logfile=imp_exptest.log sqlfile=exptest.sql set pagesize 0 set trimspool ON SET linesize 10000 set 90000 set feedback OFF set feed off; set echo off spool schema_scott.sql SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME,U.owner) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION'，'PACKAGE','TRIGGER') AND U.owner='SCOTT'; spool off; -- 只导出表结构 expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql TRANSFORM=segment_attributes:n --transform=segment_attributes|storage|SEGMENT_CREATION|oid|pctspace：Y/N:object_type -- 修改对象schema和tablespace impdp test/test directory=exp_dump dumpfile=test.dmp logfile=test.log remap_schema=test1:test2 remap_tablespace=TBS_DAT_1:TBS_DAT_2,TBS_IDX_1:TBS_IDX_2 impdp test/test directory=exp_dump dumpfile=test.dmp logfile=test.log remap_schema=test1:test2 remap_tablespace=TBS_DAT_1:TBS_DAT_2 remap_tablespace=TBS_IDX_1:TBS_IDX_2 -- 显示时间 metrics=y dmp文件重用 reuse_dumpfiles=y expdp \'/ AS SYSDBA\' directory=D1 dumpfile=orcl_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_orcl_20221111.log \ SCHEMAS=AB,LHR,TEST CLUSTER=N COMPRESSION=ALL parallel=32 FILESIZE=10g metrics=y reuse_dumpfiles=y |
exp和imp
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | --------------------------- exp和imp grant exp_full_database to lhr; Linux: exp scott/tiger tables=emp query="where job='SALESMAN' and sal<1600" Windows exp userid=tkyte/tkyte tables=t query="""where object_id < 5000""" 在windows中，需要在WHERE语句的两端使用三个双引号 --EXP-00091: Exporting questionable statistics 的方法 select userenv('language') from dual; ---->>> NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 或加上： STATISTICS=NONE ------ query选项 exp '/ AS SYSDBA' tables=test_query_lhr file=/tmp/test_query_lhr_scott.dmp query=" where owner='SCOTT' " log=/tmp/test_query_lhr_scott.log [ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par query="where owner='SCOTT'" [ZFZHLHRDB1:oracle]:/oracle> exp '/ AS SYSDBA' tables=test_query_lhr file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log ------ parfile选项 [ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par tables=scott.emp,scott.dept exp '/ AS SYSDBA' file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log strace exp n1/n1 tables=scott.emp file=a.dmp exp cnydm/cnydm@DATAWDB_125 file=d:/oracle_bk/cnydm20150402.dmp log=d:/oracle_bk/cnydm20150402.log buffer=50000000 tables=PRD_CTGRY_D,DSCNT_TP_D,MKT_AND_PRD_CTGRY_D,MKT_CTGRY_D imp cnydm/cnydm@DATAWDB_125 file=d:/oracle_bk/cnydm20150402.dmp log=d:/oracle_bk/imp_cnydm20150402.log buffer=50000000 full=y exp system/lhr file=E:\expfull.dmp full=y log=E:\expfull.log imp system/lhr file=E:\expfull.dmp full=y log=E:\impfull.log exp lhr/lhr@orclasm tables=xb_log_lhr,xb_a,xb_b file=e:\e1.dmp log=E:\exp_table.log buffer=41943040 imp lhr/lhr@winxp tables=xb_log_lhr file=e:\e1.dmp log=E:\exp_table.log buffer=41943040 imp lhr/lhr@orclasm tables=(emp,dept) file=d:\e1.dmp log=E:\exp_table_.log buffer=41943040 -- filesize参数 exp userid=scott/tiger file=/tmp/test1,/tmp/test2,/tmp/test3,/tmp/test4,/tmp/test5 filesize=1024000 log=test.log imp userid=lhr/lhr FILE=/tmp/test1,/tmp/test2,/tmp/test3,/tmp/test4,/tmp/test5 LOG=imp_emp.log FROMUSER=scott TOUSER=lhr TABLES=emp -- 只导出导入表结构 exp '/ AS SYSDBA' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n imp '/ AS SYSDBA' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y exp system/lhr file=E:\expfull2.dmp log=E:\expfull2.log owner=(lhrexp,lhrimp) imp system/lhr file=E:\expfull2.dmp full=y log=E:\expfull2.log imp "sys as sysdba" file=testmv_full.dmp full=y buffer=41943040 feedback=10000 log=testmv_full.log imp user2/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 feedback=10000 buffer=41943040 log=testmv_full.log |
生产环境下，oracle 9I下sga大概8G，pga大概6g，需要导入一个2.7g以上的大表到成产库中，由于是同事着手运用imp工具的默认buffer=30K，用时大概一个小时还没有结果，考虑到pga还是很大的，跟同事商议加上buffer=409600000设置buffer大概400M的，15分钟内imp完成。当然运用impdp然后运用parallel=n效率当然更加理想了！
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
