BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_CONSTRAINTS view tips

Oracle Tips by Burleson Consulting

DBA_CONSTRAINTS

<< Return to the index

Oracle 11g makes the following comments about the DBA_CONSTRAINTS table: "Constraint definitions on all tables"

Related notes on DBA_CONSTRAINTS:

Column description of the DBA_CONSTRAINTS view:

OWNER

Description of DBA_CONSTRAINTS.OWNER: "Owner of the table"

CONSTRAINT_NAME

Description of DBA_CONSTRAINTS.CONSTRAINT_NAME: "Name associated with constraint definition"

CONSTRAINT_TYPE

Description of DBA_CONSTRAINTS.CONSTRAINT_TYPE: "Type of constraint definition"

TABLE_NAME

Description of DBA_CONSTRAINTS.TABLE_NAME: "Name associated with table with constraint definition"

SEARCH_CONDITION

Description of DBA_CONSTRAINTS.SEARCH_CONDITION: "Text of search condition for table check"

R_OWNER

Description of DBA_CONSTRAINTS.R_OWNER: "Owner of table used in referential constraint"

R_CONSTRAINT_NAME

Description of DBA_CONSTRAINTS.R_CONSTRAINT_NAME: "Name of unique constraint definition for referenced table"

DELETE_RULE

Description of DBA_CONSTRAINTS.DELETE_RULE: "The delete rule for a referential constraint"

STATUS

Description of DBA_CONSTRAINTS.STATUS: "Enforcement status of constraint - ENABLED or DISABLED"

DEFERRABLE

Description of DBA_CONSTRAINTS.DEFERRABLE: "Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE"

DEFERRED

Description of DBA_CONSTRAINTS.DEFERRED: "Is the constraint deferred by default - DEFERRED or IMMEDIATE"

VALIDATED

Description of DBA_CONSTRAINTS.VALIDATED: "Was this constraint system validated? - VALIDATED or NOT VALIDATED"

GENERATED

Description of DBA_CONSTRAINTS.GENERATED: "Was the constraint name system generated? - GENERATED NAME or USER NAME"

BAD

Description of DBA_CONSTRAINTS.BAD: "Creating this constraint should give ORA-02436. Rewrite it before 2000 AD."

RELY

Description of DBA_CONSTRAINTS.RELY: "If set, this flag will be used in optimizer"

LAST_CHANGE

Description of DBA_CONSTRAINTS.LAST_CHANGE: "The date when this column was last enabled or disabled"

INDEX_OWNER

Description of DBA_CONSTRAINTS.INDEX_OWNER: "The owner of the index used by this constraint"

INDEX_NAME

Description of DBA_CONSTRAINTS.INDEX_NAME: "The index used by this constraint"

INVALID

No comments exist for the INVALID column of the DBA_CONSTRAINTS table.

VIEW_RELATED

No comments exist for the VIEW_RELATED column of the DBA_CONSTRAINTS table.

DBA_CONSTRAINTS View Source

Oracle 11g's data dictionary defines the DBA_CONSTRAINTS view using the following source query:

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', 2, 'SET NULL', '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,
decode(c.type#, 2, ui.name, 3, ui.name, null),
decode(c.type#, 2, oi.name, 3, oi.name, null),
decode(bitand(c.defer, 256), 256,
decode(c.type#, 4,
case when (bitand(c.defer, 128) = 128
or o.status in (3, 5)
or ro.status in (3, 5)) then 'INVALID'
else null end,
case when (bitand(c.defer, 128) = 128
or o.status in (3, 5)) then 'INVALID'
else null end
),
null),
decode(bitand(c.defer, 256), 256, 'DEPEND ON VIEW', null)
from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
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.type# != 12 /* don't include log groups */
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books