介绍一篇metalink的文档，介绍expdp 的trace功能 ID 286496.1
下面这个实验使用trace和10046配合使用
1.建一个了一个用户，和一个aa表
SQL> select * from maxiajun.aa;
ID NAME
———- ——————–
1 maxiajun
2 bb
2.在我们进行expdp导出的时候,Oracle会对其加一个row share锁,假设如果有一个会话在这之前对该表加了exclusive锁,那么expdp就会hang住.之所以要加row share锁,是希望在导这个表的时候,表的结构不会发生改变,或者被删除等等.
SQL>lock table aa in exclusive mode; –模拟hang住的情形
3.模拟导出
[oracle@DG2 ~]$ expdp maxiajun/oracle directory=dump dumpfile=test.dmp logfile=test.log trace=480300
Export: Release 10.2.0.1.0 – Production on Thursday, 27 September, 2012 15:15:20
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “MAXIAJUN”.”SYS_EXPORT_SCHEMA_02″: maxiajun/******** directory=dump dumpfile=test.dmp logfile=test.log trace=480300
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/COMMENT
. . exported “MAXIAJUN”.”SYS_EXPORT_SCHEMA_01″ 117.6 KB 1051 rows
4.观察expdp的trace文件
上面expdp开始hang住
[root@DG2 bdump]#tail -f /oracle/admin/hzmc/bdump/hzmc_dw03_29345.trc
KUPW: 15:15:48.584: 1: in procedure BUILD_OBJECT_STRINGS
KUPW: 15:15:48.584: 1: in procedure MOVE_DATA_USING_DATA_API UNLOADing process_order 2 TABLE_DATA:”MAXIAJUN”.”AA”
KUPW: 15:15:48.586: 1: KUPD$DATA.OPEN called
KUPW: 15:15:48.587: 1: KUPD$DATA.OPEN returned
KUPW: 15:15:48.587: 1: KUPD$DATA.SET_PARAMETER called
KUPW: 15:15:48.587: 1: KUPD$DATA.SET_PARAMETER returned
KUPW: 15:15:48.588: 1: KUPD$DATA.START_JOB called
……
5.用10046时间跟踪
获取expdp 进程的pid
SQL> select program FROM v$session;
PROGRAM
————————————————
[url=]oracle@DG2[/url] (DM02)
[url=]oracle@DG2[/url] (q000)
[url=]ude@DG2[/url] (TNS V1-V3)
[url=]sqlplus@DG2[/url] (TNS V1-V3)
[url=]oracle@DG2[/url] (DW03)
[url=]sqlplus@DG2[/url] (TNS V1-V3)
[url=]oracle@DG2[/url] (DW01)
[url=]oracle@DG2[/url] (q001)
[url=]oracle@DG2[/url] (DM00)
[url=]oracle@DG2[/url] (QMNC)
[url=]oracle@DG2[/url] (ARC1)
SQL> SELECT p.spid,p.addr,p.pid,s.sid,SUBSTR(s.username,1,15) “USERNAME”,
2 SUBSTR(s.program,1,15) “PROGRAM”
3 FROM v$process p,v$session s
4 WHERE s.paddr=p.addr
5 AND addr=(SELECT paddr FROM v$session WHERE UPPER(program) LIKE ‘%DW03%’);
SPID ADDR PID SID USERNAME PROGRAM
———— ——– ———- ———- —————————— ——————————
29345 29E1F6C4 27 140 MAXIAJUN [url=]oracle@DG2[/url] (DW03)
或者
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
SQL> oradebug setospid 29345
Oracle pid: 27, Unix process pid: 29345, image: [url=]oracle@DG2[/url] (DW03)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/hzmc/bdump/hzmc_dw03_29345.trc
查看跟踪文件
SQL>tail -f /oracle/admin/hzmc/bdump/hzmc_dw03_29345.trc
Received ORADEBUG command ‘unlimit’ from process Unix process pid: 29153, image:
Received ORADEBUG command ‘tracefile_name’ from process Unix process pid: 29153, image:
*** 2012-09-27 15:24:44.292
Received ORADEBUG command ‘Event 10046 trace name context forever, level 12′ from process Unix process pid: 29153, image:
WAIT #53: nam=’enq: TM – contention’ ela= 2974913 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119808880588
WAIT #53: nam=’enq: TM – contention’ ela= 3019428 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119811900543
WAIT #53: nam=’enq: TM – contention’ ela= 2989870 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119814890580
WAIT #53: nam=’enq: TM – contention’ ela= 2982338 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119817873007
解决锁
SQL> select s.sid,s.serial#,s.username,s.logon_time from v$session s,v$locked_object l where s.sid=l.session_id;
SID SERIAL# USERNAME LOGON_TIM
———- ———- —————————— ———
138 538 SYS 27-SEP-12
143 204 MAXIAJUN 27-SEP-12
140 1242 MAXIAJUN 27-SEP-12
SQL> select sid,addr,id1,id2,lmode,request from v$lock where sid in (138,143,140) order by sid;
SID ADDR ID1 ID2 LMODE REQUEST
———- ——– ———- ———- ———- ———-
138 288F3A90 51703 0 6 0
140 29FB9F10 7102 1 3 0
140 288F3BE8 51703 0 0 2
143 288F3B3C 51703 0 0 2
143 29FB9E58 7102 1 3 0
SQL> select owner,object_naME,object_id,object_type from dba_objects where object_id in (51703,7102);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
—————————— —————————— ———- ——————-
SYS KU$NOEXP_TAB 7102 TABLE
MAXIAJUN AA 51703 TABLE
