Re: constraint_type=?

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 4 Jan 2007 20:01:10 GMT
Message-ID: <JBD0y4.B6y@igsrsparc2.er.usgs.gov>

ajt wrote:
> Has anyone seen this before, constraint_type "?" and what does it mean?
> It appears to be defined on two sys owned tables
>
> 13:00:40 >select unique(constraint_type) from dba_constraints
> 13:03:42 2 /
>
> C
> -
> V
> R
> U
> P
> ?
> C
> O
>
> SQL> select owner,
> constraint_name,
> table_name,
> constraint_type,
> search_condition, status
> from dba_constraints
> where constraint_type = '?'
>
> OWNER CONSTRAINT_NAME TABLE_NAME C SEARCH_CONDITION
> STATUS
> ------ ----------------- ---------------------- -
> ------------------------- --------
> SYS SYS_C001228 WRI$_ADV_DEFINITIONS ? "TYPE" IS NOT NULL
> ENABLED
>
>
> It appears that there is a user defined type in the table
> SYS.WRI$_ADV_ABSTRACT_T. The type exists and is valid.
>
>
> 14:23:44 >desc sys.WRI$_ADV_DEFINITIONS
> Name
> Null? Type
>
> ------------------------------------------------------------------------
> -------- -------------------------------------------------
> ID
> NOT NULL NUMBER
> NAME
> NOT NULL VARCHAR2(30)
> PROPERTY
> NOT NULL NUMBER
> TYPE
> NOT NULL SYS.WRI$_ADV_ABSTRACT_T
>
> 14:24:43 >select * from dba_objects where object_name =
> 'WRI$_ADV_ABSTRACT_T';
>
> OWNER OBJECT_NAME SUBOBJECT_NAME
> OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
> ------------ -------------------------------
> ------------------------------ ---------- --------------
> ------------------- ---------
> LAST_DDL_ TIMESTAMP STATUS T G S
> --------- -------------------- ------- - - -
> SYS WRI$_ADV_ABSTRACT_T
> 3687 TYPE 14-NOV-06
> 14-NOV-06 2006-11-14:15:26:40 VALID N N N
>
> SYS WRI$_ADV_ABSTRACT_T
> 8919 TYPE BODY 14-NOV-06
>

The catalog.sql script can help shed some light on this. If you look in this script, you can see the CREATE VIEW command which creates the DBA_CONSTRAINTS view. A DECODE statement is used to determine the contraint type. The SYS.CDEF$ table contains a column called TYPE#. The DECODE statement in the CREATE VIEW shows us that if the TYPE# = 1, the constraint type is 'C'. For TYPE#=2, the constraint type is 'P'. As a catchall, if the TYPE# is not in the list of integers from 1 to 7, the constraint type is unknown (to the view) and is shown as '?'. There could be multiple unknown values. When I queried one of my 10.2.0.2 databases, I get the following TYPE#'s:

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

      TYPE#

---

          1
          2
          3
          4
          5
          6
          7
         11

Notice constraint type 11. So what is constraint type 11? And does this match what you are seeing in your database?

We do know that the DBA_CONSTRAINTS view uses SYS.CDEF$ as one of its base tables and this base table is where the constraint type number is obtained. So let's look at the script which creates this table, the ?/rdbms/admin/sql.bsq script. In that script, I see the following for the beginning of the CREATE TABLE command:

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 */

*.....snipped....*

The rest is snipped for brevity. Notice that after TYPE# = 7, there are other constraint types as well, each with a short description. If you query down to SYS.CDEF$, you can get the exact constraint TYPE# because the '?' can mean one of many constraint types.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Thu Jan 04 2007 - 14:01:10 CST