Re: How to determine the Oracle session's constraint state?
From: ddf <oratune_at_msn.com>
Date: Tue, 9 Feb 2010 10:45:38 -0800 (PST)
Message-ID: <0fb42329-7d2b-4402-b25d-14d2c51a2096_at_x10g2000prk.googlegroups.com>
On Feb 9, 11:42 am, "John Peterson" <j0..._at_comcast.net> wrote:
> "John Peterson" <j0..._at_comcast.net> wrote in message
>
> news:v56dnae0mqu5EOzWnZ2dnUVZ_hadnZ2d_at_giganews.com...
>
>
>
>
>
>
>
> > "John Peterson" <j0..._at_comcast.net> wrote in message
> >news:vOOdnaKmLoUeGOzWnZ2dnUVZ_tGdnZ2d_at_giganews.com...
>
> >> "Mark D Powell" <Mark.Powe..._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/genera...
> >>> 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. ;-)- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 9 Feb 2010 10:45:38 -0800 (PST)
Message-ID: <0fb42329-7d2b-4402-b25d-14d2c51a2096_at_x10g2000prk.googlegroups.com>
On Feb 9, 11:42 am, "John Peterson" <j0..._at_comcast.net> wrote:
> "John Peterson" <j0..._at_comcast.net> wrote in message
>
> news:v56dnae0mqu5EOzWnZ2dnUVZ_hadnZ2d_at_giganews.com...
>
>
>
>
>
>
>
> > "John Peterson" <j0..._at_comcast.net> wrote in message
> >news:vOOdnaKmLoUeGOzWnZ2dnUVZ_tGdnZ2d_at_giganews.com...
>
> >> "Mark D Powell" <Mark.Powe..._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/genera...
> >>> 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. ;-)- Hide quoted text -
>
> - Show quoted text -
The X$ tables are available only to SYSDBA privileged accounts.
David Fitzjarrell Received on Tue Feb 09 2010 - 12:45:38 CST