10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列對分割槽鎖定顯示為空的解決

yangtingkun發表於2012-07-19

Oracle10g的DBA_TAB_STATISTICS檢視的STATTYPE_LOCKED列沒有正確的顯示結果。

10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列對分割槽鎖定顯示為空：

 

 

上文提到了DBA_TAB_STATISTICS中的STATTYPE_LOCKED列在10g中對於分割槽鎖定統計資訊顯示為空，那麼在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的查詢結果：

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

使用新建立的這個檢視，就可以解決鎖定分割槽的統計資訊顯示問題。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-736199/，如需轉載，請註明出處，否則將追究法律責任。

相關文章