Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1168203
  • 博文数量: 178
  • 博客积分: 2776
  • 博客等级: 少校
  • 技术积分: 2809
  • 用 户 组: 普通用户
  • 注册时间: 2012-03-22 15:36
文章分类

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2012-12-12 15:26:08

实际上dba_tables

_dml_monitoring_enabled

 

SQL> set long 99999

 

SQL> select text from dba_views where view_name='DBA_TABLES';

 

TEXT

--------------------------------------------------------------------------------

select u.name, o.name, decode(bitand(t.property,2151678048), 0, ts.name, null),

       decode(bitand(t.property, 1024), 0, null, co.name),

       decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),

              0, null, co.name),

       decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),

       decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),

       decode(bitand(ts.flags, 32), 32, to_number(NULL),

          decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),

       decode(bitand(t.property, 32), 0, t.initrans, null),

       decode(bitand(t.property, 32), 0, t.maxtrans, null),

       s.iniexts * ts.blocksize,

       decode(bitand(ts.flags, 3), 1, to_number(NULL),

                                      s.extsize * ts.blocksize),

       s.minexts, s.maxexts,

       decode(bitand(ts.flags, 3), 1, to_number(NULL),

                                      s.extpct),

       decode(bitand(ts.flags, 32), 32, to_number(NULL),

         decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),

       decode(bitand(ts.flags, 32), 32, to_number(NULL),

         decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),

       decode(bitand(t.property, 32+64), 0,

                decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),

       decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),

       t.rowcnt,

       decode(bitand(t.property, 64), 0, t.blkcnt, null),

       decode(bitand(t.property, 64), 0, t.empcnt, null),

       t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,

       decode(bitand(t.property, 64), 0, t.flbcnt, null),

       lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),

       lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),

       lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),

       decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),

       t.samplesize, t.analyzetime,

       decode(bitand(t.property, 32), 32, 'YES', 'NO'),

       decode(bitand(t.property, 64), 64, 'IOT',

               decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',

               decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null

))),

       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),

       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),

       decode(bitand(t.property, 8192), 8192, 'YES',

              decode(bitand(t.property, 1), 0, 'NO', 'YES')),

       decode(bitand(o.flags, 2), 2, 'DEFAULT',

             decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),

       decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),

       decode(bitand(t.flags, 512), 0, 'NO', 'YES'),

       decode(bitand(t.flags, 256), 0, 'NO', 'YES'),

       decode(bitand(o.flags, 2), 0, NULL,

          decode(bitand(t.property, 8388608), 8388608,

                 'SYS$SESSION', 'SYS$TRANSACTION')),

       decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),

       decode(bitand(o.flags, 2), 2, 'NO',

           decode(bitand(t.property, 2147483648), 2147483648, 'NO',

              decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),

       decode(bitand(t.property, 1024), 0, null, cu.name),

       decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),

       decode(bitand(t.property, 32), 32, null,

                decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),

       decode(bitand(o.flags, 128), 128, 'YES', 'NO')

from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,

     sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi

where o.owner# = u.user#

  and o.obj# = t.obj#

  and bitand(t.property, 1) = 0

  and bitand(o.flags, 128) = 0

  and t.bobj# = co.obj# (+)

  and t.ts# = ts.ts#

  and t.file# = s.file# (+)

  and t.block# = s.block# (+)

  and t.ts# = s.ts# (+)

  and t.dataobj# = cx.obj# (+)

  and cx.owner# = cu.user# (+)

  and ksppi.indx = ksppcv.indx

  and ksppi.ksppinm = '_dml_monitoring_enabled'

oracle dbahttp://www.cuug.com/

阅读(793) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~