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

"John Peterson" <j0hnp_at_comcast.net> wrote in message news:v56dnae0mqu5EOzWnZ2dnUVZ_hadnZ2d_at_giganews.com...
>
> "John Peterson" <j0hnp_at_comcast.net> wrote in message
> news: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!
>
> Upon further review, I see that both settings *are* the same -- they
> affect the transaction scope (the session-based one just acts as if you
> submitted the statement before every transaction).
>
> But figuring out how to ascertain the current state of this setting is
> proving to be challenging!

A long time ago, I seem to have come across a post that suggested that maybe the information I seek could be found in the "hidden" parameter views:

http://www.adp-gmbh.ch/ora/misc/view_hidden_parameters.html

However, when I try to access the x$ksppi and x$ksppsv views, I get a "table or view does not exist" error (even using credentials with administrative privileges).

It seemed to me, that there was a integer value, where a couple of "bits" would change, depending on the current state.

Dunno if that could be of use to me, but I thought I'd throw that out there in case it rang any bells. ;-)   Received on Tue Feb 09 2010 - 10:42:40 CST