VIEW: SYS.USER_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 table_name, qualified_col_name
from
   ( select utc.table_name, utc.qualified_col_name, data_type
     from user_tab_cols utc, user_all_tables uat
     where (data_type like 'TIMESTAMP%WITH TIME ZONE' or
            data_type in (select name from va_of_tstz_typ))
       and utc.table_name = uat.table_name
     union all
     select table_name, qualified_col_name, data_type
     from user_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 TABLE_NAME
, QUALIFIED_COL_NAME FROM ( SELECT UTC.TABLE_NAME
, UTC.QUALIFIED_COL_NAME
, DATA_TYPE
FROM USER_TAB_COLS UTC
, USER_ALL_TABLES UAT
WHERE (DATA_TYPE LIKE 'TIMESTAMP%WITH TIME ZONE' OR DATA_TYPE IN (SELECT NAME
FROM VA_OF_TSTZ_TYP))
AND UTC.TABLE_NAME = UAT.TABLE_NAME UNION ALL SELECT TABLE_NAME
, QUALIFIED_COL_NAME
, DATA_TYPE
FROM USER_NESTED_TABLE_COLS
WHERE DATA_TYPE LIKE 'TIMESTAMP%WITH TIME ZONE' OR DATA_TYPE IN (SELECT NAME
FROM VA_OF_TSTZ_TYP) )