10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空的解决

Posted on 2012/07/19 by yangtingkun

Oracle10g的DBA_TAB_STATISTICS视图的STATTYPE_LOCKED列没有正确的显示结果。
10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空：https://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

使用新创建的这个视图，就可以解决锁定分区的统计信息显示问题。

This entry was posted in ORACLE and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *