Re: How to determine the Oracle session's constraint state?
From: joel garry <joel-garry_at_home.com>
Date: Tue, 9 Feb 2010 17:39:12 -0800 (PST)
Message-ID: <6e0889cf-c6bf-408c-93e1-00b266dbb7e9_at_k36g2000prb.googlegroups.com>
On Feb 9, 4:31 pm, "John Peterson" <j0..._at_comcast.net> wrote:
> "John Peterson" <j0..._at_comcast.net> wrote in message
>
> news:SM-dnf2DM_dAV-zWnZ2dnUVZ_t6dnZ2d_at_giganews.com...
>
>
>
>
>
> > "joel garry" <joel-ga..._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>
Date: Tue, 9 Feb 2010 17:39:12 -0800 (PST)
Message-ID: <6e0889cf-c6bf-408c-93e1-00b266dbb7e9_at_k36g2000prb.googlegroups.com>
On Feb 9, 4:31 pm, "John Peterson" <j0..._at_comcast.net> wrote:
> "John Peterson" <j0..._at_comcast.net> wrote in message
>
> news:SM-dnf2DM_dAV-zWnZ2dnUVZ_t6dnZ2d_at_giganews.com...
>
>
>
>
>
> > "joel garry" <joel-ga..._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>
Well, that's probably because the difference isn't trivial, the session can go over many transactions, but the set constraints is just a transaction.
>
> 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!
Presumably, your code knows when it is in a transaction, rather than having to figure it out. I don't deal with this, because the language I use has a built-in to do it - so I know it is possible, but no clue how, and it quite possibly is just tracking it with its own variables. But I have to deal with loops unwinding levels of pseudotransactions until a rollback will actually work, so it's always something.
jg
-- _at_home.com is bogus. http://latimesblogs.latimes.com/.a/6a00d8341c630a53ef01287762a80b970c-800wiReceived on Tue Feb 09 2010 - 19:39:12 CST