VIEW: SYS.DBA_LOGSTDBY_EDS_SUPPORTED

Source

View Text - Preformatted

select distinct owner, table_name from
        dba_logstdby_unsupported_table un,
        tab$ t,
        obj$ o,
        user$ u,
        cdef$ c
  where
        /* get a handle on tab$ row to eliminate uninteresting tables */
        o.name = un.table_name and
        o.type# = 2 and
        u.user# = o.owner# and
        un.owner = u.name and
        o.obj# = t.obj# and
        (bitand(t.property, 7) = 2 or   /* not an object table but has an
                                         * object column and no nested-table
                                         * columns:
                                         * 1  -- typed table
                                         * 2  -- has ADT columns
                                         * 4  -- has nested-table columns
                                         */
        bitand(t.property, 21) = 16)    /* has varray columns
                                         * 1  -- typed table
                                         * 4  -- has nested-table columns
                                         * 16 -- has a varray column
                                         */
        and c.obj# = o.obj# and c.type# = 2     /* has a primary key */
        and
        /*
         * evaluate all columns, hidden or not, to determine whether any that
         * are not system generated, including object attributes, fall outside
         * of the supported set
         */
        (un.owner, un.table_name) NOT IN
        (select distinct owner,table_name from dba_tab_cols d where
                d.owner=un.owner and d.table_name=un.table_name
                and
                ((d.data_type_owner IS NULL or
                 d.data_type_owner = 'SYS' or
                 d.data_type_owner = 'MDSYS')
                and d.qualified_col_name not like 'SYS_NC%'
                and d.qualified_col_name not like '"SYS_NC%'
                and d.data_type != 'NUMBER'
                and d.data_type != 'VARCHAR2'
                and d.data_type != 'RAW'
                and d.data_type != 'DATE'
                and d.data_type != 'FLOAT'
                and d.data_type != 'INTEGER'
                and d.data_type != 'CHAR'
                and d.data_type != 'NCHAR'
                and d.data_type != 'NVARCHAR2'
                and d.data_type != 'BINARY_FLOAT'
                and d.data_type != 'BINARY_DOUBLE'
                and not d.data_type LIKE 'TIMESTAMP(%'
                and not d.data_type LIKE 'INTERVAL %'
                and d.data_type != 'SDO_GEOMETRY'
                and d.data_type != 'SDO_ELEM_INFO_ARRAY'
                and d.data_type != 'SDO_ORDINATE_ARRAY'
                and d.data_type != 'XMLTYPE'
                and d.data_type != 'CLOB'
                and d.data_type != 'NCLOB'
                and d.data_type != 'BLOB'
                ) or
                (d.data_type = 'XMLTYPE'        -- disallow XMLTYPE attribute
                and (d.data_type_owner = 'PUBLIC' or d.data_type_owner = 'SYS')
                and d.qualified_col_name != d.column_name)
                )

View Text - HTML Formatted

SELECT DISTINCT OWNER
, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE UN
, TAB$ T
, OBJ$ O
, USER$ U
, CDEF$ C WHERE /* GET A HANDLE ON TAB$ ROW TO ELIMINATE UNINTERESTING TABLES */ O.NAME = UN.TABLE_NAME AND O.TYPE# = 2 AND U.USER# = O.OWNER# AND UN.OWNER = U.NAME AND O.OBJ# = T.OBJ# AND (BITAND(T.PROPERTY
, 7) = 2 OR /* NOT AN OBJECT TABLE BUT HAS AN * OBJECT COLUMN
AND NO NESTED-TABLE * COLUMNS: * 1 -- TYPED TABLE * 2 -- HAS ADT COLUMNS * 4 -- HAS NESTED-TABLE COLUMNS */ BITAND(T.PROPERTY
, 21) = 16) /* HAS VARRAY COLUMNS * 1 -- TYPED TABLE * 4 -- HAS NESTED-TABLE COLUMNS * 16 -- HAS A VARRAY COLUMN */
AND C.OBJ# = O.OBJ#
AND C.TYPE# = 2 /* HAS A PRIMARY KEY */ AND /* * EVALUATE ALL COLUMNS
, HIDDEN OR NOT
, TO DETERMINE WHETHER ANY THAT * ARE NOT SYSTEM GENERATED
, INCLUDING OBJECT ATTRIBUTES
, FALL OUTSIDE * OF THE SUPPORTED SET */ (UN.OWNER
, UN.TABLE_NAME) NOT IN (SELECT DISTINCT OWNER
, TABLE_NAME
FROM DBA_TAB_COLS D WHERE D.OWNER=UN.OWNER
AND D.TABLE_NAME=UN.TABLE_NAME AND ((D.DATA_TYPE_OWNER IS NULL OR D.DATA_TYPE_OWNER = 'SYS' OR D.DATA_TYPE_OWNER = 'MDSYS')
AND D.QUALIFIED_COL_NAME NOT LIKE 'SYS_NC%'
AND D.QUALIFIED_COL_NAME NOT LIKE '"SYS_NC%'
AND D.DATA_TYPE != 'NUMBER'
AND D.DATA_TYPE != 'VARCHAR2'
AND D.DATA_TYPE != 'RAW'
AND D.DATA_TYPE != 'DATE'
AND D.DATA_TYPE != 'FLOAT'
AND D.DATA_TYPE != 'INTEGER'
AND D.DATA_TYPE != 'CHAR'
AND D.DATA_TYPE != 'NCHAR'
AND D.DATA_TYPE != 'NVARCHAR2'
AND D.DATA_TYPE != 'BINARY_FLOAT'
AND D.DATA_TYPE != 'BINARY_DOUBLE'
AND NOT D.DATA_TYPE LIKE 'TIMESTAMP(%'
AND NOT D.DATA_TYPE LIKE 'INTERVAL %'
AND D.DATA_TYPE != 'SDO_GEOMETRY'
AND D.DATA_TYPE != 'SDO_ELEM_INFO_ARRAY'
AND D.DATA_TYPE != 'SDO_ORDINATE_ARRAY'
AND D.DATA_TYPE != 'XMLTYPE'
AND D.DATA_TYPE != 'CLOB'
AND D.DATA_TYPE != 'NCLOB'
AND D.DATA_TYPE != 'BLOB' ) OR (D.DATA_TYPE = 'XMLTYPE' -- DISALLOW XMLTYPE ATTRIBUTE
AND (D.DATA_TYPE_OWNER = 'PUBLIC' OR D.DATA_TYPE_OWNER = 'SYS')
AND D.QUALIFIED_COL_NAME != D.COLUMN_NAME) )