eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.KU$_PTAB_STATS_VIEW

Source

View Text - Preformatted


select  /*+ no_merge */
          t.obj#, bt.trigflag, o.name, o.subname, null, t.bo#,
          bitand(bt.property, 2097152), t.blkcnt, t.rowcnt, t.avgrln,
          decode(bitand(t.flags, 24), 24, 3, 16, 2, 8, 1, 0),
          t.samplesize, TO_CHAR(t.analyzetime, 'YYYY-MM-DD HH24:MI:SS'),
          (select value(tcsv) from sys.ku$_tab_cache_stats_view tcsv
           where t.obj# = tcsv.obj_num),
          cast(multiset(select value(csv)
                        from   sys.ku$_col_stats_view csv
                        where  csv.obj_num = t.obj#)
                        as ku$_col_stats_list_t)
  from    sys.obj$ o, sys.tab$ bt, sys.tabpart$ t
  where   o.obj# = t.obj# and
          t.bo# = bt.obj# AND
          NOT EXISTS (                   /* table does not have associations */
                SELECT 1
                FROM   sys.association$ a
                where  a.obj# = t.obj#) and
          NOT EXISTS (           /* type in table does not have associations */
                SELECT  1
                FROM    sys.obj$ tt, sys.coltype$ ct, sys.association$ a
                WHERE   t.obj# = ct.obj# AND
                        ct.toid = tt.oid$ AND
                        tt.obj# = a.obj#) AND
          BITAND(t.flags,2) != 0
UNION ALL
  select  /*+ no_merge */
          t.obj#, bt.trigflag, op.name, op.subname, null, t.bo#,
          bitand(bt.property, 2097152), t.blkcnt, t.rowcnt,
          t.avgrln,
          decode(bitand(t.flags, 24), 24, 3, 16, 2, 8, 1, 0),
          t.samplesize, TO_CHAR(t.analyzetime, 'YYYY-MM-DD HH24:MI:SS'),
          (select value(tcsv) from sys.ku$_tab_cache_stats_view tcsv
           where t.obj# = tcsv.obj_num),
          cast(multiset(select value(csv)
                        from   sys.ku$_col_stats_view csv
                        where  csv.obj_num = t.obj#)
                        as ku$_col_stats_list_t)
  from    sys.obj$ op, sys.tab$ bt, sys.tabcompart$ t
  where   op.obj# = t.obj# and
          t.bo# = bt.obj# AND
          NOT EXISTS (                   /* table does not have associations */
                SELECT 1
                FROM   sys.association$ a
                where  a.obj# = t.obj#) and
          NOT EXISTS (           /* type in table does not have associations */
                SELECT  1
                FROM    sys.obj$ tt, sys.coltype$ ct, sys.association$ a
                WHERE   t.obj# = ct.obj# AND
                        ct.toid = tt.oid$ AND
                        tt.obj# = a.obj#) AND
          BITAND(t.flags,2) != 0
UNION ALL
  select  /*+ no_merge */
          tsp.obj#, bt.trigflag, ot.name, op.subname, ot.subname, t.bo#,
          bitand(bt.property, 2097152), tsp.blkcnt, tsp.rowcnt,
          tsp.avgrln,
          decode(bitand(tsp.flags, 24), 24, 3, 16, 2, 8, 1, 0),
          t.samplesize, TO_CHAR(t.analyzetime, 'YYYY-MM-DD HH24:MI:SS'),
          (select value(tcsv) from sys.ku$_tab_cache_stats_view tcsv
           where t.obj# = tcsv.obj_num),
          cast(multiset(select value(csv)
                        from   sys.ku$_col_stats_view csv
                        where  csv.obj_num = tsp.obj#)
                        as ku$_col_stats_list_t)
  from    sys.obj$ ot, sys.obj$ op, sys.tab$ bt, sys.tabcompart$ t,
          sys.tabsubpart$ tsp
  where   ot.obj# = tsp.obj# and
          tsp.pobj# = t.obj# AND
          t.obj# = op.obj# AND
          t.bo# = bt.obj# AND
          NOT EXISTS (                   /* table does not have associations */
                SELECT 1
                FROM   sys.association$ a
                where  a.obj# = t.obj#) and
          NOT EXISTS (           /* type in table does not have associations */
                SELECT  1
                FROM    sys.obj$ tt, sys.coltype$ ct, sys.association$ a
                WHERE   t.obj# = ct.obj# AND
                        ct.toid = tt.oid$ AND
                        tt.obj# = a.obj#) AND
          BITAND(tsp.flags,2) != 0

View Text - HTML Formatted

SELECT /*+ NO_MERGE */ T.OBJ#
, BT.TRIGFLAG
, O.NAME
, O.SUBNAME
, NULL
, T.BO#
, BITAND(BT.PROPERTY
, 2097152)
, T.BLKCNT
, T.ROWCNT
, T.AVGRLN
, DECODE(BITAND(T.FLAGS
, 24)
, 24
, 3
, 16
, 2
, 8
, 1
, 0)
, T.SAMPLESIZE
, TO_CHAR(T.ANALYZETIME
, 'YYYY-MM-DD HH24:MI:SS')
, (SELECT VALUE(TCSV)
FROM SYS.KU$_TAB_CACHE_STATS_VIEW TCSV
WHERE T.OBJ# = TCSV.OBJ_NUM)
, CAST(MULTISET(SELECT VALUE(CSV)
FROM SYS.KU$_COL_STATS_VIEW CSV
WHERE CSV.OBJ_NUM = T.OBJ#) AS KU$_COL_STATS_LIST_T)
FROM SYS.OBJ$ O
, SYS.TAB$ BT
, SYS.TABPART$ T
WHERE O.OBJ# = T.OBJ# AND T.BO# = BT.OBJ# AND NOT EXISTS ( /* TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.ASSOCIATION$ A
WHERE A.OBJ# = T.OBJ#) AND NOT EXISTS ( /* TYPE IN TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.OBJ$ TT
, SYS.COLTYPE$ CT
, SYS.ASSOCIATION$ A
WHERE T.OBJ# = CT.OBJ# AND CT.TOID = TT.OID$ AND TT.OBJ# = A.OBJ#) AND BITAND(T.FLAGS
, 2) != 0 UNION ALL SELECT /*+ NO_MERGE */ T.OBJ#
, BT.TRIGFLAG
, OP.NAME
, OP.SUBNAME
, NULL
, T.BO#
, BITAND(BT.PROPERTY
, 2097152)
, T.BLKCNT
, T.ROWCNT
, T.AVGRLN
, DECODE(BITAND(T.FLAGS
, 24)
, 24
, 3
, 16
, 2
, 8
, 1
, 0)
, T.SAMPLESIZE
, TO_CHAR(T.ANALYZETIME
, 'YYYY-MM-DD HH24:MI:SS')
, (SELECT VALUE(TCSV)
FROM SYS.KU$_TAB_CACHE_STATS_VIEW TCSV
WHERE T.OBJ# = TCSV.OBJ_NUM)
, CAST(MULTISET(SELECT VALUE(CSV)
FROM SYS.KU$_COL_STATS_VIEW CSV
WHERE CSV.OBJ_NUM = T.OBJ#) AS KU$_COL_STATS_LIST_T)
FROM SYS.OBJ$ OP
, SYS.TAB$ BT
, SYS.TABCOMPART$ T
WHERE OP.OBJ# = T.OBJ# AND T.BO# = BT.OBJ# AND NOT EXISTS ( /* TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.ASSOCIATION$ A
WHERE A.OBJ# = T.OBJ#) AND NOT EXISTS ( /* TYPE IN TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.OBJ$ TT
, SYS.COLTYPE$ CT
, SYS.ASSOCIATION$ A
WHERE T.OBJ# = CT.OBJ# AND CT.TOID = TT.OID$ AND TT.OBJ# = A.OBJ#) AND BITAND(T.FLAGS
, 2) != 0 UNION ALL SELECT /*+ NO_MERGE */ TSP.OBJ#
, BT.TRIGFLAG
, OT.NAME
, OP.SUBNAME
, OT.SUBNAME
, T.BO#
, BITAND(BT.PROPERTY
, 2097152)
, TSP.BLKCNT
, TSP.ROWCNT
, TSP.AVGRLN
, DECODE(BITAND(TSP.FLAGS
, 24)
, 24
, 3
, 16
, 2
, 8
, 1
, 0)
, T.SAMPLESIZE
, TO_CHAR(T.ANALYZETIME
, 'YYYY-MM-DD HH24:MI:SS')
, (SELECT VALUE(TCSV)
FROM SYS.KU$_TAB_CACHE_STATS_VIEW TCSV
WHERE T.OBJ# = TCSV.OBJ_NUM)
, CAST(MULTISET(SELECT VALUE(CSV)
FROM SYS.KU$_COL_STATS_VIEW CSV
WHERE CSV.OBJ_NUM = TSP.OBJ#) AS KU$_COL_STATS_LIST_T)
FROM SYS.OBJ$ OT
, SYS.OBJ$ OP
, SYS.TAB$ BT
, SYS.TABCOMPART$ T
, SYS.TABSUBPART$ TSP
WHERE OT.OBJ# = TSP.OBJ# AND TSP.POBJ# = T.OBJ# AND T.OBJ# = OP.OBJ# AND T.BO# = BT.OBJ# AND NOT EXISTS ( /* TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.ASSOCIATION$ A
WHERE A.OBJ# = T.OBJ#) AND NOT EXISTS ( /* TYPE IN TABLE DOES NOT HAVE ASSOCIATIONS */ SELECT 1
FROM SYS.OBJ$ TT
, SYS.COLTYPE$ CT
, SYS.ASSOCIATION$ A
WHERE T.OBJ# = CT.OBJ# AND CT.TOID = TT.OID$ AND TT.OBJ# = A.OBJ#) AND BITAND(TSP.FLAGS
, 2) != 0