Thinking Out Loud

June 15, 2011

Have you seen ? CONSTRAINT_TYPE ?

Filed under: 11g,oracle — mdinh @ 4:00 am

When I was testing DBMS_REDEFINITION to convert a non-partitioned table to a partitioned table, I came across constraint_type “?” from TOAD.

WTH! I have must have done something wrong.

It turns out that TOAD used the following SQL to determine constraint_type and does not label non-constraints:

SELECT CN.NAME constraint_name, c.type#, decode(c.type#, 1, 'Check', 2, 'Primary Key', 3, 'Unique Key',
       4, 'Referential Integrity', 5, 'Check Option on a View', 6, 'Read Only Option on a View', 7,'Check', '?') constraint_type
FROM   SYS.CDEF$ C, SYS.CON$ CN, SYS.OBJ$ O, SYS.USER$ U, SYS.CON$ RC, SYS.USER$ RU, SYS.OBJ$ RO
WHERE  C.CON# = CN.CON#
AND    C.OBJ# = O.OBJ#
AND    O.OWNER# = U.USER#
AND    C.RCON# = RC.CON#(+)
AND    RC.OWNER# = RU.USER#(+)
AND    C.ROBJ# = RO.OBJ#(+)
AND    U.NAME = 'SCOTT'
AND    O.NAME = 'SH'
AND    c.type# not in (8, 12)
order by 1;

Notice how TOAD purposely left out  cdef$.type# 8 & 12, but forgot about the others.

Querying dba_contraints shows different results:

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='SCOTT' and TABLE_NAME='SH';

no rows selected

SQL>

Here’s how I was able to find the definition for the various cdef$.type#

From ORCLE_HOME/rdbms/admin –

> grep 'cdef\$' *.sql
catmeta.sql:          oi.obj# = cdef$.enabled and
catmeta.sql:          bitand(cdef$.defer,8) = 8 and                  /* system generated */
catmeta.sql:          cdef$.type# = 3 and

This hinted me to look at catmeta.sql and found this

  type_num      number,                                  /* 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 */

Here is a little testing I did to confirm  cdef$.type# = 17 (All column supplemental logging)

SQL> SELECT CN.NAME constraint_name, c.type#, decode(c.type#, 1, 'Check', 2, 'Primary Key', 3, 'Unique Key',
  2         4, 'Referential Integrity', 5, 'Check Option on a View', 6, 'Read Only Option on a View', 7,'Check', '?') constraint_type
  3  FROM   SYS.CDEF$ C, SYS.CON$ CN, SYS.OBJ$ O, SYS.USER$ U, SYS.CON$ RC, SYS.USER$ RU, SYS.OBJ$ RO
  4  WHERE  C.CON# = CN.CON#
  5  AND    C.OBJ# = O.OBJ#
  6  AND    O.OWNER# = U.USER#
  7  AND    C.RCON# = RC.CON#(+)
  8  AND    RC.OWNER# = RU.USER#(+)
  9  AND    C.ROBJ# = RO.OBJ#(+)
 10  AND    U.NAME = 'SCOTT'
 11  AND    O.NAME = 'SH'
 12  AND    c.type# not in (8, 12)
 13  order by 1;

no rows selected

SQL> alter table scott.sh add supplemental log data (all) columns;

Table altered.

SQL> SELECT CN.NAME constraint_name, c.type#, decode(c.type#, 1, 'Check', 2, 'Primary Key', 3, 'Unique Key',
  2         4, 'Referential Integrity', 5, 'Check Option on a View', 6, 'Read Only Option on a View', 7,'Check', '?') constraint_type
  3  FROM   SYS.CDEF$ C, SYS.CON$ CN, SYS.OBJ$ O, SYS.USER$ U, SYS.CON$ RC, SYS.USER$ RU, SYS.OBJ$ RO
  4  WHERE  C.CON# = CN.CON#
  5  AND    C.OBJ# = O.OBJ#
  6  AND    O.OWNER# = U.USER#
  7  AND    C.RCON# = RC.CON#(+)
  8  AND    RC.OWNER# = RU.USER#(+)
  9  AND    C.ROBJ# = RO.OBJ#(+)
 10  AND    U.NAME = 'SCOTT'
 11  AND    O.NAME = 'SH'
 12  AND    c.type# not in (8, 12)
 13  order by 1;

CONSTRAINT_NAME                     TYPE# CONSTRAINT_TYPE
------------------------------ ---------- --------------------------------
SYS_C0010094                           17 ?

SQL>

Guess I can close the SR now.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: