BLOG文档结构图

 


《Oracle DBA工作笔记》第二章 常用工具和问题分析(1)–删库、orabase、sqlplus http://mp.weixin.qq.com/s?src=3&timestamp=1470396751&ver=1&signature=3zOxvwdWqIAgGXx0pyNNHRtVIP-JqJS0WZD-Tq*7H2azxglzF0p1Ni-bsPrW*35Jq-zpEA*3ihQlunpvfe-0XCsHRELNgghB5HNTarBrkluNO7Ne9QiKRIeTwPZOv0U5KYwgVc5T1t9ymz8UkilPkVBa66Izb5TXBxzpeAexcWY=


《Oracle DBA工作笔记》第二章 常用工具和问题分析(2)–exp/imp系列问题 http://mp.weixin.qq.com/s?timestamp=1470499222&src=3&ver=1&signature=O0CVF6Pg3zcwYs2p7WLIjR8tUzpgu5aCMXp9LKve9G-Q7UcoKXDJcdEgq3*10LTXEm0hx4yBQk8hUBmZ-SZByd7nQcVlOejbyTtJGCOoBKTJlPwBc1tn1ybNqCsgLK3oRb*Q99ukf5H84gUL4yp72GSNPnQETIYzKToKlQEVJMA=


4.4  expdp/impdp系列问题

 

4.4.1  使用query选项

比如我们想导出SCOTT.EMP表中DEPTNO=20SCOTT.DEPT表中DNAME=’SALES’的记录,我们可以在parfile中写:query=SCOTT.EMP:”WHERE DEPTNO=20″,SCOTT.DEPT:”WHERE DNAME=’SALES'”,示例如下:

SYS@raclhr1> select * from scott.emp where DEPTNO=20;

 

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

———- ———- ——— ———- ——————- ———- ———- ———-

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20

      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20

      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

 

SYS@raclhr1> SELECT * FROM SCOTT.DEPT where DNAME=’SALES’;

 

    DEPTNO DNAME          LOC

———- ————– ————-

        30 SALES          CHICAGO

 

[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par

query=SCOTT.EMP:”WHERE DEPTNO=20″,SCOTT.DEPT:”WHERE DNAME=’SALES'”

 

[ZFZHLHRDB1:oracle]:/tmp>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

 

Export: Release 11.2.0.4.0 – Production on Wed Aug 3 09:32:21 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: “log=test_query_lhr_scott_02.log” Location: Command Line, Replaced with: “logfile=test_query_lhr_scott_02.log”

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting “SYS”.”SYS_EXPORT_SCHEMA_01″:  “/******** AS SYSDBA” directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par logfile=test_query_lhr_scott_02.log reuse_dumpfiles=true

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported “SCOTT”.”DEPT”                              5.859 KB       1 rows

. . exported “SCOTT”.”EMP”                               8.195 KB       5 rows

. . exported “SCOTT”.”SALGRADE”                          5.859 KB       5 rows

. . exported “SCOTT”.”BONUS”                                 0 KB       0 rows

Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /oracle/app/oracle/admin/raclhr1/dpdump/test_query_lhr_scott_02.dmp

Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Wed Aug 3 09:32:34 2016 elapsed 0 00:00:12

 

4.4.2  使用include

只导出procedure,function和含有TEST的序列。

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%'”

或使用parfile文件:

include=procedure,function,sequence:”like ‘%TEST%'”

或:

include=procedure

include=function

include=sequence:”like ‘%TEST%'”

 

[ZFZHLHRDB1:oracle]:/tmp>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

Export: Release 11.2.0.4.0 – Production on Wed Aug 3 10:06:04 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting “SYS”.”MY_JOB_LHR”:  “/******** 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

Estimate in progress using BLOCKS method…

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Master table “SYS”.”MY_JOB_LHR” successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.MY_JOB_LHR is:

  /oracle/app/oracle/admin/raclhr1/dpdump/test_include_lhr_scott_05.dmp

Job “SYS”.”MY_JOB_LHR” successfully completed at Wed Aug 3 10:06:10 2016 elapsed 0 00:00:05

4.4.3  得到对象的DDL语句

IMP工具使用show=y log=get_ddl.sql的方式获取ddl语句,同样,impdp也可以获取到dmp文件的ddl语句。IMPDP工具给我们提供了SQLFILE的命令行选项,只获取DDL语句,并未真正的执行数据导入:

impdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=impdp_hr.log schemas=hr sqlfile=get_ddl.sql

 

–expdp \’/ AS SYSDBA\’ directory=DATA_PUMP_DIR schemas=SCOTT  dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp

impdp  \’/ AS SYSDBA\’  directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp  logfile=imp_exptest.log sqlfile=exptest.sql

 

[ZFXDESKDB1:oracle]:/oracle>expdp \’/ AS SYSDBA\’ directory=DATA_PUMP_DIR schemas=SCOTT  dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp

 

Export: Release 11.2.0.4.0 – Production on Wed Aug 3 15:14:55 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting “SYS”.”SYS_EXPORT_SCHEMA_01″:  “/******** AS SYSDBA” directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 256 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported “SCOTT”.”DEPT”                              5.929 KB       4 rows

. . exported “SCOTT”.”EMP”                               8.562 KB      14 rows

. . exported “SCOTT”.”SALGRADE”                          5.859 KB       5 rows

. . exported “SCOTT”.”TEST”                              5.007 KB       1 rows

. . exported “SCOTT”.”BONUS”                                 0 KB       0 rows

Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /oracle/app/oracle/admin/lhrdb/dpdump/exptest_sql.dmp

Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Wed Aug 3 15:15:16 2016 elapsed 0 00:00:20

 

[ZFXDESKDB1:oracle]:/oracle>impdp  \’/ AS SYSDBA\’  directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp  logfile=imp_exptest.log sqlfile=exptest.sql

 

Import: Release 11.2.0.4.0 – Production on Wed Aug 3 15:16:06 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table “SYS”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded

Starting “SYS”.”SYS_SQL_FILE_FULL_01″:  “/******** AS SYSDBA” directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job “SYS”.”SYS_SQL_FILE_FULL_01″ successfully completed at Wed Aug 3 15:16:09 2016 elapsed 0 00:00:02

 

[ZFXDESKDB1:oracle]:/oracle>cd /oracle/app/oracle/admin/lhrdb/dpdump/

[ZFXDESKDB1:oracle]:/oracle/app/oracle/admin/lhrdb/dpdump>more exptest.sql

— CONNECT SYS

ALTER SESSION SET EVENTS ‘10150 TRACE NAME CONTEXT FOREVER, LEVEL 1’;

ALTER SESSION SET EVENTS ‘10904 TRACE NAME CONTEXT FOREVER, LEVEL 1’;

ALTER SESSION SET EVENTS ‘25475 TRACE NAME CONTEXT FOREVER, LEVEL 1’;

ALTER SESSION SET EVENTS ‘10407 TRACE NAME CONTEXT FOREVER, LEVEL 1’;

ALTER SESSION SET EVENTS ‘10851 TRACE NAME CONTEXT FOREVER, LEVEL 1’;

ALTER SESSION SET EVENTS ‘22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ‘;

— new object type path: SCHEMA_EXPORT/USER

— CONNECT SYSTEM

CREATE USER “SCOTT” IDENTIFIED BY VALUES ‘S:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67’

      DEFAULT TABLESPACE “USERS”

      TEMPORARY TABLESPACE “TEMP”

      PASSWORD EXPIRE

      ACCOUNT LOCK;

— new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT UNLIMITED TABLESPACE TO “SCOTT”;

— new object type path: SCHEMA_EXPORT/ROLE_GRANT

GRANT “CONNECT” TO “SCOTT”;

GRANT “RESOURCE” TO “SCOTT”;

— new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

ALTER USER “SCOTT” DEFAULT ROLE ALL;

— new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

— CONNECT SCOTT

《《《《。。。。。。。。篇幅原因,有省略,剩下的都是统计信息,生成sqlfile的时候也可以不用生成。。。。。。。。》》》》

 

 

4.4.4  Datapump的工作原理

一般数据在导入的过程中会生成3类的临时表,分别为IMPORT表、ERR表和ET表,其中只有IMPORT表可以查询,ERR表和ET表不能访问,报ORA-29913错误,但可以执行drop操作。

TABLE_EXISTS_ACTION=REPLACE 这个选项的底层操作是drop purge+create的操作。

 

4.4.5  使用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

 

更多内容请参考:

1、MOSExport/Import DataPump Parameter TRACE (文档 ID 286496.1)http://blog.itpub.net/26736162/viewspace-2085076/

2、使用隐含Trace参数诊断Oracle Data Pump故障:http://blog.itpub.net/26736162/viewspace-2072331/

 

4.5  如何彻底停止expdp

许多人在使用expdp命令时,不小心按了CTLR+C,然后又输入exit命令(或者网络中断等异常现象),导致expdp进程不存在,但oracle数据库的session仍存在,dmp文件也一直在增长。

处理办法

1、检查expdp进程是否还在

ps ef | grep expdp

(如存在,可用kill -9 process命令杀掉)

2、检查session是否仍存在

3、把相关session杀掉,如无DBA权限

drop table JOBID purge;

(JOBID即为DIRECTORY,此例为LZT_CASS1DATAJOB)

 

4、检查相关表及dumpsession

select * from GV$DATAPUMP_SESSION;

select * From USER_DATAPUMP_JOBS;

DBA_DATAPUMP_JOBS;

dba_datapump_sessions;

结果应该无记录

 

5、删除导出的dmp文件。如不删除,重提expdp命令时,会报dmp文件已存在

 

总结:查看进程、查看session、查看表GV$DATAPUMP_SESSIONUSER_DATAPUMP_JOBS

4.5.1  我的视图

set line 9999

col owner_name for a10

col job_name for a25

col operation for a10

col job_mode for a10

col state for a15

col job_mode for a10 

col state for a15

col osuser for a10

col “degree|attached|datapump” for a25

col session_info for a20 

SELECT ds.inst_id,

       dj.owner_name,

       dj.job_name,

       dj.operation,

       dj.job_mode,

       dj.state,

       dj.degree || ‘,’ || dj.attached_sessions || ‘,’ ||

       dj.datapump_sessions “degree|attached|datapump”,

       ds.session_type,

       s.osuser ,

       (SELECT s.SID || ‘,’ || s.SERIAL# || ‘,’ || p.SPID

          FROM gv$process p

         where s.paddr = p.addr

           AND s.inst_id = p.inst_id) session_info

  FROM DBA_DATAPUMP_JOBS dj –gv$datapump_job 

  full outer join dba_datapump_sessions ds –gv$datapump_session

    on (dj.job_name = ds.job_name and dj.owner_name = ds.owner_name)

  left outer join gv$session s

    on (s.saddr = ds.saddr and ds.inst_id = s.inst_id)

 ORDER BY dj.owner_name, dj.job_name;

 

 

About Me

………………………………………………………………………………………………………………………………………………………………………………….                        

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

v 本文在ITpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

v QQ群:230161599 微信群:私聊

v 本文地址:http://blog.itpub.net/26736162/viewspace-2122942/

v 本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b)

v 小麦苗分享的其它资料:http://blog.itpub.net/26736162/viewspace-1624453/

v 联系我请加QQ好友(642808185),注明添加缘由

v 于 2016-08-02 09:00~ 2016-08-03 19:00 在中行完成

v 【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

………………………………………………………………………………………………………………………………………………………………………………….

长按识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,学习最实用的数据库技术。

本文分享自微信公众号 – DB宝(lhrdba)。
如有侵权,请联系 [email protected] 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。