Re: How to determine the Oracle session's constraint state?

From: John Peterson <>
Date: Mon, 8 Feb 2010 11:09:34 -0700
Message-ID: <>

"Mark D Powell" <> wrote in message
> On Feb 8, 12:08 pm, "John Peterson" <> wrote:
>> Hello!
>> First time poster in this forum -- please forgive me if this is the wrong
>> place for my question. I've exhausted a web search on this issue, and
>> was
>> hoping that this might be a more targeted approach.
>> I am trying to programmatically determine the current session's
>> constraint
>> state (immediate, deferred, or default).
>> I have a procedure that I'd like to implement which would essentially
>> temporarily set the constraints "deferred" (e.g. SET CONSTRAINTS ALL
>> DEFERRED), perform some work, and then restore the constraint setting to
>> the
>> original state (e.g., immediate). However, I'm having a difficult time
>> identifying how to determine the current state of the constraints.
>> Any help would be very much appreciated!
>> Kind regards,
>> John Peterson
> See the DEFERRABLE and DEFERRED columns in the ALL_, USER_, or
> DBA_CONSTRAINTS views (documented in the Oracle version# Reference
> manual).
> By the way making a PK or UK deferrable would require use of a non-
> unique index to support the constraint instead of the standard unique
> index.
> HTH -- Mark D Powell --

Thanks, Mark!

Unfortunately, those metadata views don't seem to reflect the current session state.

That is, if I have some FKs that are deferrable (but initially immediate) (e.g., CONSTRAINT_TYPE = 'R'), it will show DEFERRABLE/IMMEDIATE in the DEFERRABLE/DEFERRED columns.

But, after I run:

SET CONSTRAINTS ALL DEFERRED Those columns are still DEFERRABLE/IMMEDIATE in the metadata views.

I had thought maybe I could obtain this information from the SYS_CONTEXT function to get the current session state information, but none of the options seem applicable.

Any other ideas?   Received on Mon Feb 08 2010 - 12:09:34 CST

Original text of this message