问题描述
Oracle10g的DBA_TAB_STATISTICS视图的STATTYPE_LOCKED列没有正确的显示结果。
10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空:http://yangtingkun.net/?p=1023
上文提到了DBA_TAB_STATISTICS中的STATTYPE_LOCKED列在10g中对于分区锁定统计信息显示为空,那么在10g中有没有办法获取到正确的结果呢:
SQL> SELECT TABLE_NAME, partition_name, last_analyzed, stattype_locked 2 FROM dba_tab_statistics 3 WHERE owner = 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的查询结果:
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 owner = '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
使用新创建的这个视图,就可以解决锁定分区的统计信息显示问题。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。