Oracle数据泵(expdp和impdp)导出导入常用写法
Tags： EXCLUDEexpexpdpimpimpdpOracle常用SQL常用写法排除表数据泵逻辑导出导入
👉 本文共约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(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
生产环境下，oracle 9I下sga大概8G，pga大概6g，需要导入一个2.7g以上的大表到成产库中，由于是同事着手运用imp工具的默认buffer=30K，用时大概一个小时还没有结果，考虑到pga还是很大的，跟同事商议加上buffer=409600000设置buffer大概400M的，15分钟内imp完成。当然运用impdp然后运用parallel=n效率当然更加理想了！
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 | [ZFZHLHRDB1:oracle]:/tmp>more /tmp/gettabddl.sh awk ' / "BEGIN / { N=1; } / "CREATE / { N=1; } / "CREATE INDEX/ { N=1; } / "CREATE UNIQUE INDEX/ { N=1; } / "ALTER / { N=1; } / " ALTER / { N=1; } / "ANALYZE / { N=1; } / "GRANT / { N=1; } / "COMMENT / { N=1; } / "AUDIT / { N=1; } N1 { printf "\n/\n"; N++ } /"$/ { if (N0) next; s=index( $0, """ ); ln0=length( $0 ) if ( s!=0 ) { lcnt++ if ( lcnt >= 30 ) { ln=substr( $0,s+1,length( substr($0,s+1))-1) t=index( ln, ")," ) if ( t0 ) { t=index( ln, ", " ) } if ( t0 ) { t=index( ln, ") " ) } if ( t > 0 ) { printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2) lcnt=0 } else { printf "%s", ln if ( ln0 < 78 ) { printf "\n" ; lcnt=0 } } } else { printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 ) if ( ln0 < 78 ) { printf "\n" ; lcnt=0 } } } } END { printf "\n/\n"} ' $* |sed '1,2d; /^$/ d; s/STORAGE *(INI/~ STORAGE (INI/g; s/, "/,~ "/g; s/ ("/~ &/g; s/PCT[FI]/~ &/g; s/[( ]PARTITION /~&/g; s/) TABLESPACE/)~ TABLESPACE/g; s/ , / ,~/g; s/ DATAFILE /&~/' | tr "~" "\n" [ZFZHLHRDB1:oracle]: /tmp [ZFZHLHRDB1:oracle]:/tmp>ksh /tmp/gettabddl.sh /tmp/get_ddl.sql > /tmp/gen_tabddl.sql |
导出ASH视图
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 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 | -------------------------------------------- 导出ASH视图的数据 ash数据 --- 方法1：ctas建表导出 有的客户不让建表 CREATE TABLE ASH_TEMP_20161117 NOLOGGING AS SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY D WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2016-11-10 02:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2016-11-17 06:00:00', 'YYYY-MM-DD HH24:MI:SS') ; exp '/ AS SYSDBA' tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/ASH_TEMP_20161117.log buffer=41943040 imp lhr/lhr tables=ASH_TEMP_20161117 file=/tmp/ASH_TEMP_20161117.dmp log=/tmp/imp_ASH_TEMP_20161117.log buffer=41943040 --- 方法2：导出基表的数据 ---more /tmp/exp_ash_lhr_01.par query="WHERE SAMPLE_TIME BETWEEN TO_DATE('2016-12-02 08:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2016-12-08 23:38:00', 'YYYY-MM-DD HH24:MI:SS')" exp '/ AS SYSDBA' tables='WRH$ACTIVE_SESSION_HISTORY' file=/tmp/exp_ash_lhr_01.dmp parfile=/tmp/exp_ash_lhr_01.par log=/tmp/exp_ash_lhr_01.log GRANTS=N CONSTRAINTS=N STATISTICS=NONE exp '/ AS SYSDBA' tables='WRM$SNAPSHOT','WRH$EVENT_NAME','WRH$SQLCOMMAND_NAME','WRH$PLAN_OPERATION_NAME','WRH$PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' file=/tmp/exp_ash_lhr_02.dmp log=/tmp/exp_ash_lhr_02.log GRANTS=N CONSTRAINTS=N STATISTICS=NONE imp lhr/lhr file=/tmp/exp_ash_lhr_01.dmp tables='WRH$ACTIVE_SESSION_HISTORY' log=/tmp/imp_ash_lhr_01.log FROMUSER=SYS TOUSER=LHR imp lhr/lhr file=/tmp/exp_ash_lhr_02.dmp tables='WRM$SNAPSHOT','WRH$EVENT_NAME','WRH$SQLCOMMAND_NAME','WRH$PLAN_OPERATION_NAME','WRH$PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' log=/tmp/imp_ash_lhr_02.log FROMUSER=SYS TOUSER=LHR DROP TABLE LHR.WRH$ACTIVE_SESSION_HISTORY PURGE; DROP TABLE LHR.WRM$SNAPSHOT PURGE; DROP TABLE LHR.WRH$EVENT_NAME PURGE; DROP TABLE LHR.WRH$SQLCOMMAND_NAME PURGE; DROP TABLE LHR.WRH$PLAN_OPERATION_NAME PURGE; DROP TABLE LHR.WRH$PLAN_OPTION_NAME PURGE; DROP TABLE LHR.WRH$_TOPLEVELCALL_NAME PURGE; create or replace view dh_ash_11g_lhr (snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated) as select /* ASH/AWR meta attributes / ash.snap_id, ash.dbid, ash.instance_number, ash.sample_id, ash.sample_time, / Session/User attributes / ash.session_id, ash.session_serial#, decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'), ash.flags, ash.user_id, / SQL attributes / ash.sql_id, decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), ash.sql_child_number, ash.sql_opcode, (select command_name from WRH$_SQLCOMMAND_NAME where command_type = ash.sql_opcode and dbid = ash.dbid) as sql_opname, ash.force_matching_signature, decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id), decode(ash.top_level_sql_id, NULL, ash.sql_opcode, ash.top_level_sql_opcode), / SQL Plan/Execution attributes / ash.sql_plan_hash_value, decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id), (select operation_name from WRH$PLAN_OPERATION_NAME where operation_id = ash.sql_plan_operation# and dbid = ash.dbid) as sql_plan_operation, (select option_name from WRH$PLAN_OPTION_NAME where option_id = ash.sql_plan_options# and dbid = ash.dbid) as sql_plan_options, decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id), ash.sql_exec_start, / PL/SQL attributes / decode(ash.plsql_entry_object_id,0,to_number(NULL), ash.plsql_entry_object_id), decode(ash.plsql_entry_object_id,0,to_number(NULL), ash.plsql_entry_subprogram_id), decode(ash.plsql_object_id,0,to_number(NULL), ash.plsql_object_id), decode(ash.plsql_object_id,0,to_number(NULL), ash.plsql_subprogram_id), / PQ attributes / decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id), decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id), decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#), decode(ash.px_flags, 0, to_number(NULL), ash.px_flags), / Wait event attributes / decode(ash.wait_time, 0, evt.event_name, NULL), decode(ash.wait_time, 0, evt.event_id, NULL), ash.seq#, evt.parameter1, ash.p1, evt.parameter2, ash.p2, evt.parameter3, ash.p3, decode(ash.wait_time, 0, evt.wait_class, NULL), decode(ash.wait_time, 0, evt.wait_class_id, NULL), ash.wait_time, decode(ash.wait_time, 0, 'WAITING', 'ON CPU'), ash.time_waited, (case when ash.blocking_session = 4294967295 then 'UNKNOWN' when ash.blocking_session = 4294967294 then 'GLOBAL' when ash.blocking_session = 4294967293 then 'UNKNOWN' when ash.blocking_session = 4294967292 then 'NO HOLDER' when ash.blocking_session = 4294967291 then 'NOT IN WAIT' else 'VALID' end), (case when ash.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else ash.blocking_session end), (case when ash.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else ash.blocking_session_serial# end), (case when ash.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else ash.blocking_inst_id end), (case when ash.blocking_session between 4294967291 and 4294967295 then NULL else decode(bitand(ash.flags, power(2, 3)), NULL, 'N', 0, 'N', 'Y') end), / Session's working context / ash.current_obj#, ash.current_file#, ash.current_block#, ash.current_row#, ash.top_level_call#, (select top_level_call_name from WRH$_TOPLEVELCALL_NAME where top_level_call# = ash.top_level_call# and dbid = ash.dbid) as top_level_call_name, decode(ash.consumer_group_id, 0, to_number(NULL), ash.consumer_group_id), ash.xid, decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#), ash.time_model, decode(bitand(ash.time_model,power(2, 3)),0,'N','Y') as in_connection_mgmt, decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse, decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse, decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution, decode(bitand(ash.time_model,power(2,11)),0,'N','Y') as in_plsql_execution, decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc, decode(bitand(ash.time_model,power(2,13)),0,'N','Y') as in_plsql_compilation, decode(bitand(ash.time_model,power(2,14)),0,'N','Y') as in_java_execution, decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind, decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close, decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load, decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y') as capture_overhead, decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' ) as replay_overhead, decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured, decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed, / Application attributes / ash.service_hash, ash.program, ash.module module, ash.action action, ash.client_id, ash.machine, ash.port, ash.ecid, / DB Replay info / ash.dbreplay_file_id, ash.dbreplay_call_counter, / stash columns */ ash.tm_delta_time, ash.tm_delta_cpu_time, ash.tm_delta_db_time, ash.delta_time, ash.delta_read_io_requests, ash.delta_write_io_requests, ash.delta_read_io_bytes, ash.delta_write_io_bytes, ash.delta_interconnect_io_bytes, ash.pga_allocated, ash.temp_space_allocated from WRM$SNAPSHOT sn, WRH$ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt where ash.snap_id = sn.snap_id(+) and ash.dbid = sn.dbid(+) and ash.instance_number = sn.instance_number(+) and ash.dbid = evt.dbid and ash.event_id = evt.event_id; ----以下数据不能导出 SELECT * FROM sys.Ku_Noexp_View d WHERE d.name LIKE '%WRH%' ; SELECT * FROM DBA_OBJECTS d WHERE d.ORACLE_MAINTAINED='Y' AND D.object_name LIKE 'WR%'; |
排除某个表
参考：https://www.xmmup.com/oracle-expdpheimpdppaichumougebiao.html
重新接着导入
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- 导出导入 expdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp schemas=lhr impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp schemas=lhr -- 若报错重复导入 impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=a.dmp table_exists_action=SKIP schemas=lhr -- 最后再把元数据导出导入一下（必须） expdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr content=metadata_only impdp system/lhr DIRECTORY=DATA_PUMP_DIR dumpfile=b.dmp schemas=lhr sqlfile=ddl.sql @ddl.sql |
