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.ALL_TABLES

Source

View Text - Preformatted


select u.name, o.name,
       decode(bitand(t.property,2151678048), 0, ts.name,
              decode(t.ts#, 0, null, ts.name)),
       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),
       decode(bitand(t.property, 17179869184), 17179869184,
                     ds.initial_stg * ts.blocksize,
                     s.iniexts * ts.blocksize),
       decode(bitand(t.property, 17179869184), 17179869184,
              ds.next_stg * ts.blocksize,
              s.extsize * ts.blocksize),
       decode(bitand(t.property, 17179869184), 17179869184,
              ds.minext_stg, s.minexts),
       decode(bitand(t.property, 17179869184), 17179869184,
              ds.maxext_stg, s.maxexts),
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
              decode(bitand(t.property, 17179869184), 17179869184,
                            ds.pctinc_stg, s.extpct)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(bitand(o.flags, 2), 2, 1,
                decode(bitand(t.property, 17179869184), 17179869184,
                       decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
                       decode(s.lists, 0, 1, s.lists)))),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(bitand(o.flags, 2), 2, 1,
                decode(bitand(t.property, 17179869184), 17179869184,
                       decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                       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),
       decode(bitand(t.property, 64), 0, t.avgspc, null),
       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(bitand(decode(bitand(t.property, 17179869184), 17179869184,
                            ds.bfp_stg, s.cachehint), 3),
                            1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
                            ds.bfp_stg, s.cachehint), 12)/4,
                            1, 'KEEP', 2, 'NONE', 'DEFAULT')),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
                            ds.bfp_stg, s.cachehint), 48)/16,
                            1, 'KEEP', 2, 'NONE', 'DEFAULT')),
       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'),
       case when (bitand(t.property, 32) = 32) then
         null
       when (bitand(t.property, 17179869184) = 17179869184) then
          decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED')
       else
         decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')
       end,
       case when (bitand(t.property, 32) = 32) then
         null
       when (bitand(t.property, 17179869184) = 17179869184) then
          decode(bitand(ds.flags_stg, 4), 4,
          case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC'
               when bitand(ds.cmpflag_stg, 3) = 2 then 'ADVANCED'
               else concat(decode(ds.cmplvl_stg, 1, 'QUERY LOW',
                                                 2, 'QUERY HIGH',
                                                 3, 'ARCHIVE LOW',
                                                    'ARCHIVE HIGH'),
                           decode(bitand(ds.flags_stg, 524288), 524288,
                                  ' ROW LEVEL LOCKING', '')) end,
               null)
       else
         decode(bitand(s.spare1, 2048), 0, null,
         case when bitand(s.spare1, 16777216) = 16777216
                   then 'ADVANCED'
              when bitand(s.spare1, 100663296) = 33554432  -- 0x2000000
                   then concat('QUERY LOW',
                               decode(bitand(s.spare1, 2147483648),
                                      2147483648, ' ROW LEVEL LOCKING', ''))
              when bitand(s.spare1, 100663296) = 67108864  -- 0x4000000
                   then concat('QUERY HIGH',
                               decode(bitand(s.spare1, 2147483648),
                                      2147483648, ' ROW LEVEL LOCKING', ''))
              when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000
                   then concat('ARCHIVE LOW',
                               decode(bitand(s.spare1, 2147483648),
                                      2147483648, ' ROW LEVEL LOCKING', ''))
              when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000
                   then concat('ARCHIVE HIGH',
                               decode(bitand(s.spare1, 2147483648),
                                      2147483648, ' ROW LEVEL LOCKING', ''))
              else 'BASIC' end)
       end,
       decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
       decode(bitand(t.trigflag, 2097152), 2097152, 'YES', 'NO'),
       decode(bitand(t.property, 17179869184), 17179869184, 'NO',
              decode(bitand(t.property, 32), 32, 'N/A', 'YES')),
       decode(bitand(t.property,16492674416640),2199023255552,'FORCE',
                 4398046511104,'MANUAL','DEFAULT'),
       decode(bitand(t.property, 18014398509481984), 18014398509481984,
                     'YES', 'NO'),
       case when bitand(t.property, 1125899906842624) = 1125899906842624
                 then 'ROW ACCESS TRACKING'
            when bitand(t.property, 2251799813685248) = 2251799813685248
                 then 'SEGMENT ACCESS TRACKING'
       end,
       case when bitand(t.property, 844424930131968) = 844424930131968
                 then 'ROW CREATION/MODIFICATION'
            when bitand(t.property, 281474976710656) = 281474976710656
                 then 'ROW MODIFICATION'
            when bitand(t.property, 562949953421312) = 562949953421312
                 then 'ROW CREATION'
       end,
       decode(bitand(t.property, 288230376151711744), 288230376151711744,
              'YES', 'NO'),
       decode(bitand(t.property/4294967296, 134217728), 134217728, 'YES', 'NO'),
       -- INMEMORY
       case when (bitand(t.property, 32) = 32) then
         null
       when (bitand(t.property, 17179869184) = 17179869184) then
         -- flags/imcflag_stg (stgdef.h)
         decode(bitand(ds.flags_stg, 6291456),
             2097152, 'ENABLED',
             4194304, 'DISABLED', 'DISABLED')
       else
         -- ktsscflg (ktscts.h)
         decode(bitand(s.spare1, 70373039144960),
             4294967296,     'ENABLED',
             70368744177664, 'DISABLED', 'DISABLED')
       end,
       -- INMEMORY_PRIORITY
       case when (bitand(t.property, 32) = 32) then
         null
       when (bitand(t.property, 17179869184) = 17179869184) then
         decode(bitand(ds.flags_stg, 2097152), 2097152,
                decode(bitand(ds.imcflag_stg, 4), 4,
                decode(bitand(ds.flags_stg, 2097152), 2097152,
                decode(bitand(ds.imcflag_stg, 7936),
                256, 'NONE',
                512, 'LOW',
                1024, 'MEDIUM',
                2048, 'HIGH',
                4096, 'CRITICAL', 'UNKNOWN'), null),
                'NONE'),
                null)
       else
         decode(bitand(s.spare1, 4294967296), 4294967296,
                decode(bitand(s.spare1, 34359738368), 34359738368,
                decode(bitand(s.spare1, 61572651155456),
                8796093022208, 'LOW',
                17592186044416, 'MEDIUM',
                35184372088832, 'HIGH',
                52776558133248, 'CRITICAL', 'NONE'),
                'NONE'),
                null)
       end,
       -- INMEMORY_DISTRIBUTE
       case when (bitand(t.property, 32) = 32) then
         null
       when (bitand(t.property, 17179869184) = 17179869184) then
         decode(bitand(ds.flags_stg, 2097152), 2097152,
                decode(bitand(ds.imcflag_stg, 1), 1,
                       decode(bitand(ds.imcflag_stg, (16+32)),
                              16,  'BY ROWID RANGE',
                              32,  'BY PARTITION',
                              48,  'BY SUBPARTITION',
                               0,  'AUTO'),
                  null), null)
       else
         decode(bitand(s.spare1, 4294967296), 4294967296,
                decode(bitand(s.spare1, 8589934592), 8589934592,
                        decode(bitand(s.spare1, 206158430208),
                        68719476736,   'BY ROWID RANGE',
                        137438953472,  'BY PARTITION',
                        206158430208,  'BY SUBPARTITION',
                        0,             'AUTO'),
                        'UNKNOWN'),
                  null)
       end,
       -- INMEMORY_COMPRESSION
       case when (bitand(t.property, 32) = 32) then
         null
       when (bitand(t.property, 17179869184) = 17179869184) then
         decode(bitand(ds.flags_stg, 2097152), 2097152,
                decode(bitand(ds.imcflag_stg, (2+8+64+128)),
                              2,   'NO MEMCOMPRESS',
                              8,  'FOR DML',
                              10,  'FOR QUERY LOW',
                              64, 'FOR QUERY HIGH',
                              66, 'FOR CAPACITY LOW',
                              72, 'FOR CAPACITY HIGH', 'UNKNOWN'),
                null)
       else
         decode(bitand(s.spare1, 4294967296), 4294967296,
                decode(bitand(s.spare1, 841813590016),
                              17179869184,  'NO MEMCOMPRESS',
                              274877906944, 'FOR DML',
                              292057776128, 'FOR QUERY LOW',
                              549755813888, 'FOR QUERY HIGH',
                              566935683072, 'FOR CAPACITY LOW',
                              824633720832, 'FOR CAPACITY HIGH', 'UNKNOWN'),
                 null)
       end,
       -- INMEMORY_DUPLICATE
       case when (bitand(t.property, 32) = 32) then
         null
       when (bitand(t.property, 17179869184) = 17179869184) then
        decode(bitand(ds.flags_stg, 2097152), 2097152,
               decode(bitand(ds.imcflag_stg, (8192+16384)),
                              8192,   'NO DUPLICATE',
                              16384,  'DUPLICATE',
                              24576,  'DUPLICATE ALL',
                              'UNKNOWN'),
                null)
       else
          decode(bitand(s.spare1, 4294967296), 4294967296,
                   decode(bitand(s.spare1, 6597069766656),
                           2199023255552, 'NO DUPLICATE',
                           4398046511104, 'DUPLICATE',
                           6597069766656, 'DUPLICATE ALL', 'UNKNOWN'),
                 null)
       end
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,
     (select /*+ no_merge */ ksppcv.ksppstvl
      from x$ksppcv ksppcv, x$ksppi ksppi
      where ksppi.ksppinm = '_dml_monitoring_enabled' and
            ksppi.indx = ksppcv.indx
     ) ksppcv,
     sys.deferred_stg$ ds
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.obj# = ds.obj# (+)
  and t.file# = s.file# (+)
  and t.block# = s.block# (+)
  and t.ts# = s.ts# (+)
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -397/* READ ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
       or /* user is SYSBACKUP */
         SYS_CONTEXT('USERENV','CURRENT_USERID') = 2147483617
      )
  and t.dataobj# = cx.obj# (+)
  and cx.owner# = cu.user# (+)

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DECODE(BITAND(T.PROPERTY
, 2151678048)
, 0
, TS.NAME
, DECODE(T.TS#
, 0
, NULL
, TS.NAME))
, 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)
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.INITIAL_STG * TS.BLOCKSIZE
, S.INIEXTS * TS.BLOCKSIZE)
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.NEXT_STG * TS.BLOCKSIZE
, S.EXTSIZE * TS.BLOCKSIZE)
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.MINEXT_STG
, S.MINEXTS)
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.MAXEXT_STG
, S.MAXEXTS)
, DECODE(BITAND(TS.FLAGS
, 3)
, 1
, TO_NUMBER(NULL)
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.PCTINC_STG
, S.EXTPCT))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 1
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DECODE(DS.FRLINS_STG
, 0
, 1
, DS.FRLINS_STG)
, DECODE(S.LISTS
, 0
, 1
, S.LISTS))))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 1
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
, 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)
, DECODE(BITAND(T.PROPERTY
, 64)
, 0
, T.AVGSPC
, NULL)
, 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(BITAND(DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.BFP_STG
, S.CACHEHINT)
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT'))
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 'DEFAULT'
, DECODE(BITAND(DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT'))
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 'DEFAULT'
, DECODE(BITAND(DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT'))
, 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')
, CASE WHEN (BITAND(T.PROPERTY
, 32) = 32) THEN NULL WHEN (BITAND(T.PROPERTY
, 17179869184) = 17179869184) THEN DECODE(BITAND(DS.FLAGS_STG
, 4)
, 4
, 'ENABLED'
, 'DISABLED') ELSE DECODE(BITAND(S.SPARE1
, 2048)
, 2048
, 'ENABLED'
, 'DISABLED') END
, CASE WHEN (BITAND(T.PROPERTY
, 32) = 32) THEN NULL WHEN (BITAND(T.PROPERTY
, 17179869184) = 17179869184) THEN DECODE(BITAND(DS.FLAGS_STG
, 4)
, 4
, CASE WHEN BITAND(DS.CMPFLAG_STG
, 3) = 1 THEN 'BASIC' WHEN BITAND(DS.CMPFLAG_STG
, 3) = 2 THEN 'ADVANCED' ELSE CONCAT(DECODE(DS.CMPLVL_STG
, 1
, 'QUERY LOW'
, 2
, 'QUERY HIGH'
, 3
, 'ARCHIVE LOW'
, 'ARCHIVE HIGH')
, DECODE(BITAND(DS.FLAGS_STG
, 524288)
, 524288
, ' ROW LEVEL LOCKING'
, '')) END
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 2048)
, 0
, NULL
, CASE WHEN BITAND(S.SPARE1
, 16777216) = 16777216 THEN 'ADVANCED' WHEN BITAND(S.SPARE1
, 100663296) = 33554432 -- 0X2000000 THEN CONCAT('QUERY LOW'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) WHEN BITAND(S.SPARE1
, 100663296) = 67108864 -- 0X4000000 THEN CONCAT('QUERY HIGH'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) WHEN BITAND(S.SPARE1
, 100663296) = 100663296 -- 0X2000000+0X4000000 THEN CONCAT('ARCHIVE LOW'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) WHEN BITAND(S.SPARE1
, 134217728) = 134217728 -- 0X8000000 THEN CONCAT('ARCHIVE HIGH'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) ELSE 'BASIC' END) END
, DECODE(BITAND(O.FLAGS
, 128)
, 128
, 'YES'
, 'NO')
, DECODE(BITAND(T.TRIGFLAG
, 2097152)
, 2097152
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY
, 17179869184)
, 17179869184
, 'NO'
, DECODE(BITAND(T.PROPERTY
, 32)
, 32
, 'N/A'
, 'YES'))
, DECODE(BITAND(T.PROPERTY
, 16492674416640)
, 2199023255552
, 'FORCE'
, 4398046511104
, 'MANUAL'
, 'DEFAULT')
, DECODE(BITAND(T.PROPERTY
, 18014398509481984)
, 18014398509481984
, 'YES'
, 'NO')
, CASE WHEN BITAND(T.PROPERTY
, 1125899906842624) = 1125899906842624 THEN 'ROW ACCESS TRACKING' WHEN BITAND(T.PROPERTY
, 2251799813685248) = 2251799813685248 THEN 'SEGMENT ACCESS TRACKING' END
, CASE WHEN BITAND(T.PROPERTY
, 844424930131968) = 844424930131968 THEN 'ROW CREATION/MODIFICATION' WHEN BITAND(T.PROPERTY
, 281474976710656) = 281474976710656 THEN 'ROW MODIFICATION' WHEN BITAND(T.PROPERTY
, 562949953421312) = 562949953421312 THEN 'ROW CREATION' END
, DECODE(BITAND(T.PROPERTY
, 288230376151711744)
, 288230376151711744
, 'YES'
, 'NO')
, DECODE(BITAND(T.PROPERTY/4294967296
, 134217728)
, 134217728
, 'YES'
, 'NO')
, -- INMEMORY CASE WHEN (BITAND(T.PROPERTY
, 32) = 32) THEN NULL WHEN (BITAND(T.PROPERTY
, 17179869184) = 17179869184) THEN -- FLAGS/IMCFLAG_STG (STGDEF.H) DECODE(BITAND(DS.FLAGS_STG
, 6291456)
, 2097152
, 'ENABLED'
, 4194304
, 'DISABLED'
, 'DISABLED') ELSE -- KTSSCFLG (KTSCTS.H) DECODE(BITAND(S.SPARE1
, 70373039144960)
, 4294967296
, 'ENABLED'
, 70368744177664
, 'DISABLED'
, 'DISABLED') END
, -- INMEMORY_PRIORITY CASE WHEN (BITAND(T.PROPERTY
, 32) = 32) THEN NULL WHEN (BITAND(T.PROPERTY
, 17179869184) = 17179869184) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, 4)
, 4
, DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, 7936)
, 256
, 'NONE'
, 512
, 'LOW'
, 1024
, 'MEDIUM'
, 2048
, 'HIGH'
, 4096
, 'CRITICAL'
, 'UNKNOWN')
, NULL)
, 'NONE')
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 34359738368)
, 34359738368
, DECODE(BITAND(S.SPARE1
, 61572651155456)
, 8796093022208
, 'LOW'
, 17592186044416
, 'MEDIUM'
, 35184372088832
, 'HIGH'
, 52776558133248
, 'CRITICAL'
, 'NONE')
, 'NONE')
, NULL) END
, -- INMEMORY_DISTRIBUTE CASE WHEN (BITAND(T.PROPERTY
, 32) = 32) THEN NULL WHEN (BITAND(T.PROPERTY
, 17179869184) = 17179869184) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, 1)
, 1
, DECODE(BITAND(DS.IMCFLAG_STG
, (16+32))
, 16
, 'BY ROWID RANGE'
, 32
, 'BY PARTITION'
, 48
, 'BY SUBPARTITION'
, 0
, 'AUTO')
, NULL)
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 8589934592)
, 8589934592
, DECODE(BITAND(S.SPARE1
, 206158430208)
, 68719476736
, 'BY ROWID RANGE'
, 137438953472
, 'BY PARTITION'
, 206158430208
, 'BY SUBPARTITION'
, 0
, 'AUTO')
, 'UNKNOWN')
, NULL) END
, -- INMEMORY_COMPRESSION CASE WHEN (BITAND(T.PROPERTY
, 32) = 32) THEN NULL WHEN (BITAND(T.PROPERTY
, 17179869184) = 17179869184) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, (2+8+64+128))
, 2
, 'NO MEMCOMPRESS'
, 8
, 'FOR DML'
, 10
, 'FOR QUERY LOW'
, 64
, 'FOR QUERY HIGH'
, 66
, 'FOR CAPACITY LOW'
, 72
, 'FOR CAPACITY HIGH'
, 'UNKNOWN')
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 841813590016)
, 17179869184
, 'NO MEMCOMPRESS'
, 274877906944
, 'FOR DML'
, 292057776128
, 'FOR QUERY LOW'
, 549755813888
, 'FOR QUERY HIGH'
, 566935683072
, 'FOR CAPACITY LOW'
, 824633720832
, 'FOR CAPACITY HIGH'
, 'UNKNOWN')
, NULL) END
, -- INMEMORY_DUPLICATE CASE WHEN (BITAND(T.PROPERTY
, 32) = 32) THEN NULL WHEN (BITAND(T.PROPERTY
, 17179869184) = 17179869184) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, (8192+16384))
, 8192
, 'NO DUPLICATE'
, 16384
, 'DUPLICATE'
, 24576
, 'DUPLICATE ALL'
, 'UNKNOWN')
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 6597069766656)
, 2199023255552
, 'NO DUPLICATE'
, 4398046511104
, 'DUPLICATE'
, 6597069766656
, 'DUPLICATE ALL'
, 'UNKNOWN')
, NULL) END 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
, (SELECT /*+ NO_MERGE */ KSPPCV.KSPPSTVL
FROM X$KSPPCV KSPPCV
, X$KSPPI KSPPI
WHERE KSPPI.KSPPINM = '_DML_MONITORING_ENABLED' AND KSPPI.INDX = KSPPCV.INDX ) KSPPCV
, SYS.DEFERRED_STG$ DS 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.OBJ# = DS.OBJ# (+)
AND T.FILE# = S.FILE# (+)
AND T.BLOCK# = S.BLOCK# (+)
AND T.TS# = S.TS# (+)
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) OR /* USER IS SYSBACKUP */ SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') = 2147483617 )
AND T.DATAOBJ# = CX.OBJ# (+)
AND CX.OWNER# = CU.USER# (+)