0

I would like to ask what are the number presenting in Property column in sys.col$ table? When I count distinct, it returns 39 rows. And base on my own research, I know for sure that the property = 0, it means that column uses Byte semantic and property = 8388608, it uses Char semantic. Below is a query that I found on the internet:

"select DECODE (type#, 1, DECODE (BITAND (property, 8388608), 0, 'B', 'C'), 96, DECODE (BITAND (property, 8388608), 0, 'B', 'C'), Null) from sys.col$;"

Again, I try to understand what other numbers are.

Thank you so much

Khang

1 Answer 1

1

Does it really matter? It is an undocumented internal dictionary table. It is a number representing some flags set.

Just have a look at the definition of views such as DBA_TAB_COLS_V$.

select dbms_metadata.get_ddl('VIEW', 'DBA_TAB_COLS_V$', 'SYS') from dual;

There you can find stuff like below, example:

   ...
   decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                  96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                  null),
   decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
   decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
                    decode(bitand(ac.flags, 2), 2, 'NO',
                           decode(bitand(ac.flags, 4), 4, 'NO',
                                  decode(bitand(ac.flags, 8), 8, 'NO',
                                         'N/A')))),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
                                      'NO')),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
                                      'NO')),
   decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
   -- warning! If you update stats related info, make sure to also update
   -- GTT session private stats in cdoptim.sql
   case when nvl(h.row_cnt,0) = 0 then 'NONE'
        when exists(select 1 from sys.histgrm$ hg
                    where c.obj# = hg.obj# and c.intcol# = hg.intcol#
                      and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'
        when bitand(h.spare2, 64) > 0
          then 'TOP-FREQUENCY'
        when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
              (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
            then 'FREQUENCY'
        else 'HEIGHT BALANCED'
   end,                                                          /* HISTOGRAM */
   decode(bitand(c.property, 1024), 1024,
          (select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
           from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
           and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
           cl.intcol# = rc.intcol#(+)),
          decode(bitand(c.property, 1), 0, c.name,
                 (select tc.name from sys.attrcol$ tc
                  where c.obj# = tc.obj# and c.intcol# = tc.intcol#))),
   decode(bitand(c.property, 17179869184), 17179869184, 'YES',
          decode(bitand(c.property, 32), 32, 'NO', 'YES')),
   decode(bitand(c.property, 68719476736), 68719476736, 'YES', 'NO'),
   decode(bitand(c.property, 137438953472 + 274877906944),
                 137438953472, 'YES', 274877906944, 'YES', 'NO'),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 8796093022208),
                                      8796093022208, 'YES', 'NO')),
   ...
2
  • Hi Balazs, yes currently I am working on some queries that need to filter using sys.col$ table. And I try to find the meaning of each value from that Property column. And because I am new to Oracle so I try my best to find document mentions about it. Again, Thanks for sharing. Khang
    – Khang Mai
    Mar 19, 2020 at 23:45
  • And since this is an internal table, the various bits and bytes could change meanings in newer versions, so beware! Mar 31, 2020 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.