При выполнении ALTER TABLE DROP CONSTRAINT случился TM — library cache deadlock распределённого (RAC) вида, отразившийся в ASH следующим образом:
12.1.0.2@ SQL> @ash_sql_wait_tree "(session_id,session_serial#) in ((4460,16750)) and sample_time > trunc(sysdate,'hh24')" 0 LVL INST_ID BLOCKING_TREE EVENT WAIT_CLASS DATA_OBJECT_P1RAW IN_PARSE SQL_CHILD_NUMBER WAITS_COUNT EXECS_COUNT AVG_WA SESS_COUNT BLOCK_SID MIN_STIME MAX_STIME SQL_ID SQL_PLAN_HASH_VALUE SQL_EXEC_ID SQL_TEXT --- ------- --------------- --------------------- ------------ ------------------ -------- ---------------- ----------- ----------- ------ ---------- ---------- -------------------------- -------------------------- ------------- ------------------- ----------- ---------------------------------------------------------------------------------------------------- 1 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 11 1 0 1 GLOBAL i# 26-JUN-17 04.06.39.431 PM 26-JUN-17 04.06.49.451 PM 7kpzqksdg4wu8 0 1 1 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 2 1 0 1 VALID i#2 26-JUN-17 04.06.50.451 PM 26-JUN-17 04.06.51.451 PM 7kpzqksdg4wu8 0 1 1 1 (USER) library cache pin Concurrency 000000261138EA48 Y 0 1 0 1 1 GLOBAL i# 26-JUN-17 04.06.37.431 PM 26-JUN-17 04.06.37.431 PM 73f85gf8qvb34 2024994732 0 select NULL AS TABLE_CAT, OWNER AS TABLE_SCHEM, 'NO' as IS_AUTOINCREMENT, cc.COMMENTS AS REMARKS,OWN 1 1 (USER) library cache pin Concurrency 000000274A98AEE0 Y 0 1 0 0 1 GLOBAL i# 26-JUN-17 04.06.36.431 PM 26-JUN-17 04.06.36.431 PM 6196bjzyj6dv7 2050374072 0 SELECT null as TABLE_CAT, a.OWNER as TABLE_SCHEM, a.TABLE_NAME as TABLE_NAME, a.TEMPORARY as TEMPORA 2 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 4 0 6807 1 VALID i#1 26-JUN-17 04.06.50.574 PM 26-JUN-17 04.06.51.574 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 2 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 1 0 0 1 GLOBAL i# 26-JUN-17 04.06.49.564 PM 26-JUN-17 04.06.49.564 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 3 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 4 1 0 1 VALID i#2 26-JUN-17 04.06.50.451 PM 26-JUN-17 04.06.51.451 PM 7kpzqksdg4wu8 0 1 3 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 2 1 0 1 GLOBAL i# 26-JUN-17 04.06.49.451 PM 26-JUN-17 04.06.49.451 PM 7kpzqksdg4wu8 0 1 4 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 4 0 6807 1 VALID i#1 26-JUN-17 04.06.50.574 PM 26-JUN-17 04.06.51.574 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 4 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 2 0 0 1 GLOBAL i# 26-JUN-17 04.06.49.564 PM 26-JUN-17 04.06.49.564 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 5 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 2 1 0 1 GLOBAL i# 26-JUN-17 04.06.49.451 PM 26-JUN-17 04.06.49.451 PM 7kpzqksdg4wu8 0 1 11 rows selected
где: (more…)