Re: How to determine the Oracle session's constraint state?
From: John Peterson <j0hnp_at_comcast.net>
Date: Tue, 9 Feb 2010 08:53:37 -0700
Message-ID: <vOOdnaKmLoUeGOzWnZ2dnUVZ_tGdnZ2d_at_giganews.com>
>> "Mark D Powell" <Mark.Powe..._at_hp.com> wrote in
>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc_at_l26g2000yqd.googlegroups.com...
>>
>>
>>
>>
>>
>> > On Feb 8, 12:08 pm, "John Peterson" <j0..._at_comcast.net> 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?- Hide quoted text -
>>
>> - Show quoted text -
Date: Tue, 9 Feb 2010 08:53:37 -0700
Message-ID: <vOOdnaKmLoUeGOzWnZ2dnUVZ_tGdnZ2d_at_giganews.com>
"Mark D Powell" <Mark.Powell2_at_hp.com> wrote in message news:1e6c1dd0-add9-41c3-a505-a0800c61c9f8_at_c4g2000yqa.googlegroups.com...
> On Feb 8, 1:09 pm, "John Peterson" <j0..._at_comcast.net> wrote:
>> "Mark D Powell" <Mark.Powe..._at_hp.com> wrote in
>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc_at_l26g2000yqd.googlegroups.com...
>>
>>
>>
>>
>>
>> > On Feb 8, 12:08 pm, "John Peterson" <j0..._at_comcast.net> 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?- Hide quoted text -
>>
>> - Show quoted text -
> > I understand the question now. When you follow the directions in the > manual to set contrainst processing deferred in your session as per > http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/general.htm#sthref1939 > how do you check to see the current status of constraint processing. > Note that the command is valid only for a single transaction so it > automatically resets on commit or rollback. > > I do not know how to check while you have an active transaction in > progress, but I can remember dealing with a similar question where you > set a session parameter and where to see it since it is not a database > parameter and does not show in v$parameter. I will look and see if I > can find a note on whatever parameter I reseached before, if it had an > answer, and if that answer applies. > > HTH -- Mark D Powell -- >
Thanks Mark!
One thing that I found from the AskTom site (but haven't verified) that there's a subtle distinction between:
SET CONSTRAINTS ALL DEFERRED; and
ALTER SESSION SET CONSTRAINTS=DEFERRED; Apparently the first will operate at the transaction scope, and the second at the session scope. I had always thought the two were interchangeable.
But, yes -- you understand my question now: how can we programmatically determine the current "state" of this setting?
Thanks again! Received on Tue Feb 09 2010 - 09:53:37 CST