VIEW: SYS.DBA_TSTZ_TAB_COLS

Source

View Text - Preformatted

with va_of_tstz_typ as
(select distinct o.name
 from
    ( select p_obj# obj# from sys.dependency$
      start with p_obj# in (
         select distinct o.obj#
         from sys.obj$ o, sys.attribute$ a
         where o.oid$ = a.toid
           and a.attr_toid = '0000000000000000000000000000003E'
         union all
         select distinct o.obj#
         from sys.obj$ o, sys.collection$ c
         where o.oid$ = c.toid
           and c.elem_toid = '0000000000000000000000000000003E'
       )
      connect by prior d_obj# = p_obj# and bitand(prior property, 1) = 1
      order siblings by d_obj#, p_obj#
     ) t, sys.obj$ o, sys.coltype$ c
 where t.obj# = o.obj#
   and o.oid$ = c.toid
   and bitand(c.flags, 8) = 8
)
select owner, table_name, qualified_col_name
from
   ( select dtc.owner, dtc.table_name, dtc.qualified_col_name, data_type
     from dba_tab_cols dtc, dba_all_tables dat
     where (data_type like 'TIMESTAMP%WITH TIME ZONE' or
            data_type in (select name from va_of_tstz_typ))
       and dtc.owner = dat.owner
       and dtc.table_name = dat.table_name
     union all
     select owner, table_name, qualified_col_name, data_type
     from dba_nested_table_cols
     where data_type like 'TIMESTAMP%WITH TIME ZONE'
        or data_type in (select name from va_of_tstz_typ)
   )

View Text - HTML Formatted

WITH VA_OF_TSTZ_TYP AS (SELECT DISTINCT O.NAME FROM ( SELECT P_OBJ# OBJ#
FROM SYS.DEPENDENCY$ START WITH P_OBJ# IN ( SELECT DISTINCT O.OBJ#
FROM SYS.OBJ$ O
, SYS.ATTRIBUTE$ A
WHERE O.OID$ = A.TOID
AND A.ATTR_TOID = '0000000000000000000000000000003E' UNION ALL SELECT DISTINCT O.OBJ#
FROM SYS.OBJ$ O
, SYS.COLLECTION$ C
WHERE O.OID$ = C.TOID
AND C.ELEM_TOID = '0000000000000000000000000000003E' ) CONNECT BY PRIOR D_OBJ# = P_OBJ#
AND BITAND(PRIOR PROPERTY
, 1) = 1 ORDER SIBLINGS BY D_OBJ#
, P_OBJ# ) T
, SYS.OBJ$ O
, SYS.COLTYPE$ C
WHERE T.OBJ# = O.OBJ#
AND O.OID$ = C.TOID
AND BITAND(C.FLAGS
, 8) = 8 ) SELECT OWNER
, TABLE_NAME
, QUALIFIED_COL_NAME FROM ( SELECT DTC.OWNER
, DTC.TABLE_NAME
, DTC.QUALIFIED_COL_NAME
, DATA_TYPE
FROM DBA_TAB_COLS DTC
, DBA_ALL_TABLES DAT
WHERE (DATA_TYPE LIKE 'TIMESTAMP%WITH TIME ZONE' OR DATA_TYPE IN (SELECT NAME
FROM VA_OF_TSTZ_TYP))
AND DTC.OWNER = DAT.OWNER
AND DTC.TABLE_NAME = DAT.TABLE_NAME UNION ALL SELECT OWNER
, TABLE_NAME
, QUALIFIED_COL_NAME
, DATA_TYPE
FROM DBA_NESTED_TABLE_COLS
WHERE DATA_TYPE LIKE 'TIMESTAMP%WITH TIME ZONE' OR DATA_TYPE IN (SELECT NAME
FROM VA_OF_TSTZ_TYP) )