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

On Feb 8, 12:08*pm, "John Peterson" <j0...@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 --

"Mark D Powell" <Mark.Powell2@hp.com> wrote in message
news:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
> On Feb 8, 12:08 pm, "John Peterson" <j0...@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?

On Feb 8, 1:09*pm, "John Peterson" <j0...@comcast.net> wrote:
> "Mark D Powell" <Mark.Powe...@hp.com> wrote in messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>
>
>
>
>
> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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 -

Deferrable constraints are either deferred or immediate and that is
set at the constraint level, which is independent of the session. One
does not execute

alter session set constraint ...

one submits

alter constraint ...

Why would you think such changes would be at the session level? The
constraint is already listed as DEFERRABLE/IMMEDIATE; setting it to
DEFERRED would not change that.


David Fitzjarrell.

On Feb 8, 1:09*pm, "John Peterson" <j0...@comcast.net> wrote:
> "Mark D Powell" <Mark.Powe...@hp.com> wrote in messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>
>
>
>
>
> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/B...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 --

"Mark D Powell" <Mark.Powell2@hp.com> wrote in message
news:1e6c1dd0-add9-41c3-a505-a0800c61c9f8@c4g2000yqa.googlegroups.com...
> On Feb 8, 1:09 pm, "John Peterson" <j0...@comcast.net> wrote:
>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>>
>>
>>
>>
>>
>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/B...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!

"ddf" <oratune@msn.com> wrote in message
news:cf165c3c-5766-45d0-8b41-d5a9f1cf13ce@b7g2000pro.googlegroups.com...
> On Feb 8, 1:09 pm, "John Peterson" <j0...@comcast.net> wrote:
>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>>
>>
>>
>>
>>
>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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 -
>
> Deferrable constraints are either deferred or immediate and that is
> set at the constraint level, which is independent of the session. One
> does not execute
>
> alter session set constraint ...
>
> one submits
>
> alter constraint ...
>
> Why would you think such changes would be at the session level? The
> constraint is already listed as DEFERRABLE/IMMEDIATE; setting it to
> DEFERRED would not change that.
>
>
> David Fitzjarrell.

Hi David! Please see the other replies in this thread. I'm trying to
figure out how to programmatically determine the current constraint "state"
(deferred, immediate, or default) when one of the following command examples
are issued:

SET CONSTRAINTS ALL DEFERRED;

or

ALTER SESSION SET CONSTRAINTS=DEFERRED;

I had originally thought that maybe the SYS_CONTEXT function could be
used -- but I don't see any applicable options. There *must* be some
metadata that Oracle saves to manage this, but I'm not sure whether that's
exposed to the user (I *hope* that it is).

Trying to find this via Google or other web searches has been like finding a
needle in a haystack. ;-)

"John Peterson" <j0hnp@comcast.net> wrote in message
news:vOOdnaKmLoUeGOzWnZ2dnUVZ_tGdnZ2d@giganews.com ...
>
> "Mark D Powell" <Mark.Powell2@hp.com> wrote in message
> news:1e6c1dd0-add9-41c3-a505-a0800c61c9f8@c4g2000yqa.googlegroups.com...
>> On Feb 8, 1:09 pm, "John Peterson" <j0...@comcast.net> wrote:
>>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
>>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>>>
>>>
>>>
>>>
>>>
>>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/B...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!

"John Peterson" <j0hnp@comcast.net> wrote in message
news:v56dnae0mqu5EOzWnZ2dnUVZ_hadnZ2d@giganews.com ...
>
> "John Peterson" <j0hnp@comcast.net> wrote in message
> news:vOOdnaKmLoUeGOzWnZ2dnUVZ_tGdnZ2d@giganews.com ...
>>
>> "Mark D Powell" <Mark.Powell2@hp.com> wrote in message
>> news:1e6c1dd0-add9-41c3-a505-a0800c61c9f8@c4g2000yqa.googlegroups.com...
>>> On Feb 8, 1:09 pm, "John Peterson" <j0...@comcast.net> wrote:
>>>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
>>>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/B...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...arameters.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. ;-)

On Feb 9, 11:42*am, "John Peterson" <j0...@comcast.net> wrote:
> "John Peterson" <j0...@comcast.net> wrote in message
>
> news:v56dnae0mqu5EOzWnZ2dnUVZ_hadnZ2d@giganews.com ...
>
>
>
>
>
>
>
> > "John Peterson" <j0...@comcast.net> wrote in message
> >news:vOOdnaKmLoUeGOzWnZ2dnUVZ_tGdnZ2d@giganews.co m...
>
> >> "Mark D Powell" <Mark.Powe...@hp.com> wrote in message
> >>news:1e6c1dd0-add9-41c3-a505-a0800c61c9f8@c4g2000yqa.googlegroups.com....
> >>> On Feb 8, 1:09 pm, "John Peterson" <j0...@comcast.net> wrote:
> >>>> "Mark D Powell" <Mark.Powe...@hp.com> wrote in
> >>>> messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc@l26g2000yqd.googlegroups.com...
>
> >>>> > On Feb 8, 12:08 pm, "John Peterson" <j0...@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/B.../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 ifI
> >>> 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...arameters.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