VIEW: SYS.KU$_PCOLUMN_VIEW

Source

View Text - Preformatted

select c.obj#, c.col#, c.segcol#,
         c.segcollength, c.offset, c.name,
         (select a.name from attrcol$ a where
                        a.obj#=c.obj# and a.intcol#=c.intcol#),
         c.type#, c.length, c.fixedstorage,
         c.precision#, c.scale, c.null$, c.deflength,
         sys.dbms_metadata_util.long2varchar(c.deflength,
                                        'SYS.COL$',
                                        'DEFAULT$',
                                        c.rowid),
         sys.dbms_metadata_util.blob2clob(c.obj#, c.intcol#, c.property),
         c.intcol#,
         case c.col# when c.intcol# then c.intcol#
                     when 0 then c.intcol#
          else sys.dbms_metadata_util.get_base_intcol_num(c.obj#,c.col#,
                                                          c.intcol#,c.type#)
         end,
         case c.col# when 0 then 0
          else
            sys.dbms_metadata_util.get_base_col_type(c.obj#,c.col#,
                                                          c.intcol#,c.type#)
         end,
         case c.col# when c.intcol# then NULL
                     when 0 then NULL
          else
            sys.dbms_metadata_util.get_base_col_name(c.obj#,c.col#,
                                                          c.intcol#,c.type#)
         end,
         bitand(c.property, 4294967295),
         trunc(c.property / power(2,32)),
         c.charsetid, c.charsetform,
         ( select value(cv)
             from ku$_constraint0_view cv, ku$_constraint_col_view ccv
             where c.intcol# = ccv.intcol_num
             and c.obj# = ccv.obj_num
             and ccv.con_num = cv.con_num
             and cv.contype in (7,11)
         ),
         ( select value(ctv)
             from ku$_coltype_view ctv
             where c.type# in ( 121,    -- DTYADT  (user-defined type)
                                122,    -- DTYNTB  (nested table)
                                123,    -- DTYNAR  (varray)
                                111,    -- DTYIREF (REF)
                                 58)    -- DTYOPQ  (opaque type)
             and   c.obj#  = ctv.obj_num
             and   c.intcol# = ctv.intcol_num
         ),
         ( select value(lv)
             from ku$_lob_view lv
             where (c.type# in (112,    -- CLOB
                                113,    -- BLOB
                                123)    -- DTYNAR  (varray)
                    and   c.obj#  = lv.obj_num
                    and   c.intcol# = lv.intcol_num)
             or    (c.type# = 58        -- DTYOPQ (XML type)
                    and   c.obj#  = lv.obj_num
                    and   lv.intcol_num =
                          (select op.lobcol from sys.opqtype$ op
                                    where op.obj# = c.obj#
                                    and   bitand(op.flags,4) != 0
                                    and   op.intcol# = c.intcol#)
                    )
             or    (c.type# = 58        -- DTYOPQ (opaque type)
                    and   c.obj#  = lv.obj_num
                    and   c.intcol# = lv.intcol_num
                    and   EXISTS (
                          SELECT  1
                          FROM    sys.opqtype$ op
                          WHERE  op.obj# = c.obj#
                                 and   op.intcol# = c.intcol#
                                 and   op.type = 0 )
                    )
             or    (c.type# in (1,          -- VARCHAR2
                                23)         -- RAW
                    and   bitand(c.property,128)!=0 -- stored as lob (long var)
                    and   c.obj#  = lv.obj_num
                    and   c.intcol# = lv.intcol_num
                    )
         ),
         ( select value(lv)
             from ku$_partlob_view lv
             where (c.type# in (112,    -- CLOB
                                113,    -- BLOB
                                123)    -- DTYNAR  (varray)
                    and   c.obj#  = lv.obj_num
                    and   c.intcol# = lv.intcol_num)
             or    (c.type# = 58        -- DTYOPQ (XML type)
                    and   c.obj#  = lv.obj_num
                    and   lv.intcol_num =
                          (select op.lobcol from sys.opqtype$ op
                                    where op.obj# = c.obj#
                                    and   bitand(op.flags,4) != 0
                                    and   op.intcol# = c.intcol#)
                    )
             or    (c.type# = 58        -- DTYOPQ (opaque type)
                    and   c.obj#  = lv.obj_num
                    and   c.intcol# = lv.intcol_num
                    and   EXISTS (
                          SELECT  1
                          FROM    sys.opqtype$ op
                          WHERE  op.obj# = c.obj#
                                 and   op.intcol# = c.intcol#
                                 and   op.type = 0 )
                    )
             or    (c.type# in (1,          -- VARCHAR2
                                23)         -- RAW
                    and   bitand(c.property,128)!=0 -- stored as lob (long var)
                    and   c.obj#  = lv.obj_num
                    and   c.intcol# = lv.intcol_num
                    )
         ),
         ( select value(opq) from sys.ku$_opqtype_view opq
             where c.type# = 58        -- DTYOPQ (opaque type)
             and   c.obj# = opq.obj_num
             and   c.intcol# = opq.intcol_num
         ),
         ( select value(oi)
             from ku$_oidindex_view oi
             where bitand(c.property, 2) = 2
             and   c.obj# = oi.obj_num
             and   c.intcol# = oi.intcol_num
         ),
         c.spare1, c.spare2, c.spare3, c.spare4, c.spare5,
         to_char(c.spare6, 'YYYY/MM/DD HH24:MI:SS'),
         case when (bitand(c.property,137438953472+274877906944)!=0) then
             (select value(i) from ku$_identity_col_view i
                              where i.obj_num = c.obj#)
         else null end,
         -- If column has the properties ((ADT attribute, hidden, system
         -- generated) or (type id, ADT attribute, hidden)), then
         -- this column may be part of an unpacked anydata type.
         case when (bitand(c.property,289) = 289  or
                    bitand(c.property,33554465) = 33554465) then
            sys.dbms_metadata_util.get_attrname2(c.obj#, c.intcol#, c.col#)
          else
            NULL
         end,
         -- Column sortkey: in principle we want to sort by segcol#, but
         -- segcol# for xmltype is 0 so replace it with the segcol# of its
         -- underlying lob or object rel column that contains the actual
         -- data.  This query needs to be identical to the one for the
         -- col_sorkey column in ku$_strmtable_view in order to ensure that
         -- lob columns are ordered identically when writing to and reading
         -- from dump files (bug# 12998987, 17627666).
         case when (c.segcol# = 0 and c.type# = 58) then
          NVL((select cc.segcol# from col$ cc, opqtype$ opq
              where opq.obj#=c.obj#
                and opq.intcol#=c.intcol#
                and opq.type=1
                and cc.intcol#=opq.lobcol    -- xmltype stored as lob
                and cc.obj#=c.obj#),
                (NVL((select cc.segcol# from col$ cc, opqtype$ opq
                      where opq.obj#=c.obj#
                        and opq.intcol#=c.intcol#
                        and opq.type=1
                        and cc.intcol#=opq.objcol  -- xmltype stored obj rel
                        and bitand(opq.flags,1)=1
                        and cc.obj#=c.obj#),0)))
          else c.segcol#
         end
  from col$ c

View Text - HTML Formatted

SELECT C.OBJ#
, C.COL#
, C.SEGCOL#
, C.SEGCOLLENGTH
, C.OFFSET
, C.NAME
, (SELECT A.NAME
FROM ATTRCOL$ A WHERE A.OBJ#=C.OBJ#
AND A.INTCOL#=C.INTCOL#)
, C.TYPE#
, C.LENGTH
, C.FIXEDSTORAGE
, C.PRECISION#
, C.SCALE
, C.NULL$
, C.DEFLENGTH
, SYS.DBMS_METADATA_UTIL.LONG2VARCHAR(C.DEFLENGTH
, 'SYS.COL$'
, 'DEFAULT$'
, C.ROWID)
, SYS.DBMS_METADATA_UTIL.BLOB2CLOB(C.OBJ#
, C.INTCOL#
, C.PROPERTY)
, C.INTCOL#
, CASE C.COL# WHEN C.INTCOL# THEN C.INTCOL# WHEN 0 THEN C.INTCOL# ELSE SYS.DBMS_METADATA_UTIL.GET_BASE_INTCOL_NUM(C.OBJ#
, C.COL#
, C.INTCOL#
, C.TYPE#) END
, CASE C.COL# WHEN 0 THEN 0 ELSE SYS.DBMS_METADATA_UTIL.GET_BASE_COL_TYPE(C.OBJ#
, C.COL#
, C.INTCOL#
, C.TYPE#) END
, CASE C.COL# WHEN C.INTCOL# THEN NULL WHEN 0 THEN NULL ELSE SYS.DBMS_METADATA_UTIL.GET_BASE_COL_NAME(C.OBJ#
, C.COL#
, C.INTCOL#
, C.TYPE#) END
, BITAND(C.PROPERTY
, 4294967295)
, TRUNC(C.PROPERTY / POWER(2
, 32))
, C.CHARSETID
, C.CHARSETFORM
, ( SELECT VALUE(CV)
FROM KU$_CONSTRAINT0_VIEW CV
, KU$_CONSTRAINT_COL_VIEW CCV
WHERE C.INTCOL# = CCV.INTCOL_NUM
AND C.OBJ# = CCV.OBJ_NUM
AND CCV.CON_NUM = CV.CON_NUM
AND CV.CONTYPE IN (7
, 11) )
, ( SELECT VALUE(CTV)
FROM KU$_COLTYPE_VIEW CTV
WHERE C.TYPE# IN ( 121
, -- DTYADT (USER-DEFINED TYPE) 122
, -- DTYNTB (NESTED TABLE) 123
, -- DTYNAR (VARRAY) 111
, -- DTYIREF (REF) 58) -- DTYOPQ (OPAQUE TYPE)
AND C.OBJ# = CTV.OBJ_NUM
AND C.INTCOL# = CTV.INTCOL_NUM )
, ( SELECT VALUE(LV)
FROM KU$_LOB_VIEW LV
WHERE (C.TYPE# IN (112
, -- CLOB 113
, -- BLOB 123) -- DTYNAR (VARRAY)
AND C.OBJ# = LV.OBJ_NUM
AND C.INTCOL# = LV.INTCOL_NUM) OR (C.TYPE# = 58 -- DTYOPQ (XML TYPE)
AND C.OBJ# = LV.OBJ_NUM
AND LV.INTCOL_NUM = (SELECT OP.LOBCOL
FROM SYS.OPQTYPE$ OP
WHERE OP.OBJ# = C.OBJ#
AND BITAND(OP.FLAGS
, 4) != 0
AND OP.INTCOL# = C.INTCOL#) ) OR (C.TYPE# = 58 -- DTYOPQ (OPAQUE TYPE)
AND C.OBJ# = LV.OBJ_NUM
AND C.INTCOL# = LV.INTCOL_NUM
AND EXISTS ( SELECT 1
FROM SYS.OPQTYPE$ OP
WHERE OP.OBJ# = C.OBJ#
AND OP.INTCOL# = C.INTCOL#
AND OP.TYPE = 0 ) ) OR (C.TYPE# IN (1
, -- VARCHAR2 23) -- RAW
AND BITAND(C.PROPERTY
, 128)!=0 -- STORED AS LOB (LONG VAR)
AND C.OBJ# = LV.OBJ_NUM
AND C.INTCOL# = LV.INTCOL_NUM ) )
, ( SELECT VALUE(LV)
FROM KU$_PARTLOB_VIEW LV
WHERE (C.TYPE# IN (112
, -- CLOB 113
, -- BLOB 123) -- DTYNAR (VARRAY)
AND C.OBJ# = LV.OBJ_NUM
AND C.INTCOL# = LV.INTCOL_NUM) OR (C.TYPE# = 58 -- DTYOPQ (XML TYPE)
AND C.OBJ# = LV.OBJ_NUM
AND LV.INTCOL_NUM = (SELECT OP.LOBCOL
FROM SYS.OPQTYPE$ OP
WHERE OP.OBJ# = C.OBJ#
AND BITAND(OP.FLAGS
, 4) != 0
AND OP.INTCOL# = C.INTCOL#) ) OR (C.TYPE# = 58 -- DTYOPQ (OPAQUE TYPE)
AND C.OBJ# = LV.OBJ_NUM
AND C.INTCOL# = LV.INTCOL_NUM
AND EXISTS ( SELECT 1
FROM SYS.OPQTYPE$ OP
WHERE OP.OBJ# = C.OBJ#
AND OP.INTCOL# = C.INTCOL#
AND OP.TYPE = 0 ) ) OR (C.TYPE# IN (1
, -- VARCHAR2 23) -- RAW
AND BITAND(C.PROPERTY
, 128)!=0 -- STORED AS LOB (LONG VAR)
AND C.OBJ# = LV.OBJ_NUM
AND C.INTCOL# = LV.INTCOL_NUM ) )
, ( SELECT VALUE(OPQ)
FROM SYS.KU$_OPQTYPE_VIEW OPQ
WHERE C.TYPE# = 58 -- DTYOPQ (OPAQUE TYPE)
AND C.OBJ# = OPQ.OBJ_NUM
AND C.INTCOL# = OPQ.INTCOL_NUM )
, ( SELECT VALUE(OI)
FROM KU$_OIDINDEX_VIEW OI
WHERE BITAND(C.PROPERTY
, 2) = 2
AND C.OBJ# = OI.OBJ_NUM
AND C.INTCOL# = OI.INTCOL_NUM )
, C.SPARE1
, C.SPARE2
, C.SPARE3
, C.SPARE4
, C.SPARE5
, TO_CHAR(C.SPARE6
, 'YYYY/MM/DD HH24:MI:SS')
, CASE WHEN (BITAND(C.PROPERTY
, 137438953472+274877906944)!=0) THEN (SELECT VALUE(I)
FROM KU$_IDENTITY_COL_VIEW I
WHERE I.OBJ_NUM = C.OBJ#) ELSE NULL END
, -- IF COLUMN HAS THE PROPERTIES ((ADT ATTRIBUTE
, HIDDEN
, SYSTEM -- GENERATED) OR (TYPE ID
, ADT ATTRIBUTE
, HIDDEN))
, THEN -- THIS COLUMN MAY BE PART OF AN UNPACKED ANYDATA TYPE. CASE WHEN (BITAND(C.PROPERTY
, 289) = 289 OR BITAND(C.PROPERTY
, 33554465) = 33554465) THEN SYS.DBMS_METADATA_UTIL.GET_ATTRNAME2(C.OBJ#
, C.INTCOL#
, C.COL#) ELSE NULL END
, -- COLUMN SORTKEY: IN PRINCIPLE WE WANT TO SORT BY SEGCOL#
, BUT -- SEGCOL# FOR XMLTYPE IS 0 SO REPLACE IT WITH THE SEGCOL# OF ITS -- UNDERLYING LOB OR OBJECT REL COLUMN THAT CONTAINS THE ACTUAL -- DATA. THIS QUERY NEEDS TO BE IDENTICAL TO THE ONE FOR THE -- COL_SORKEY COLUMN IN KU$_STRMTABLE_VIEW IN ORDER TO ENSURE THAT -- LOB COLUMNS ARE ORDERED IDENTICALLY WHEN WRITING TO
AND READING --
FROM DUMP FILES (BUG# 12998987
, 17627666). CASE WHEN (C.SEGCOL# = 0
AND C.TYPE# = 58) THEN NVL((SELECT CC.SEGCOL#
FROM COL$ CC
, OPQTYPE$ OPQ
WHERE OPQ.OBJ#=C.OBJ#
AND OPQ.INTCOL#=C.INTCOL#
AND OPQ.TYPE=1
AND CC.INTCOL#=OPQ.LOBCOL -- XMLTYPE STORED AS LOB
AND CC.OBJ#=C.OBJ#)
, (NVL((SELECT CC.SEGCOL#
FROM COL$ CC
, OPQTYPE$ OPQ
WHERE OPQ.OBJ#=C.OBJ#
AND OPQ.INTCOL#=C.INTCOL#
AND OPQ.TYPE=1
AND CC.INTCOL#=OPQ.OBJCOL -- XMLTYPE STORED OBJ REL
AND BITAND(OPQ.FLAGS
, 1)=1
AND CC.OBJ#=C.OBJ#)
, 0))) ELSE C.SEGCOL# END
FROM COL$ C