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

"John Peterson" <j0hnp_at_comcast.net> wrote in message news:SM-dnf2DM_dAV-zWnZ2dnUVZ_t6dnZ2d_at_giganews.com...
>
> "joel garry" <joel-garry_at_home.com> wrote in message
> news:9b55d403-2ff0-47bc-a21f-3713f51bfd7c_at_e19g2000prn.googlegroups.com...
>> On Feb 8, 10:09 am, "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?
>>
>> http://forums.oracle.com/forums/thread.jspa?messageID=3575293
>>
>> Although I personally would have expected the
>> user_constraints.deferred column to reflect that. Is that a bug or
>> documentation insufficiency?
>>
>> Give some ddl/dml so we can all be sure to be on the same page.
>>
>> jg
>> --
>> _at_home.com is bogus.
>> http://ostatic.com/blog/oracle-cuts-affect-gnome-accessibility-work
>
> AHA! That's *exactly* what I'm looking for! Thank you!
>
> Can I create a view based off of this x$ view, that I can then grant to a
> "standard" user of the system? I'm guessing so...but I guess I'll play
> around. :-)

Well, nuts. Upon further review, this isn't *quite* what I'm looking for (it's close!).

As it turns out, this works when the ALTER SESSION syntax is used.

However, it *doesn't* work when the SET CONSTRAINTS syntax is used. <sigh>

Ideally I would be able to determine *both* aspects. I wonder if there's an x$ view that deals with transactions? When I review the list here:

http://yong321.freeshell.org/computer/x$table.html

I don't see anything that's immediately obvious.

Why does this have to be so hard!   Received on Tue Feb 09 2010 - 18:31:10 CST