--生成测试表.
create table yang_part (
id number,
create_time date,
value varchar2(20),
name varchar2(30)
)
partition by range (create_time) (
partition p2010 values less than (to_date('20110101','yyyymmdd')),
partition p201101 values less than (to_date('20110401','yyyymmdd')),
partition p201102 values less than (to_date('20110701','yyyymmdd')),
partition p201103 values less than (to_date('20111001','yyyymmdd')),
partition p201104 values less than (to_date('20120101','yyyymmdd'))
);
/*create index id_ind_yang on yang_part(id) global partition by range(create_time)
partition by range (create_time) (
partition p2010 values less than (to_date('20110101','yyyymmdd')),
partition p201101 values less than (to_date('20110401','yyyymmdd')),
partition p201102 values less than (to_date('20110701','yyyymmdd')),
partition p201103 values less than (to_date('20111001','yyyymmdd')),
partition p201104 values less than (to_date('20120101','yyyymmdd'))
);*/
--生成测试用数据.
--使用随机数生成200w的数据, 时间分布在从今天往过去的500天内.字符串数据的内容完全为随机数
insert into yang_part
select rownum id,sysdate - dbms_random.value(1,500) create_time,
dbms_random.string('l',15) value,
dbms_random.string('l',20) name
from dual
connect by level <= 2e7;
commit;
--创建全局索引。
create index id_ind_yang on yang_part(id);
--打开enqueue lock的trace跟踪.
alter session set events '10704 trace name context forever,level 12';
--执行truncate partition xx update global indexes语句.
--截断分区表p201102
alter table yang_part truncate partition p201102 update global indexes;
--删除分区表p201103
alter table yang_part drop partition p201103 update global indexes;
--交换分区表
alter table yang_part exchange partition p201104 with table exch_with_p201104 update global indexes;
----关闭
alter session set events '10704 trace name context off';
--查询跟踪文件。
select value from v$diag_info where name='Default Trace File';
---
sys@rac1>select object_name,subobject_name,object_id,data_object_id from dba_objects where object_name='YANG_PART';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID 十六进制
------------- ---------------- ---------- ---------- ----------
YANG_PART 129028 1F804
YANG_PART P201104 129033 129033 1F809
YANG_PART P201103 129032 129032 1F808
YANG_PART P201102 129031 129031 1F807
YANG_PART P201101 129030 129030 1F806
YANG_PART P2010 129029 129029 1F805
6 rows selected.
Elapsed: 00:00:00.01
================================跟踪文件分析=======================
oracle@rac1:rac1 />more /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_9020.trc | grep TM |more
ksqgtl *** TM-00010187-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,10187,0 mode=3 timeout=21474836
ksqrcl: TM,10187,0
ksqcmi: TM,10187,0 mode=0 timeout=0
---有上面的查询和转换可知 1F804对应于基表 yang_part，
---在删除数据时，首先会请求基表的share lock SX(Row-X)行级排它锁
ksqgtl *** TM-0001f804-00000000 mode=3 flags=0x401 timeout=0 ***
ksqcmi: TM,1f804,0 mode=3 timeout=0
---129031=>> 1F807 truncate分区P201102，此时对P201102表分区加排他锁。
select object_name,subobject_name,object_id,data_object_id from dba_objects where object_id=129031；
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID 十六进制
------------- ---------------- ---------- ---------- ----------
YANG_PART P201102 129031 129031 1F807
ksqgtl *** TM-0001f807-00000000 mode=6 flags=0x401 timeout=0 ***
ksqcmi: TM,1f807,0 mode=6 timeout=0
ksqgtl *** TM-0000027b-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,27b,0 mode=3 timeout=21474836
ksqgtl *** TM-000001ca-00000000 mode=6 flags=0x401 timeout=0 ***
ksqcmi: TM,1ca,0 mode=6 timeout=0
ksqrcl: TM,1ca,0
ksqcmi: TM,1ca,0 mode=0 timeout=0
ksqgtl *** TM-00000047-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,47,0 mode=3 timeout=21474836
ksqgtl *** TM-000001ad-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,1ad,0 mode=3 timeout=21474836
ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,4,0 mode=3 timeout=21474836
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,12,0 mode=3 timeout=21474836
ksqrcl: TM,12,0 select object_name ,object_type from dba_objects where object_id in (18,4,71,458,635);
ksqcmi: TM,12,0 mode=0 timeout=0
ksqrcl: TM,4,0
ksqcmi: TM,4,0 mode=0 timeout=0
ksqrcl: TM,1ad,0
ksqcmi: TM,1ad,0 mode=0 timeout=0
ksqrcl: TM,47,0
ksqcmi: TM,47,0 mode=0 timeout=0
ksqrcl: TM,27b,0
ksqcmi: TM,27b,0 mode=0 timeout=0
ksqrcl: TM,1f807,0
ksqcmi: TM,1f807,0 mode=0 timeout=0
ksqrcl: TM,1f804,0
ksqcmi: TM,1f804,0 mode=0 timeout=0
===================================================
一下是drop 分区 P201103的跟踪文件，除了所用的系统表以外，和truncate表加的锁相同。
ksqgtl *** TM-0001f804-00000000 mode=3 flags=0x401 timeout=0 ***
ksqcmi: TM,1f804,0 mode=3 timeout=0
ksqgtl *** TM-0001f808-00000000 mode=6 flags=0x401 timeout=0 ***
ksqcmi: TM,1f808,0 mode=6 timeout=0
ksqgtl *** TM-000001b1-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,1b1,0 mode=3 timeout=21474836
ksqgtl *** TM-000001a8-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,1a8,0 mode=3 timeout=21474836
ksqgtl *** TM-0000027b-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,27b,0 mode=3 timeout=21474836
ksqgtl *** TM-00000047-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,47,0 mode=3 timeout=21474836
ksqgtl *** TM-000001ad-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,1ad,0 mode=3 timeout=21474836
ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,4,0 mode=3 timeout=21474836
=====================分区交换的跟踪文件分析==================
从对象号为，1F804 (分区表 yang_part),1F809(分区表的P201104 分区),1F80B(全局索引) 所加的锁
oracle@rac1:rac1 /tmp>more /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_29814.trc | grep TM |more
--先对基表加share lock
ksqgtl *** TM-0001f804-00000000 mode=3 flags=0x401 timeout=0 ***
ksqcmi: TM,1f804,0 mode=3 timeout=0
--对P201104 分区加 排他锁。
ksqgtl *** TM-0001f809-00000000 mode=6 flags=0x401 timeout=0 ***
ksqcmi: TM,1f809,0 mode=6 timeout=0
--对EXCH_WITH_201104 加排他锁。
ksqgtl *** TM-0001f80b-00000000 mode=6 flags=0x401 timeout=0 ***
ksqcmi: TM,1f80b,0 mode=6 timeout=0
==============其他的为系统表。
ksqgtl *** TM-000001a8-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,1a8,0 mode=3 timeout=21474836
ksqgtl *** TM-000001a6-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,1a6,0 mode=3 timeout=21474836
----整个过程中使用的系统表：
sys@rac1>select object_name ,object_type from dba_objects where object_id in (18,4,71,458,635);
OBJECT_NAME OBJECT_TYPE
------------------------- -------------------
TAB$ TABLE
OBJ$ TABLE
SUPEROBJ$ TABLE
MON_MODS$ TABLE
MLOG$ TABLE
===================================================
