在我们进行expdp导出的时候,Oracle会对其加一个row share锁,假设如果有一个会话在这之前对该表加了exclusive锁,那么expdp就会hang住.之所以要加row share锁,是希望在导这个表的时候,表的结构不会发生改变,或者被删除等等.
---------session1
SQL> select owner,table_name from dba_tables where owner='TEST';
OWNER TABLE_NAME
------------------------------ ------------------------------
TEST T2
TEST T1
SQL> lock table t1 in exclusive mode;
Table(s) Locked.
---------session2
[oracle@db10g datapump]$ expdp test/test directory=dp dumpfile=test.dmp logfile=test.log
Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 21 July, 2012 15:47:18
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/******** directory=dp dumpfile=test.dmp logfile=test.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.06 MB
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
这个时候我们可以发现session2在导出的时候一直被hang住,我们来具体的看一看我们的expdp需要请求的锁.
SQL> select vs.sid, vp.program PROCESSNAME, vp.spid THREADID
2 from v$session vs,
3 v$process vp ,
4 dba_datapump_sessions dp
5 where vp.addr = vs.paddr(+) and
6 vs.saddr = dp.saddr;
SID PROCESSNAME THREADID
---------- ------------------------------------------------ ------------
149 oracle@db10g.localdomain (TNS V1-V3) 7249
146 oracle@db10g.localdomain (DM00) 7253
144 oracle@db10g.localdomain (DW01) 7255
我们通过查询以上三个视图,可以看到sid,操作系统的process id,还有我们的process name.接下来我们看看这三个会话的等待事件.
SQL> select sid,event,p1,p2,p3, seconds_in_wait
2 from v$session_wait
3 where sid in (144,146,149);SID EVENT P1 P2 P3 SECONDS_IN_WAIT
---------- --------------------------------------------- ---------- ---------- ---------- ---------------
144 enq: TM - contention 1414332418 51799 0 864
146 wait for unread message on broadcast channel 2199568920 2199510048 0 60
149 wait for unread message on broadcast channel 2199568920 2199510624 0 872
注意看142,这个session就是在wait的session.
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ----- -- ------ ------ ------ -------- ------- ------
00000000830E51B8 00000000830E51D8 165 XR 4 0 1 0 2704 0
00000000830E5260 00000000830E5280 165 CF 0 0 2 0 2704 0
00000000830E53B0 00000000830E53D0 165 RS 25 1 2 0 2701 0
00000000830E5458 00000000830E5478 167 PW 1 0 3 0 2507 0
00000000830E5500 00000000830E5520 166 RT 1 0 6 0 2701 0
00000000830E55A8 00000000830E55C8 144 TO 7429 1 3 0 1460 0
00000000830E5650 00000000830E5670 164 TS 3 1 3 0 2508 0
00000000830E57A0 00000000830E57C0 167 MR 1 0 4 0 2509 0
00000000830E5848 00000000830E5868 167 MR 2 0 4 0 2509 0
00000000830E58F0 00000000830E5910 167 MR 3 0 4 0 2509 0
00000000830E5998 00000000830E59B8 167 MR 4 0 4 0 2509 0
00000000830E5A40 00000000830E5A60 167 MR 201 0 4 0 2509 0
0000000082269B90 0000000082269BB8 159 TM 51799 0 6 0 1518 1
0000000082269CB0 0000000082269CD8 144 TM 51799 0 0 2 1443 0
我们可以看到144这个会话的ID1是51799,这里对应的是OBJECT ID,而159这个会话锁住了51799这个对象,所以我们可以通过DBA_OBJECTS来查看此对象.
SQL> select owner, object_name
2 from dba_objects
3 where object_id = 51799;
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------
TEST T1
接下来我们要用oradebug进行hang的分析.
SQL> oradebug setospid 7255
Oracle pid: 22, Unix process pid: 7255, image: oracle@db10g.localdomain (DW01)
SQL> oradebug dump errorstack 3
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/oracle/admin/db10g/bdump/db10g_dw01_7255.trc
ksedmp: internal or fatal error
Current SQL statement for this session:
LOCK TABLE "TEST"."T1" IN ROW SHARE MODE
----- PL/SQL Call Stack -----
object line object
handle number name
0x835ab670 14 package body SYS.KUPD$DATA_INT
0x7dbbfee0 1633 package body SYS.KUPD$DATA
0x7dbc17f8 11005 package body SYS.KUPW$WORKER
0x7dbc17f8 2695 package body SYS.KUPW$WORKER
0x7dbc17f8 7050 package body SYS.KUPW$WORKER
0x7dbc17f8 1340 package body SYS.KUPW$WORKER
0x7db92c50 2 anonymous block
这里我们可以清楚的看到这个会话想对T1上row share锁,结果因为T1这个对象已经被另外一个会话上了exclusive模式的锁,所以此处就被hang住了,当我们对session1做commit或者rollback操作后,expdp才能继续往下走.
---------session1
SQL> commit;
Commit complete.
----------session2
. . exported "TEST"."T1" 9.327 MB 100064 rows
. . exported "TEST"."T2" 4.921 KB 2 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/oracle/app/oracle/datapump/test.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:22:55
参考文档:Expdp Hangs During Exporting Data Due to a Lock Situation [ID 756834.1]
Post a Comment