DBA_CONSTRAINTS: Constraint_type is ‘?’

At first, it looks funny when see constraint_type show ‘?’ when query dba_constraints. One thing clear is that table contained in streams for me. Quickly 1 article explained why ‘supplemental logging’ play this way:
http://www.orafaq.com/usenet/comp.databases.oracle.server/2007/01/04/0206.htm

My summary:
1) "catalog.sql" created view "DBA_CONSTRAINTS" with base table "SYS.$_def", then DECODE statement on column "TYPE#" is used to determine the constraint type. But it cannot handle any integers out of from 1 to 7, hehe, that is why it show us ‘?’.

create or replace view DBA_CONSTRAINTS
    (OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE,
     TABLE_NAME, SEARCH_CONDITION, R_OWNER,
     R_CONSTRAINT_NAME, DELETE_RULE, STATUS,
     DEFERRABLE, DEFERRED, VALIDATED, GENERATED,
     BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME,
     INVALID, VIEW_RELATED)
as
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,

from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru, sys.obj$ ro,
     sys.obj$ o, sys.cdef$ c, sys.obj$ oi, sys.user$ ui

2) check out what your database support on constraint_type

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning and Data Mining options

SQL> select distinct type# from sys.cdef$ order by 1;

     TYPE#
———-
         1
         2
         3
         4
         5
         6
         7
        11
        12
        14
        15

     TYPE#
———-
        16

12 rows selected.

3) "sql.bsq" script can show how table SYS.cdef$ created.

create table cdef$                        /* constraint definition table */
( con#          number not null,          /* constraint number */
   obj#          number not null,        /* object number of base table/view */
   cols          number,                 /* number of columns in constraint */
   type#         number not null,                  /* constraint type: */
                      /* 1 = table check, 2 = primary key, 3 = unique, */
                      /* 4 = referential, 5 = view with CHECK OPTION, */
                      /* 6 = view READ ONLY check */
                      /* 7 – table check constraint associated with column NOT NULL */
                      /* 8 – hash expressions for hash clusters */
                      /* 9 – Scoped REF column constraint */
                      /* 10 – REF column WITH ROWID constraint */
                      /* 11 – REF/ADT column with NOT NULL const */
                      /* 12 – Log Groups for supplemental logging */
                      /* 14 – Primary key supplemental logging */
                      /* 15 – Unique key supplemental logging */
                      /* 16 – Foreign key supplemental logging */
                      /* 17 – All column supplemental logging */


We can directly query those underline table to know much clear, of course.

Advertisements

About Pang

Oracle DBA working in database, fusion middleware and identity management and cloud.
This entry was posted in Oracle database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s