I just received a tip about a query that explicitly lists a constraint with its underlying index. Very nice. One of the tables referenced in this query is CDEF$. This table has a column called TYPE# which has numbers that varies from 1 to 12, apparently categorizes the constraints into types. After an search of available on-line documentation, I just can not find anything that explains just what these numbers mean. Could be the numbers are not that meaningful, but the query in the tip included them so I am thinking these could convey useful information. Can anyone provide some insight? Thank you.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
About cdef$
- Thread starter CRoberts
- Start date
I can get you partly there....
Look at the text for the DBA_CONSTRAINTS view.
It decodes cdef#.TYPE# into single character values.
========================================================
select ou.name, oc.name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', '?'),
o.name, c.condition, ru.name, rc.name,
decode(c.type#, 4,
decode(c.refact, 1, 'CASCADE', 'NO ACTION'), NULL),
decode(c.type#, 5, 'ENABLED',
decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),
decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
decode(bitand(c.defer,16),16, 'BAD', null),
decode(bitand(c.defer,32),32, 'RELY', null),
c.mtime
from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru,
sys.obj$ o, sys.cdef$ c
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.type# != 8 /* don't include hash expressions */
and c.rcon# = rc.con#(+)
and rc.owner# = ru.user#(+)
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
Look at the text for the DBA_CONSTRAINTS view.
It decodes cdef#.TYPE# into single character values.
========================================================
select ou.name, oc.name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', '?'),
o.name, c.condition, ru.name, rc.name,
decode(c.type#, 4,
decode(c.refact, 1, 'CASCADE', 'NO ACTION'), NULL),
decode(c.type#, 5, 'ENABLED',
decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),
decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
decode(bitand(c.defer,16),16, 'BAD', null),
decode(bitand(c.defer,32),32, 'RELY', null),
c.mtime
from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru,
sys.obj$ o, sys.cdef$ c
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.type# != 8 /* don't include hash expressions */
and c.rcon# = rc.con#(+)
and rc.owner# = ru.user#(+)
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
Let me guess...
'C' Check
'U' Unique
'P' Primary Key
'R' Referential/Foreign Key
'V' View w/ Check Option
'O' View w/ Read Only
What do you think ?
Looks like 1 and 7 decode to a 'C'
Are they both for Check constraints ?
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
'C' Check
'U' Unique
'P' Primary Key
'R' Referential/Foreign Key
'V' View w/ Check Option
'O' View w/ Read Only
What do you think ?
Looks like 1 and 7 decode to a 'C'
Are they both for Check constraints ?
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
Beyond 7....
cdef$.type# = 8 shows up as a major filter condition in the "ALL_CLUSTER_HASH_EXPRESSIONS" view (so 8 represents Hash Expressions)
Cannot find other references beyond 7 in any sys views
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
cdef$.type# = 8 shows up as a major filter condition in the "ALL_CLUSTER_HASH_EXPRESSIONS" view (so 8 represents Hash Expressions)
Cannot find other references beyond 7 in any sys views
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
- Status
Similar threads
- Locked
- Question
- Locked
- Question