How to determine the Oracle session's constraint state?
in [Oracle Help]
|
From: John Peterson on 8 Feb 2010 12:08 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
From: Mark D Powell on 8 Feb 2010 12:27 On Feb 8, 12:08 pm, "John Peterson" <j0...(a)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 --
From: John Peterson on 8 Feb 2010 13:09 "Mark D Powell" <Mark.Powell2(a)hp.com> wrote in message news:63f31d18-8229-43b2-9fbb-1c79cb5bdefc(a)l26g2000yqd.googlegroups.com... > On Feb 8, 12:08 pm, "John Peterson" <j0...(a)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?
From: ddf on 9 Feb 2010 08:51 On Feb 8, 1:09 pm, "John Peterson" <j0...(a)comcast.net> wrote: > "Mark D Powell" <Mark.Powe...(a)hp.com> wrote in messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc(a)l26g2000yqd.googlegroups.com... > > > > > > > On Feb 8, 12:08 pm, "John Peterson" <j0...(a)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.
From: Mark D Powell on 9 Feb 2010 10:11 On Feb 8, 1:09 pm, "John Peterson" <j0...(a)comcast.net> wrote: > "Mark D Powell" <Mark.Powe...(a)hp.com> wrote in messagenews:63f31d18-8229-43b2-9fbb-1c79cb5bdefc(a)l26g2000yqd.googlegroups.com... > > > > > > > On Feb 8, 12:08 pm, "John Peterson" <j0...(a)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 --
|
Next
|
Last
Pages: 1 2 3 4 Prev: Collections Next: Creating Tablespace in sql developer (Oracle 11g) |