Oracle10g的DBA_TAB_STATISTICS视图的STATTYPE_LOCKED列没有正确的显示结果。
10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空：http://yangtingkun.net/?p=1023
上文提到了DBA_TAB_STATISTICS中的STATTYPE_LOCKED列在10g中对于分tb区锁定统计信息显示为空，那么在10g中有没有办法获取到正确的结果呢：
SQL> select table_name, partition_name, last_analyzed, stattype_locked
2 from dba_tab_statistics
3 where wner = user
4 and table_name = 'T_PART';
TABLE_NAME PARTITION_NAME LAST_ANAL STATT
------------------------------ ------------------------------ --------- -----
T_PART 16-JUL-12
T_PART P1
T_PART P2 16-JUL-12
T_PART PMAX 16-JUL-12
SQL> exec dbms_stats.gather_table_stats(user, 'T_PART', partname => 'P1')
BEGIN dbms_stats.gather_table_stats(user, 'T_PART', partname => 'P1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1
显然虽然Oracle在DBA_TAB_STATISTICS视图中没有正确的显示分区的锁定状态，但是Oracle在内部确实记录了分区的锁定状态，既然Oracle记录了这个信息，就有办法将这个信息显示出来。
既然11g能够显示该列的值，最简单的方法莫过于对比10g和11g中DBA_TAB_STATISTICS视图的区别，10g视图的结果：
SQL> select text from dba_views where view_name = 'DBA_TAB_STATISTICS';
TEXT
--------------------------------------------------------------------------------
SELECT /* TABLES */
u.name, o.name, NULL, NULL, NULL, NULL, 'TABLE', t.rowcnt,
.
.
.
decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* PARTITIONS, NOT IOT */
u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab_stats$ ts, sys.tab$ tab,
sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* IOT Partitions */
u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* COMPOSITE PARTITIONS */
u.name, o.name, o.subname, tcp.part#, NULL, NULL, 'PARTITION',
.
.
.
decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp,
sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* SUBPARTITIONS */
u.name, po.name, po.subname, tcp.part#, so.subname, tsp.subpart#,
.
.
.
decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp,
sys.tabsubpartv$ tsp, sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* FIXED TABLES */
'SYS', t.kqftanam, NULL, NULL, NULL, NULL, 'FIXED TABLE',
.
.
.
对比一下11g的查询结果tb：
SQL> select text from dba_views where view_name = 'DBA_TAB_STATISTICS';
TEXT
--------------------------------------------------------------------------------
SELECT /* TABLES */
u.name, o.name, NULL, NULL, NULL, NULL, 'TABLE', t.rowcnt,
.
.
.
decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* PARTITIONS, NOT IOT */
u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
decode(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level
*/
decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
/*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
/* if 0 => not locked, 3 => data and cache stats locked */
0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab_stats$ ts, sys.tab$ tab,
sys.mon_mods_all$ m
.
.
.
UNION ALL
SELECT /* IOT Partitions */
u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
decode(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level
*/
decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
/*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
/* if 0 => not locked, 3 => data and cache stats locked */
0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* COMPOSITE PARTITIONS */
u.name, o.name, o.subname, tcp.part#, NULL, NULL, 'PARTITION',
.
.
.
decode(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level
*/
decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
/*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
/* if 0 => not locked, 3 => data and cache stats locked */
0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp,
sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* SUBPARTITIONS */
u.name, po.name, po.subname, tcp.part#, so.subname, tsp.subpart#,
.
.
.
decode(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level.
* Note that dbms_stats does n't allow locking subpartition stats.
* If the composite partition is locked, all subpartitions are
* considered locked. Hence decode checks for tcp entry.
*/
decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
/*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
/* if 0 => not locked, 3 => data and cache stats locked */
0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
FROM
sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp,
sys.tabsubpartv$ tsp, sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
WHERE
.
.
.
UNION ALL
SELECT /* FIXED TABLES */
.
.
显然在11g中Oracle对于分区锁定的显示采用了新的算法，那么可以仿照11g中建立一个视图，来解决10g中分区显示存在错误的问题：
SQL> CREATE OR REPLACE VIEW DBA_TAB_STATISTICS_LOCK
2 (OWNER, TABLE_NAME, PARTITION_NAME,
3 SUBPARTITION_NAME, OBJECT_TYPE, STATTYPE_LOCKED)
4 AS
5 SELECT u.name, o.name, NULL, NULL, 'TABLE',
6 decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
7 0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL')
8 FROM sys.user$ u, sys.obj$ o, sys.tab$ t
9 WHERE o.owner# = u.user#
10 and o.obj# = t.obj#
11 and bitand(t.property, 1) = 0
12 and o.subname IS NULL
13 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
14 and bitand(o.flags, 128) = 0
15 UNION ALL
16 SELECT u.name, o.name, o.subname, NULL, 'PARTITION',
17 decode(
18 decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
19 decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
20 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
21 FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab
22 WHERE o.owner# = u.user#
23 and o.obj# = tp.obj#
24 and tp.bo# = tab.obj#
25 and bitand(tab.property, 64) = 0
26 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
27 and bitand(o.flags, 128) = 0
28 UNION ALL
29 SELECT u.name, o.name, o.subname, NULL, 'PARTITION',
30 decode(
31 decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
32 decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
33 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
34 FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab
35 WHERE o.owner# = u.user#
36 and o.obj# = tp.obj#
37 and tp.bo# = tab.obj#
38 and bitand(tab.property, 64) = 64
39 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
40 and bitand(o.flags, 128) = 0
41 UNION ALL
42 SELECT u.name, o.name, o.subname, NULL, 'PARTITION',
43 decode(
44 decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
45 decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
46 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
47 FROM sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp, sys.tab$ tab
48 WHERE o.owner# = u.user#
49 and o.obj# = tcp.obj#
50 and tcp.bo# = tab.obj#
51 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
52 and bitand(o.flags, 128) = 0
53 UNION ALL
54 SELECT u.name, po.name, po.subname, so.subname, 'SUBPARTITION',
55 decode(
56 decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
57 decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
58 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
59 FROM sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp, sys.tabsubpartv$ tsp, sys.tab$ tab
60 WHERE so.obj# = tsp.obj#
61 and po.obj# = tcp.obj#
62 and tcp.obj# = tsp.pobj#
63 and tcp.bo# = tab.obj#
64 and u.user# = po.owner#
65 and bitand(tab.property, 64) = 0
66 and po.namespace = 1 and po.remoteowner IS NULL and po.linkname IS NULL
67 and bitand(po.flags, 128) = 0
68 ;
View created.
SQL> select table_name, partition_name, object_type, stattype_locked
2 from dba_tab_statistics_lock
3 where wner = 'TEST'
4 and table_name = 'T_PART';
TABLE_NAME PARTITION_NAME OBJECT_TYPE STATT
------------------------------ ------------------------------ ------------ -----
T_PART TABLE
T_PART P1 PARTITION ALL
T_PART P2 PARTITION
T_PART PMAX PARTITION
使用新创建的这个视图，就可以解决锁定分区的统计信息显示问题。