[Home] [Help]
select u.name owner, o.name table_name, c.name column_name,
c.scale, c.precision#, c.charsetform, c.type#,
(case when bitand(t.flags, 536870912) = 536870912
then 'Mapping table for physical rowid of IOT'
when bitand(nvl(s.spare1, 0), 2048) = 2048
then 'Table Compression'
when bitand(t.property, 131072) = 131072
then 'AQ queue table'
when c.type# = 58
then 'Unsupported XML Storage'
when bitand(t.property, 1 ) = 1 /* 0x00000001 typed table */
then 'Object Table'
when bitand(c.property, 65544) != 0
then 'Unsupported Virtual Column'
else null end) attributes,
(case
/* The following are tables that are system maintained */
when bitand(o.flags,
2 /* temporary object */
+ 16 /* secondary object */
+ 32 /* in-memory temp table */
+ 128 /* dropped table (RecycleBin) */
) != 0
or bitand(t.flags,
262144 /* 0x00040000 Summary Container Table, MV */
+ 134217728 /* 0x08000000 in-memory temporary table */
+ 536870912 /* 0x20000000 Mapping Tab for Phys rowid of IOT */
) != 0
or bitand(t.property,
512 /* 0x00000200 iot OVeRflow segment */
+ 8192 /* 0x00002000 nested table */
+ 4194304 /* 0x00400000 global temporary table */
+ 8388608 /* 0x00800000 session-specific temporary table */
+ 33554432 /* 0x02000000 Read Only Materialized View */
+ 67108864 /* 0x04000000 Materialized View table */
+ 134217728 /* 0x08000000 Is a Sub object */
+ 2147483648 /* 0x80000000 eXternal TaBle */
+ 4294967296 /* 0x100000000 Cube */
+ 8589934592 /* 0x200000000 FBA Internal */
) != 0
or bitand(t.trigflag,
536870912 /* 0x20000000 DDLs autofiltered */
) != 0
or exists /* MVLOG table */
(select 1
from sys.mlog$ ml where ml.mowner = u.name and ml.log = o.name)
or exists (select 1 from sys.secobj$ so /* ODCI storage table */
where o.obj# = so.secobj#)
then -1
/* The following tables are data tables in internal schemata *
* that are not secondary objects */
when (exists (select 1 from system.logstdby$skip_support s
where s.name = u.name and action = 0))
then -2
/* The following tables are user visible tables that we choose to *
* skip because of some unsupported attribute of the table or column */
when (bitand(t.property, 1) = 1 /* 0x00000001 typed table */
AND not exists /* Only XML Typed Tables Are Supported */
(select 1
from sys.col$ cc, sys.opqtype$ opq
where cc.name = 'SYS_NC_ROWINFO$' and cc.type# = 58 and
opq.obj# = cc.obj# and opq.intcol# = cc.intcol# and
opq.type = 1 and cc.obj# = t.obj#
and bitand(opq.flags,4) = 4 /* stored as lob */
and bitand(opq.flags,64) = 0 /* not stored as binary */
and bitand(opq.flags,512) = 0 /* not hierarch enab */
and not exists (select 1 from logstdby_support_11lob lb
where lb.obj# = o.obj#
and lb.securefile = 1)))
or bitand(t.property,
131072 /* 0x00020000 table is used as an AQ queue table */
) != 0
or (bitand(nvl(s.spare1,0), 2048) = 2048 /* Compression */
and bitand(t.property, 32) != 32)
or (bitand(t.property, 32) = 32)
and exists (select 1 from partobj$ po
where po.obj#=o.obj#
and (po.parttype in (3, /* System partitioned */
5))) /* Reference partitioned */
or (c.type# not in (
1, /* VARCHAR2 */
2, /* NUMBER */
8, /* LONG */
12, /* DATE */
24, /* LONG RAW */
96, /* CHAR */
100, /* BINARY FLOAT */
101, /* BINARY DOUBLE */
112, /* CLOB and NCLOB */
113, /* BLOB */
180, /* TIMESTAMP (..) */
181, /* TIMESTAMP(..) WITH TIME ZONE */
182, /* INTERVAL YEAR(..) TO MONTH */
183, /* INTERVAL DAY(..) TO SECOND(..) */
231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */
and (c.type# != 23 /* RAW not RAW OID */
or (c.type# = 23 and bitand(c.property, 2) = 2))
and (c.type# != 58 /* OPAQUE */
or (c.type# = 58 /* XMLTYPE as CLOB */
and not exists (select 1 from opqtype$ opq
where opq.type=1
and bitand(opq.flags, 4) = 4 /* stored as lob */
and bitand(opq.flags,64) = 0 /* not stored as binary */
and bitand(opq.flags,512) = 0 /* not hierarch enab */
and opq.obj#=c.obj#
and opq.intcol#=c.intcol#
and not exists ( select 1 from logstdby_support_11lob lb
where lb.obj# = c.obj#
and lb.col# = c.col#
and lb.securefile = 1)
))))
----------------------------------------------------------
/* longs must have a scalar column to use as the id key */
or (c.type# in (8,24,58,112,113)
and bitand(t.property, 1) = 0 /* typed table has an OID */
and 0 = (select count(*) from sys.col$ c2
where t.obj# = c2.obj#
and bitand(c2.property, 32) != 32 /* Not hidden */
and (c2.type# in ( 1, /* VARCHAR2 */
2, /* NUMBER */
12, /* DATE */
23, /* RAW */
96, /* CHAR */
100, /* BINARY FLOAT */
101, /* BINARY DOUBLE */
180, /* TIMESTAMP (..) */
181, /* TIMESTAMP(..) WITH TIME ZONE */
182, /* INTERVAL YEAR(..) TO MONTH */
183, /* INTERVAL DAY(..) TO SECOND(..) */
231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */
)))
----------------------------------------------------------
/* we don't support securefile */
or (c.type# in (112, 113)
and exists (select 1 from logstdby_support_11lob lb
where lb.obj# = o.obj#
and lb.col# = c.col#
and lb.securefile = 1))
----------------------------------------------------------
/* we don't support virtual column candidate key */
or (bitand(c.property, 65544) != 0 /* Virtual Column */
and bitand(c.property, 32) != 32 /* Not hidden */
and exists (select 1 from icol$ ic, ind$ i
where ic.bo# = t.obj# and ic.col# = c.col#
and i.bo# = t.obj# and i.obj# = ic.obj#
and bitand(i.property, 1) = 1)) /* Unique Index */
----------------------------------------------------------
then 0 else 1 end) gensby
from sys.obj$ o, sys.user$ u, sys.tab$ t, sys.seg$ s, sys.col$ c
where o.owner# = u.user#
and o.obj# = t.obj#
and o.obj# = c.obj#
and t.file# = s.file# (+)
and t.ts# = s.ts# (+)
and t.block# = s.block# (+)
and t.obj# = o.obj#
and bitand(c.property, 32) != 32 /* Not hidden */
SELECT U.NAME OWNER
, O.NAME TABLE_NAME
, C.NAME COLUMN_NAME
,
C.SCALE
, C.PRECISION#
, C.CHARSETFORM
, C.TYPE#
,
(CASE WHEN BITAND(T.FLAGS
, 536870912) = 536870912
THEN 'MAPPING TABLE FOR PHYSICAL ROWID OF IOT'
WHEN BITAND(NVL(S.SPARE1
, 0)
, 2048) = 2048
THEN 'TABLE COMPRESSION'
WHEN BITAND(T.PROPERTY
, 131072) = 131072
THEN 'AQ QUEUE TABLE'
WHEN C.TYPE# = 58
THEN 'UNSUPPORTED XML STORAGE'
WHEN BITAND(T.PROPERTY
, 1 ) = 1 /* 0X00000001 TYPED TABLE */
THEN 'OBJECT TABLE'
WHEN BITAND(C.PROPERTY
, 65544) != 0
THEN 'UNSUPPORTED VIRTUAL COLUMN'
ELSE NULL END) ATTRIBUTES
,
(CASE
/* THE FOLLOWING ARE TABLES THAT ARE SYSTEM MAINTAINED */
WHEN BITAND(O.FLAGS
,
2 /* TEMPORARY OBJECT */
+ 16 /* SECONDARY OBJECT */
+ 32 /* IN-MEMORY TEMP TABLE */
+ 128 /* DROPPED TABLE (RECYCLEBIN) */
) != 0
OR BITAND(T.FLAGS
,
262144 /* 0X00040000 SUMMARY CONTAINER TABLE
, MV */
+ 134217728 /* 0X08000000 IN-MEMORY TEMPORARY TABLE */
+ 536870912 /* 0X20000000 MAPPING TAB FOR PHYS ROWID OF IOT */
) != 0
OR BITAND(T.PROPERTY
,
512 /* 0X00000200 IOT OVERFLOW SEGMENT */
+ 8192 /* 0X00002000 NESTED TABLE */
+ 4194304 /* 0X00400000 GLOBAL TEMPORARY TABLE */
+ 8388608 /* 0X00800000 SESSION-SPECIFIC TEMPORARY TABLE */
+ 33554432 /* 0X02000000 READ ONLY MATERIALIZED VIEW */
+ 67108864 /* 0X04000000 MATERIALIZED VIEW TABLE */
+ 134217728 /* 0X08000000 IS A SUB OBJECT */
+ 2147483648 /* 0X80000000 EXTERNAL TABLE */
+ 4294967296 /* 0X100000000 CUBE */
+ 8589934592 /* 0X200000000 FBA INTERNAL */
) != 0
OR BITAND(T.TRIGFLAG
,
536870912 /* 0X20000000 DDLS AUTOFILTERED */
) != 0
OR EXISTS /* MVLOG TABLE */
(SELECT 1
FROM SYS.MLOG$ ML
WHERE ML.MOWNER = U.NAME
AND ML.LOG = O.NAME)
OR EXISTS (SELECT 1
FROM SYS.SECOBJ$ SO /* ODCI STORAGE TABLE */
WHERE O.OBJ# = SO.SECOBJ#)
THEN -1
/* THE FOLLOWING TABLES ARE DATA TABLES IN INTERNAL SCHEMATA *
* THAT ARE NOT SECONDARY OBJECTS */
WHEN (EXISTS (SELECT 1
FROM SYSTEM.LOGSTDBY$SKIP_SUPPORT S
WHERE S.NAME = U.NAME
AND ACTION = 0))
THEN -2
/* THE FOLLOWING TABLES ARE USER VISIBLE TABLES THAT WE CHOOSE TO *
* SKIP BECAUSE OF SOME UNSUPPORTED ATTRIBUTE OF THE TABLE OR COLUMN */
WHEN (BITAND(T.PROPERTY
, 1) = 1 /* 0X00000001 TYPED TABLE */
AND NOT EXISTS /* ONLY XML TYPED TABLES ARE SUPPORTED */
(SELECT 1
FROM SYS.COL$ CC
, SYS.OPQTYPE$ OPQ
WHERE CC.NAME = 'SYS_NC_ROWINFO$'
AND CC.TYPE# = 58 AND
OPQ.OBJ# = CC.OBJ#
AND OPQ.INTCOL# = CC.INTCOL# AND
OPQ.TYPE = 1
AND CC.OBJ# = T.OBJ#
AND BITAND(OPQ.FLAGS
, 4) = 4 /* STORED AS LOB */
AND BITAND(OPQ.FLAGS
, 64) = 0 /* NOT STORED AS BINARY */
AND BITAND(OPQ.FLAGS
, 512) = 0 /* NOT HIERARCH ENAB */
AND NOT EXISTS (SELECT 1
FROM LOGSTDBY_SUPPORT_11LOB LB
WHERE LB.OBJ# = O.OBJ#
AND LB.SECUREFILE = 1)))
OR BITAND(T.PROPERTY
,
131072 /* 0X00020000 TABLE IS USED AS AN AQ QUEUE TABLE */
) != 0
OR (BITAND(NVL(S.SPARE1
, 0)
, 2048) = 2048 /* COMPRESSION */
AND BITAND(T.PROPERTY
, 32) != 32)
OR (BITAND(T.PROPERTY
, 32) = 32)
AND EXISTS (SELECT 1
FROM PARTOBJ$ PO
WHERE PO.OBJ#=O.OBJ#
AND (PO.PARTTYPE IN (3
, /* SYSTEM PARTITIONED */
5))) /* REFERENCE PARTITIONED */
OR (C.TYPE# NOT IN (
1
, /* VARCHAR2 */
2
, /* NUMBER */
8
, /* LONG */
12
, /* DATE */
24
, /* LONG RAW */
96
, /* CHAR */
100
, /* BINARY FLOAT */
101
, /* BINARY DOUBLE */
112
, /* CLOB
AND NCLOB */
113
, /* BLOB */
180
, /* TIMESTAMP (..) */
181
, /* TIMESTAMP(..) WITH TIME ZONE */
182
, /* INTERVAL YEAR(..) TO MONTH */
183
, /* INTERVAL DAY(..) TO SECOND(..) */
231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */
AND (C.TYPE# != 23 /* RAW NOT RAW OID */
OR (C.TYPE# = 23
AND BITAND(C.PROPERTY
, 2) = 2))
AND (C.TYPE# != 58 /* OPAQUE */
OR (C.TYPE# = 58 /* XMLTYPE AS CLOB */
AND NOT EXISTS (SELECT 1
FROM OPQTYPE$ OPQ
WHERE OPQ.TYPE=1
AND BITAND(OPQ.FLAGS
, 4) = 4 /* STORED AS LOB */
AND BITAND(OPQ.FLAGS
, 64) = 0 /* NOT STORED AS BINARY */
AND BITAND(OPQ.FLAGS
, 512) = 0 /* NOT HIERARCH ENAB */
AND OPQ.OBJ#=C.OBJ#
AND OPQ.INTCOL#=C.INTCOL#
AND NOT EXISTS ( SELECT 1
FROM LOGSTDBY_SUPPORT_11LOB LB
WHERE LB.OBJ# = C.OBJ#
AND LB.COL# = C.COL#
AND LB.SECUREFILE = 1)
))))
----------------------------------------------------------
/* LONGS MUST HAVE A SCALAR COLUMN TO USE AS THE ID KEY */
OR (C.TYPE# IN (8
, 24
, 58
, 112
, 113)
AND BITAND(T.PROPERTY
, 1) = 0 /* TYPED TABLE HAS AN OID */
AND 0 = (SELECT COUNT(*)
FROM SYS.COL$ C2
WHERE T.OBJ# = C2.OBJ#
AND BITAND(C2.PROPERTY
, 32) != 32 /* NOT HIDDEN */
AND (C2.TYPE# IN ( 1
, /* VARCHAR2 */
2
, /* NUMBER */
12
, /* DATE */
23
, /* RAW */
96
, /* CHAR */
100
, /* BINARY FLOAT */
101
, /* BINARY DOUBLE */
180
, /* TIMESTAMP (..) */
181
, /* TIMESTAMP(..) WITH TIME ZONE */
182
, /* INTERVAL YEAR(..) TO MONTH */
183
, /* INTERVAL DAY(..) TO SECOND(..) */
231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */
)))
----------------------------------------------------------
/* WE DON'T SUPPORT SECUREFILE */
OR (C.TYPE# IN (112
, 113)
AND EXISTS (SELECT 1
FROM LOGSTDBY_SUPPORT_11LOB LB
WHERE LB.OBJ# = O.OBJ#
AND LB.COL# = C.COL#
AND LB.SECUREFILE = 1))
----------------------------------------------------------
/* WE DON'T SUPPORT VIRTUAL COLUMN CANDIDATE KEY */
OR (BITAND(C.PROPERTY
, 65544) != 0 /* VIRTUAL COLUMN */
AND BITAND(C.PROPERTY
, 32) != 32 /* NOT HIDDEN */
AND EXISTS (SELECT 1
FROM ICOL$ IC
, IND$ I
WHERE IC.BO# = T.OBJ#
AND IC.COL# = C.COL#
AND I.BO# = T.OBJ#
AND I.OBJ# = IC.OBJ#
AND BITAND(I.PROPERTY
, 1) = 1)) /* UNIQUE INDEX */
----------------------------------------------------------
THEN 0 ELSE 1 END) GENSBY
FROM SYS.OBJ$ O
, SYS.USER$ U
, SYS.TAB$ T
, SYS.SEG$ S
, SYS.COL$ C
WHERE O.OWNER# = U.USER#
AND O.OBJ# = T.OBJ#
AND O.OBJ# = C.OBJ#
AND T.FILE# = S.FILE# (+)
AND T.TS# = S.TS# (+)
AND T.BLOCK# = S.BLOCK# (+)
AND T.OBJ# = O.OBJ#
AND BITAND(C.PROPERTY
, 32) != 32 /* NOT HIDDEN */
|
|
|
|