VIEW: SYS.ALL_PART_TABLES

Source

View Text - Preformatted

select u.name, o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST',
                           5, 'REFERENCE', 'UNKNOWN'),
       decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH',
                                   3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE',
                                   'UNKNOWN'),
       po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols,
       mod(trunc(po.spare2/256), 256),
       decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
       ts.name, po.defpctfree,
       decode(bitand(ts.flags, 32), 32, to_number(NULL), po.defpctused),
       po.definitrans,
       po.defmaxtrans,
       decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts),
       decode(po.defextsize, NULL, 'DEFAULT', po.defextsize),
       decode(po.defminexts, NULL, 'DEFAULT', po.defminexts),
       decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts),
       decode(po.defmaxsize, NULL, 'DEFAULT', po.defmaxsize),
       decode(po.defextpct, NULL, 'DEFAULT', po.defextpct),
       decode(bitand(ts.flags, 32), 32, to_number(NULL), po.deflists),
       decode(bitand(ts.flags, 32), 32,  to_number(NULL),po.defgroups),
       decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       decode(bitand(mod(trunc(po.spare2/4294967296),256), 3),
                       0, 'NONE', 1, 'ENABLED', 2, 'DISABLED', 'UNKNOWN'),
       -- compression info is in byte 4 of spare2
       case bitand(mod(trunc(po.spare2/4294967296),256), 127) -- 6 bits in use
         when 0 then NULL
         when 1 then 'BASIC'                                 -- 00000001
         when 2 then NULL
         when 5 then 'ADVANCED'                              -- 00000101
         when 9 then 'QUERY LOW'                             -- 00001001
         when 17 then 'QUERY HIGH'                           -- 00010001
         when 25 then 'ARCHIVE LOW'                          -- 00011001
         when 33 then 'ARCHIVE HIGH'                         -- 00100001
         when 73 then 'QUERY LOW ROW LEVEL LOCKING'          -- 01001001
         when 81 then 'QUERY HIGH ROW LEVEL LOCKING'         -- 01010001
         when 89 then 'ARCHIVE LOW ROW LEVEL LOCKING'        -- 01011001
         when 97 then 'ARCHIVE HIGH ROW LEVEL LOCKING'       -- 01100001
                 else 'UNKNOWN' end,                         -- internal ilevels
       decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       (select c.name from sys.con$ c, sys.cdef$ cd
          where c.con# = cd.con# and cd.obj# = o.obj#
            and cd.type# = 4 and bitand(cd.defer, 512) != 0),
       case when (po.parttype = 5 and bitand(po.flags, 64) = 64) then 'YES'
            else po.interval_str end,
       decode(bitand(t.property,8224), 8224, 'YES', 'NO'),
       decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE'),
       decode(bitand(po.flags, 8192), 8192, 'OFF', 'ON'),
       -- DEF_INMEMORY
       --  defimcflags_kkpacpcd (kkpac.h)
       case bitand(mod(trunc(po.spare2/1099511627776),4096), 3) -- bits 0,1
         when 0 then 'NONE'
         when 1 then 'ENABLED'
         when 2 then 'DISABLED'
         else 'UNKNOWN' end,
       -- DEF_INMEMORY_PRIORITY
       case bitand(mod(trunc(po.spare2/1099511627776),4096), 17) -- bits 0,4
         when 0 then NULL
         when 1 then 'NONE'
         when 17 then
           case bitand(mod(trunc(po.spare2/4503599627370496),64), 7)
             when 0 then 'NONE'
             when 1 then 'LOW'
             when 2 then 'MEDIUM'
             when 3 then 'HIGH'
             when 4 then 'CRITICAL'
             else 'UNKNOWN' end
         else 'UNKNOWN' end,
       -- DEF_INMEMORY_DISTRIBUTE
       case bitand(mod(trunc(po.spare2/1099511627776),256), 103)
         when 0 then NULL                              -- first 3 bits,5,6
         when 1 then NULL
         when 2 then NULL
         when 5 then 'AUTO'
         when 37 then 'BY ROWID RANGE'
         when 69 then 'BY PARTITION'
         when 101 then 'BY SUBPARTITION'
         else 'UNKNOWN' end,
       -- DEF_INMEMORY_COMPRESSION
       case bitand(mod(trunc(po.spare2/1099511627776),4096), 395)
         when 0 then NULL                              -- bits 0,1,3,7,8
         when 1 then NULL
         when 2 then NULL
         when 9 then 'NO MEMCOMPRESS'
         when 129 then 'FOR DML'
         when 137 then 'FOR QUERY LOW'
         when 257 then 'FOR QUERY HIGH'
         when 265 then 'FOR CAPACITY LOW'
         when 385 then 'FOR CAPACITY HIGH'
         else 'UNKNOWN' end,
       -- DEF_INMEMORY_DUPLICATE
       case bitand(mod(trunc(po.spare2/1099511627776),4096), 3073)
         when 0 then NULL                              -- bits 0,1,10,11
         when 1 then NULL
         when 2 then NULL
         when 1025 then 'NO DUPLICATE'
         when 2049 then 'DUPLICATE'
         when 3073 then 'DUPLICATE ALL'
         else 'UNKNOWN' end
from   sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.tab$ t, sys.user$ u
where  o.obj# = po.obj# and po.defts# = ts.ts# (+) and t.obj# = o.obj# and
       o.owner# = u.user# and
       bitand(t.property, 64 + 128) = 0 and o.subname IS NULL and
       o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 */)
                 )
      )
union all -- NON-IOT and IOT
select u.name, o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST',
                           5, 'REFERENCE', 'UNKNOWN'),
       decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH',
                                   3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE',
                                   'UNKNOWN'),
       po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols,
       mod(trunc(po.spare2/256), 256),
       decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
       NULL, TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       NULL,--decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts),
       NULL,--decode(po.defextsize, NULL, 'DEFAULT', po.defextsize),
       NULL,--decode(po.defminexts, NULL, 'DEFAULT', po.defminexts),
       NULL,--decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts),
       NULL,--decode(po.defmaxsize, NULL, 'DEFAULT', po.defmaxsize),
       NULL,--decode(po.defextpct, NULL, 'DEFAULT', po.defextpct),
       TO_NUMBER(NULL),TO_NUMBER(NULL),--po.deflists, po.defgroups,
       decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       'N/A',
       null,
       decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       NULL  -- ref-partitioned IOT is not supported so skip the sub-query,
       ,case when (po.parttype = 5 and bitand(po.flags, 64) = 64) then 'YES'
             else po.interval_str end
       ,'N/A'
       , decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE')
       , decode(bitand(po.flags, 8192), 8192, 'OFF', 'ON')
       , NULL
       , NULL
       , NULL
       , NULL
       , NULL
from   sys.obj$ o, sys.partobj$ po, sys.tab$ t, sys.user$ u
where  o.obj# = po.obj# and t.obj# = o.obj# and
       o.owner# = u.user# and
       bitand(t.property, 64 + 128) != 0 and o.subname IS NULL and
       o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 */)
                 )
      )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DECODE(PO.PARTTYPE
, 1
, 'RANGE'
, 2
, 'HASH'
, 3
, 'SYSTEM'
, 4
, 'LIST'
, 5
, 'REFERENCE'
, 'UNKNOWN')
, DECODE(MOD(PO.SPARE2
, 256)
, 0
, 'NONE'
, 1
, 'RANGE'
, 2
, 'HASH'
, 3
, 'SYSTEM'
, 4
, 'LIST'
, 5
, 'REFERENCE'
, 'UNKNOWN')
, PO.PARTCNT
, MOD(TRUNC(PO.SPARE2/65536)
, 65536)
, PO.PARTKEYCOLS
, MOD(TRUNC(PO.SPARE2/256)
, 256)
, DECODE(BITAND(T.TRIGFLAG
, 1073741824)
, 1073741824
, 'UNUSABLE'
, 'VALID')
, TS.NAME
, PO.DEFPCTFREE
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, PO.DEFPCTUSED)
, PO.DEFINITRANS
, PO.DEFMAXTRANS
, DECODE(PO.DEFTINIEXTS
, NULL
, 'DEFAULT'
, PO.DEFTINIEXTS)
, DECODE(PO.DEFEXTSIZE
, NULL
, 'DEFAULT'
, PO.DEFEXTSIZE)
, DECODE(PO.DEFMINEXTS
, NULL
, 'DEFAULT'
, PO.DEFMINEXTS)
, DECODE(PO.DEFMAXEXTS
, NULL
, 'DEFAULT'
, PO.DEFMAXEXTS)
, DECODE(PO.DEFMAXSIZE
, NULL
, 'DEFAULT'
, PO.DEFMAXSIZE)
, DECODE(PO.DEFEXTPCT
, NULL
, 'DEFAULT'
, PO.DEFEXTPCT)
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, PO.DEFLISTS)
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, PO.DEFGROUPS)
, DECODE(PO.DEFLOGGING
, 0
, 'NONE'
, 1
, 'YES'
, 2
, 'NO'
, 'UNKNOWN')
, DECODE(BITAND(MOD(TRUNC(PO.SPARE2/4294967296)
, 256)
, 3)
, 0
, 'NONE'
, 1
, 'ENABLED'
, 2
, 'DISABLED'
, 'UNKNOWN')
, -- COMPRESSION INFO IS IN BYTE 4 OF SPARE2 CASE BITAND(MOD(TRUNC(PO.SPARE2/4294967296)
, 256)
, 127) -- 6 BITS IN USE WHEN 0 THEN NULL WHEN 1 THEN 'BASIC' -- 00000001 WHEN 2 THEN NULL WHEN 5 THEN 'ADVANCED' -- 00000101 WHEN 9 THEN 'QUERY LOW' -- 00001001 WHEN 17 THEN 'QUERY HIGH' -- 00010001 WHEN 25 THEN 'ARCHIVE LOW' -- 00011001 WHEN 33 THEN 'ARCHIVE HIGH' -- 00100001 WHEN 73 THEN 'QUERY LOW ROW LEVEL LOCKING' -- 01001001 WHEN 81 THEN 'QUERY HIGH ROW LEVEL LOCKING' -- 01010001 WHEN 89 THEN 'ARCHIVE LOW ROW LEVEL LOCKING' -- 01011001 WHEN 97 THEN 'ARCHIVE HIGH ROW LEVEL LOCKING' -- 01100001 ELSE 'UNKNOWN' END
, -- INTERNAL ILEVELS DECODE(BITAND(PO.SPARE1
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(PO.SPARE1
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(PO.SPARE1
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, (SELECT C.NAME
FROM SYS.CON$ C
, SYS.CDEF$ CD
WHERE C.CON# = CD.CON#
AND CD.OBJ# = O.OBJ#
AND CD.TYPE# = 4
AND BITAND(CD.DEFER
, 512) != 0)
, CASE WHEN (PO.PARTTYPE = 5
AND BITAND(PO.FLAGS
, 64) = 64) THEN 'YES' ELSE PO.INTERVAL_STR END
, DECODE(BITAND(T.PROPERTY
, 8224)
, 8224
, 'YES'
, 'NO')
, DECODE(BITAND(PO.FLAGS
, 6144)
, 4096
, 'YES'
, 2048
, 'NO'
, 'NONE')
, DECODE(BITAND(PO.FLAGS
, 8192)
, 8192
, 'OFF'
, 'ON')
, -- DEF_INMEMORY -- DEFIMCFLAGS_KKPACPCD (KKPAC.H) CASE BITAND(MOD(TRUNC(PO.SPARE2/1099511627776)
, 4096)
, 3) -- BITS 0
, 1 WHEN 0 THEN 'NONE' WHEN 1 THEN 'ENABLED' WHEN 2 THEN 'DISABLED' ELSE 'UNKNOWN' END
, -- DEF_INMEMORY_PRIORITY CASE BITAND(MOD(TRUNC(PO.SPARE2/1099511627776)
, 4096)
, 17) -- BITS 0
, 4 WHEN 0 THEN NULL WHEN 1 THEN 'NONE' WHEN 17 THEN CASE BITAND(MOD(TRUNC(PO.SPARE2/4503599627370496)
, 64)
, 7) WHEN 0 THEN 'NONE' WHEN 1 THEN 'LOW' WHEN 2 THEN 'MEDIUM' WHEN 3 THEN 'HIGH' WHEN 4 THEN 'CRITICAL' ELSE 'UNKNOWN' END ELSE 'UNKNOWN' END
, -- DEF_INMEMORY_DISTRIBUTE CASE BITAND(MOD(TRUNC(PO.SPARE2/1099511627776)
, 256)
, 103) WHEN 0 THEN NULL -- FIRST 3 BITS
, 5
, 6 WHEN 1 THEN NULL WHEN 2 THEN NULL WHEN 5 THEN 'AUTO' WHEN 37 THEN 'BY ROWID RANGE' WHEN 69 THEN 'BY PARTITION' WHEN 101 THEN 'BY SUBPARTITION' ELSE 'UNKNOWN' END
, -- DEF_INMEMORY_COMPRESSION CASE BITAND(MOD(TRUNC(PO.SPARE2/1099511627776)
, 4096)
, 395) WHEN 0 THEN NULL -- BITS 0
, 1
, 3
, 7
, 8 WHEN 1 THEN NULL WHEN 2 THEN NULL WHEN 9 THEN 'NO MEMCOMPRESS' WHEN 129 THEN 'FOR DML' WHEN 137 THEN 'FOR QUERY LOW' WHEN 257 THEN 'FOR QUERY HIGH' WHEN 265 THEN 'FOR CAPACITY LOW' WHEN 385 THEN 'FOR CAPACITY HIGH' ELSE 'UNKNOWN' END
, -- DEF_INMEMORY_DUPLICATE CASE BITAND(MOD(TRUNC(PO.SPARE2/1099511627776)
, 4096)
, 3073) WHEN 0 THEN NULL -- BITS 0
, 1
, 10
, 11 WHEN 1 THEN NULL WHEN 2 THEN NULL WHEN 1025 THEN 'NO DUPLICATE' WHEN 2049 THEN 'DUPLICATE' WHEN 3073 THEN 'DUPLICATE ALL' ELSE 'UNKNOWN' END FROM SYS.OBJ$ O
, SYS.PARTOBJ$ PO
, SYS.TS$ TS
, SYS.TAB$ T
, SYS.USER$ U WHERE O.OBJ# = PO.OBJ#
AND PO.DEFTS# = TS.TS# (+)
AND T.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND BITAND(T.PROPERTY
, 64 + 128) = 0
AND O.SUBNAME IS NULL AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL 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 */) ) ) UNION ALL -- NON-IOT
AND IOT SELECT U.NAME
, O.NAME
, DECODE(PO.PARTTYPE
, 1
, 'RANGE'
, 2
, 'HASH'
, 3
, 'SYSTEM'
, 4
, 'LIST'
, 5
, 'REFERENCE'
, 'UNKNOWN')
, DECODE(MOD(PO.SPARE2
, 256)
, 0
, 'NONE'
, 1
, 'RANGE'
, 2
, 'HASH'
, 3
, 'SYSTEM'
, 4
, 'LIST'
, 5
, 'REFERENCE'
, 'UNKNOWN')
, PO.PARTCNT
, MOD(TRUNC(PO.SPARE2/65536)
, 65536)
, PO.PARTKEYCOLS
, MOD(TRUNC(PO.SPARE2/256)
, 256)
, DECODE(BITAND(T.TRIGFLAG
, 1073741824)
, 1073741824
, 'UNUSABLE'
, 'VALID')
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, --DECODE(PO.DEFTINIEXTS
, NULL
, 'DEFAULT'
, PO.DEFTINIEXTS)
, NULL
, --DECODE(PO.DEFEXTSIZE
, NULL
, 'DEFAULT'
, PO.DEFEXTSIZE)
, NULL
, --DECODE(PO.DEFMINEXTS
, NULL
, 'DEFAULT'
, PO.DEFMINEXTS)
, NULL
, --DECODE(PO.DEFMAXEXTS
, NULL
, 'DEFAULT'
, PO.DEFMAXEXTS)
, NULL
, --DECODE(PO.DEFMAXSIZE
, NULL
, 'DEFAULT'
, PO.DEFMAXSIZE)
, NULL
, --DECODE(PO.DEFEXTPCT
, NULL
, 'DEFAULT'
, PO.DEFEXTPCT)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, --PO.DEFLISTS
, PO.DEFGROUPS
, DECODE(PO.DEFLOGGING
, 0
, 'NONE'
, 1
, 'YES'
, 2
, 'NO'
, 'UNKNOWN')
, 'N/A'
, NULL
, DECODE(BITAND(PO.SPARE1
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(PO.SPARE1
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(PO.SPARE1
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, NULL -- REF-PARTITIONED IOT IS NOT SUPPORTED SO SKIP THE SUB-QUERY
,
, CASE WHEN (PO.PARTTYPE = 5
AND BITAND(PO.FLAGS
, 64) = 64) THEN 'YES' ELSE PO.INTERVAL_STR END
, 'N/A'
, DECODE(BITAND(PO.FLAGS
, 6144)
, 4096
, 'YES'
, 2048
, 'NO'
, 'NONE')
, DECODE(BITAND(PO.FLAGS
, 8192)
, 8192
, 'OFF'
, 'ON')
, NULL
, NULL
, NULL
, NULL
, NULL FROM SYS.OBJ$ O
, SYS.PARTOBJ$ PO
, SYS.TAB$ T
, SYS.USER$ U WHERE O.OBJ# = PO.OBJ#
AND T.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND BITAND(T.PROPERTY
, 64 + 128) != 0
AND O.SUBNAME IS NULL AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL 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 */) ) )