Re: recreate constraints script - URGENT
TOAD has a 'Generate Schema script' function and if I recall correctly you can specify the objects that you want the script to include. You can download an evaluation copy at http://www.quest.com/solutions/download.asp On Tue, 2003-12-30 at 19:24, system manager wrote: Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Austin Hackett INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
recreate constraints script - URGENT
Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: recreate constraints script - URGENT
run the same script for every table for the schema owner and spool everything to the same file ... there you have it. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: recreate constraints script - URGENT
dba_constraint will inform you of the tables that have constraints and what type of constraint they are. Further digging into the dba_ tables will provide the information you desire. Keep the scripts as part of the database documentation and update when needed. Third party software can provide the scripts for the entire database. Ron [EMAIL PROTECTED] 12/30/2003 2:24:25 PM Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:Re: recreate constraints script - URGENT
Thanks Ron, I got this recreate constraints script from our list but lost it.It was really good script and it can re-generate all the constraints under a schema owner. -- Original Message Date: Tue, 30 Dec 2003 12:14:26 -0800 dba_constraint will inform you of the tables that have constraints and what type of constraint they are. Further digging into the dba_ tables will provide the information you desire. Keep the scripts as part of the database documentation and update when needed. Third party software can provide the scripts for the entire database. Ron [EMAIL PROTECTED] 12/30/2003 2:24:25 PM Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: recreate constraints script - URGENT
Thanks Rajendra, Good idea but I have 1200 tables :(I got a good script from our list long time ago but lost it.That script can capiture constraints for the schema owner. -- Original Message Date: Tue, 30 Dec 2003 11:39:26 -0800 run the same script for every table for the schema owner and spool everything to the same file ... there you have it. Raj --- - Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: recreate constraints script - URGENT
If you have one to generate the constraints for a table, just modify it slightly to include a whole schema. Just checked, I don't have such a beastie. Check orafaq.com, likely there is one there. Jared system manager [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 11:24 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:recreate constraints script - URGENT Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: recreate constraints script - URGENT
For 9i DBs, DBMS_METADATA will (re)create DDL for every (at least most) object in the DB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re:Re: recreate constraints script - URGENT
There are probably such scripts in internet, or you can write your own in few minutes, but if you need it only once, then one easy way would be just to use TOAD (free/shareware ver) or similar tools to extract scripts of your constraints from database. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 11:09 PM Thanks Ron, I got this recreate constraints script from our list but lost it.It was really good script and it can re-generate all the constraints under a schema owner. -- Original Message Date: Tue, 30 Dec 2003 12:14:26 -0800 dba_constraint will inform you of the tables that have constraints and what type of constraint they are. Further digging into the dba_ tables will provide the information you desire. Keep the scripts as part of the database documentation and update when needed. Third party software can provide the scripts for the entire database. Ron [EMAIL PROTECTED] 12/30/2003 2:24:25 PM Dear all, I have a script to generate constraints for a single table but I need a script to generate constraints for a schema owner . Can anyone send me a copy? Many thanks, _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: recreate constraints script - URGENT
Yep, that's a good call, although there was some bug with paritioned tables I think... Also, metalink note 1016836.6 has a script for capturing constraints. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 11:49 PM For 9i DBs, DBMS_METADATA will (re)create DDL for every (at least most) object in the DB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Questions about constraints
Hallo, I get this errormessage while inserting to this table. How can I check this constraint problem? Any wrong with the table definition 9.03.2003 09:46:38 Db_Transfil.AVSTEM - ORA-2: db_filtype_ik.AVS TEM: - ORA-2: db_filtype_ik:OVERFOR_TIL_KUN: - ORA-2: SKRIV_kuponghod_TMP1: - ORA-1: unique constraint (A172421.KUPONGHOD_TMP1_PK) vio lated CREATE TABLE KUPONGHOD_TMP1 ( KUNHOD_TMP1_ID VARCHAR2 (15) NOT NULL, TRANSFIL_ID NUMBER (10) NOT NULL, AVDNR NUMBER (6)NOT NULL, KUNDEID NUMBER (16), KASSERERID NUMBER (6)NOT NULL, KASSEID NUMBER (10) NOT NULL, BONGID NUMBER (11) NOT NULL, DATODATE NOT NULL, CONSTRAINT KUPONGHOD_TMP1_PK PRIMARY KEY ( KUNHOD_TMP1_ID ) USING INDEX TABLESPACE INNTEMPIX PCTFREE 10 STORAGE ( INITIAL 1024K NEXT 1024K PCTINCREASE 0 )) TABLESPACE INNTEMP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 505 FREELISTS 1 FREELIST GROUPS 1 ) Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Questions about constraints
You are trying to insert a row that contains values that already exist in the table, and there is a constraint on the table that says the values for the row(s) must be unique. Now about that column BONGID ... What are you smoking? -Original Message- Hallo, I get this errormessage while inserting to this table. How can I check this constraint problem? Any wrong with the table definition 9.03.2003 09:46:38 Db_Transfil.AVSTEM - ORA-2: db_filtype_ik.AVS TEM: - ORA-2: db_filtype_ik:OVERFOR_TIL_KUN: - ORA-2: SKRIV_kuponghod_TMP1: - ORA-1: unique constraint (A172421.KUPONGHOD_TMP1_PK) vio lated CREATE TABLE KUPONGHOD_TMP1 ( KUNHOD_TMP1_ID VARCHAR2 (15) NOT NULL, TRANSFIL_ID NUMBER (10) NOT NULL, AVDNR NUMBER (6)NOT NULL, KUNDEID NUMBER (16), KASSERERID NUMBER (6)NOT NULL, KASSEID NUMBER (10) NOT NULL, BONGID NUMBER (11) NOT NULL, DATODATE NOT NULL, CONSTRAINT KUPONGHOD_TMP1_PK PRIMARY KEY ( KUNHOD_TMP1_ID ) USING INDEX TABLESPACE INNTEMPIX PCTFREE 10 STORAGE ( INITIAL 1024K NEXT 1024K PCTINCREASE 0 )) TABLESPACE INNTEMP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 505 FREELISTS 1 FREELIST GROUPS 1 ) Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Questions about constraints
This message is telling you that your insert violates the primary key constraint for the table. This means that the primary key, KUNHOD_TMP1_ID, value in your inserted record already exists in the table. HTH, Peter Schauss -Original Message- Sent: Wednesday, March 19, 2003 4:16 AM To: Multiple recipients of list ORACLE-L Hallo, I get this errormessage while inserting to this table. How can I check this constraint problem? Any wrong with the table definition 9.03.2003 09:46:38 Db_Transfil.AVSTEM - ORA-2: db_filtype_ik.AVS TEM: - ORA-2: db_filtype_ik:OVERFOR_TIL_KUN: - ORA-2: SKRIV_kuponghod_TMP1: - ORA-1: unique constraint (A172421.KUPONGHOD_TMP1_PK) vio lated CREATE TABLE KUPONGHOD_TMP1 ( KUNHOD_TMP1_ID VARCHAR2 (15) NOT NULL, TRANSFIL_ID NUMBER (10) NOT NULL, AVDNR NUMBER (6)NOT NULL, KUNDEID NUMBER (16), KASSERERID NUMBER (6)NOT NULL, KASSEID NUMBER (10) NOT NULL, BONGID NUMBER (11) NOT NULL, DATODATE NOT NULL, CONSTRAINT KUPONGHOD_TMP1_PK PRIMARY KEY ( KUNHOD_TMP1_ID ) USING INDEX TABLESPACE INNTEMPIX PCTFREE 10 STORAGE ( INITIAL 1024K NEXT 1024K PCTINCREASE 0 )) TABLESPACE INNTEMP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 505 FREELISTS 1 FREELIST GROUPS 1 ) Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
constraints and optimizer
Hello, Env: Oracle 9.2.0.2.0 on Solaris 9 I suspect this is a foolish question, but I will ask anyway: (It's Friday; my brain stops working after Wednesday) How much does the presence of constraints influence the optimizer, if the indexes are present? We are developing a method for transporting a large volume of data between a staging instance and a query instance of Oracle, using transportable tablespaces (tts). When the tts export uses CONSTRAINTS=Y, the subsequent tts import takes about 4 hours; when the tts export excludes constraints, it takes about 1 hour. We prefer the 1 hour. I know we can do the constraints in a separate step and create them as ENABLE NOVALIDATE to save time, but the question was posed: Do we need them at all in an instance that will receive no updates, only queries, if the indexes exist? My first thought was, yes, the optimizer uses them, but I'm not sure how much value they add if the indexes exist. Is the optimizer MUCH more likely to make an intelligent choice if the constraints are present, or is the presence of an index the major deciding factor? We do intend to do some testing with in-house queries, but I thought I would pass this along, hoping some kind soul(s) will provide the benefit of their experience. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: constraints and optimizer
All recent versions of Oracle have used unique, pk and not-null constraints to help optimise queries - but I don't suppose you were thinking of those in particular. In general Oracle did not make use of check constraints except for partition views, but Oracle 9 can now make very good use of check constraints to help optimise queries. There is an example on www.dbazine.com by Vadim Tropashko demonstrating that Oracle can combine the constraint col_X = upper(col_X) with the predicate upper(col_X) = 'abc' to infer the predicate col_X = 'abc' and so be able to use an index on col_X . Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 14 March 2003 20:19 Hello, Env: Oracle 9.2.0.2.0 on Solaris 9 I suspect this is a foolish question, but I will ask anyway: (It's Friday; my brain stops working after Wednesday) How much does the presence of constraints influence the optimizer, if the indexes are present? We are developing a method for transporting a large volume of data between a staging instance and a query instance of Oracle, using transportable tablespaces (tts). When the tts export uses CONSTRAINTS=Y, the subsequent tts import takes about 4 hours; when the tts export excludes constraints, it takes about 1 hour. We prefer the 1 hour. I know we can do the constraints in a separate step and create them as ENABLE NOVALIDATE to save time, but the question was posed: Do we need them at all in an instance that will receive no updates, only queries, if the indexes exist? My first thought was, yes, the optimizer uses them, but I'm not sure how much value they add if the indexes exist. Is the optimizer MUCH more likely to make an intelligent choice if the constraints are present, or is the presence of an index the major deciding factor? We do intend to do some testing with in-house queries, but I thought I would pass this along, hoping some kind soul(s) will provide the benefit of their experience. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Disable / enable constraints
I need to truncate and import data into several schemas. The tables have lots of constraints. I can produce a script to disable and enable the contriants but I would like to know more about the constraint_type field in dba_constraints and what are all the SYS_ contraints? Should I disable all constraints for a schema before the import or only certain types? Thanks! Ron If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Disable / enable constraints
SYS_ contraints are the ones that when you created them you did not give a name. Oracle will automatically assign a name like 'SYS_1234567'. You can drop tables in the schema before import. Import will automatically create tables if it does not exist. HTH. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Smith, Ron L. Sent: Tuesday, March 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Subject: Disable / enable constraints I need to truncate and import data into several schemas. The tables have lots of constraints. I can produce a script to disable and enable the contriants but I would like to know more about the constraint_type field in dba_constraints and what are all the SYS_ contraints? Should I disable all constraints for a schema before the import or only certain types? Thanks! Ron If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Disable / enable constraints
Ron, SYS_ constraints are NOT NULL constraints. They are also those constraints placed on tables by you that you have not named. This is why we should always name our constraints! The Constraint_Type field is decoded as follows: C = Check Constraint P = Primary Key R = Relational (Foreign Key) constraint U = Unique Constraint There are probably others documented in the Oracle docs someplace. Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I need to truncate and import data into several schemas. The tables have lots of constraints. I can produce a script to disable and enable the contriants but I would like to know more about the constraint_type field in dba_constraints and what are all the SYS_ contraints? Should I disable all constraints for a schema before the import or only certain types? Thanks! Ron If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Primary Key Constraints
Hi Mike,
I presume you want to disregard System tables, so here goes
select owner, table_name from dba_tables
where owner not in ('SYS','SYSTEM','OUTLN','DBSNMP')
minus
select owner, table_name from dba_constraints where constraint_type = 'P';
-Original Message-
Sent: Tuesday, November 26, 2002 7:25 PM
To: Multiple recipients of list ORACLE-L
Is there an easy query to get a list of tables that don't have any primary
key?
I've tried a couple of different ones, but none of them work quite right.
Seems like this should be easy.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Sardin
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kieran Murray
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Primary Key Constraints
Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Primary Key Constraints
Mike, How about: select table_name from user_tables a where not exists(select 1 from user_constraints b where a.table_name = b.table_name and b.constraint_type = 'P'); Constraint_type values are: P = Primary Key C = Check Constraint R = Referential Constraint (Foreign Key) U = Unique Constraint There might be others. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 26, 2002 2:25 PM To: Multiple recipients of list ORACLE-L Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Primary Key Constraints
select owner, table_name from dba_tables where (owner, table_name) not in (select owner, table_name from dba_constraints where constraint_type = 'P'); Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 2:25 PM Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Primary Key Constraints
SQL select table_name 2 from dba_tables 3 minus 4 select table_name 5 from dba_constraints 6 where constraint_type = 'R'; This should work (does in my 8.1.7 test db). What methods have you tried? and what was not working? -Original Message- Sent: Tuesday, November 26, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Primary Key Constraints
select owner, table_name from dba_tables where not exists (select 'a' from dba_constraints where constraint_type = 'P' and owner = dba_tables.owner and table_name = dba_tables.table_name) order by owner, table_name; Mike Sardina wrote: Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Primary Key Constraints
Title: RE: Primary Key Constraints SELECT owner, table_name FROM DBA_TABLES t WHERE NOT EXISTS (SELECT 'X' FROM DBA_CONSTRAINTS c WHERE c.owner = t.owner AND c.table_name = t.table_name AND c.constraint_type = 'P') ORDER BY 1,2 Take off the ORDER BY to speed things up. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mike Sardina [SMTP:[EMAIL PROTECTED]] Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy.
RE: Primary Key Constraints
select owner, table_name from all_tables A where not exists (select owner, table_name from all_constraints B where b.owner=A.owner and b.table_name=A.table_name and b.constraint_type='P') That ought to do it. Cheers : Ferenc Mantfeld -Original Message- From: Mike Sardina [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, November 27, 2002 6:25 AM To: Multiple recipients of list ORACLE-L Subject:Primary Key Constraints Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mantfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Primary Key Constraints
how about select table_name from user_tables where table_name not in (select table_name from user_constraints where constraint_type = 'P'); [EMAIL PROTECTED] 11/26/02 01:25PM Is there an easy query to get a list of tables that don't have any primarykey? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Mike Sardin INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Primary Key Constraints
try this: select table_name from user_tables minus select table_name from user_constraints where constraint_type = 'P'; -Original Message- Sent: Tuesday, November 26, 2002 2:25 PM To: Multiple recipients of list ORACLE-L Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Primary Key Constraints
it's sure easy enough SELECT (OWNER, TABLE_NAME) FROM DBA_TABLES WHERE (OWNER, TABLE_NAME) NOT IN (SELECT OWNER, TABLE_NAME FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P') HTH Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 2:25 PM Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Primary Key Constraints
Another way : select t.owner, t.table_name from dba_tables t minus select c.owner, c.table_name from dba_constraints c where c.constraint_type='P' ; --- Mercadante, Thomas F [EMAIL PROTECTED] a écrit : Mike, How about: select table_name from user_tables a where not exists(select 1 from user_constraints b where a.table_name = b.table_name and b.constraint_type = 'P'); Constraint_type values are: P = Primary Key C = Check Constraint R = Referential Constraint (Foreign Key) U = Unique Constraint There might be others. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 26, 2002 2:25 PM To: Multiple recipients of list ORACLE-L Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Primary Key Constraints
select table_name from user_tables minus select table_name from user_constraints where constraint_type='P'; HTH, Krishna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 11:25 AM Is there an easy query to get a list of tables that don't have any primary key? I've tried a couple of different ones, but none of them work quite right. Seems like this should be easy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Sardin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Krishna Rao Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints problem
Title: RE: Constraints problem You can simply export, then import the tables...making sure you use fromuser/touser,ignore=y,rows=n Since the table is already there and rows=n, the table will not be rebuilt and the constraints will be added, but you have to make sure that ignore=y or the import will fail stating the object already exists. -Original Message- From: Karthikeyan S [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 24, 2002 7:13 AM To: Multiple recipients of list ORACLE-L Subject: Constraints problem Hi, I had to copy a set of tables with data from one schema into another. I did this by using create table table_name as select * from master_schema.table_name; But the constraints ( primary key, foreign key etc) are not created in the test schema. Is there any way to implement all the constraints (in the master_schema) into the test schema? Thanks in advance. regards, Karthik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karthikeyan S INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints problem
Hi I had to copy a set of tables with data from one schema into another. I did this by using create table table_name as select * from master_schema.table_name; But the constraints ( primary key, foreign key etc) are not created in the test schema. Is there any way to implement all the constraints (in the master_schema) into the test schema? Grab the freeware DBATool. It can generate schema DDL scripts and can easily generate the recreation scripts for all dependent objects on a table (indexes, fk constraints, synonyms etc). The DBATool is specifically designed to create test schemas and allows you to manipulate the schema DDL in useful ways (fold many tablespaces into a smaller number, strip off or modify extent and storage clauses etc). DBATool: http://www.DataBee.com Regards Dale Edgar Net 2000 Ltd. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Constraints
Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
Likely that your data in the table violates the constraint. Export the table, truncate table, add constraint and try importing to it -watch for violation of constraint during import. Ciao 'n best of luck. --- CSW -Original Message- Sent: Wednesday, April 24, 2002 3:09 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
you must ensure uniquness and not null of each value in cells of column BUTIKS_NR -Original Message- [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 5:39 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints
hi roland ... it seems your table already had a data redundant data ...for a BUTIKS_NR column and when you try to add a new constraints it produced an error message try to remove redundant data ... or empty your table first... then add a constraint ... regards kang bedjo I'd rather see the world from another angle *- Jewel- * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: B3D70 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
it should not contain any null values too other than being unique. Kranti -Original Message- Sent: Wednesday, April 24, 2002 5:39 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kranti pushkarna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints
what can I do to solve this? RTFM about primary keys. They need to be unique and not null. [EMAIL PROTECTED] wrote: Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints
Hi Yes that would be enough however there may be null values and they are not allowed in a primary key (try unique key instead) There is also an exception clause I believe that will put the exceptions in a table for you to fix them (I don't know if this valid for creation though) jack Roland.Skoldblom@ ica.se To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Constraints 24-04-2002 14:08 Please respond to ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability
RE: Constraints
Roland have you tried a select butiks_nr, count(*) from pbk.k1 group by butiks_nr having count(*) 1 to check there really are no duplicates -Original Message- Sent: Wednesday, April 24, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Hallo, I am trying to run this script, ALTER TABLE PBK.K1 ADD CONSTRAINT idx_rollson PRIMARY KEY (BUTIKS_NR) but gets the erormessage ORA-02437: cannot validate (PBK.IDX_ROLLSON) - primary key violated what can I do to solve this? Please help me. Wouldnt it be enough to have unique values in thefield butiks_nr? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints Lookup
This will give all the tables whose constraints reference a given table
/*
All the tables that reference a given table in their constraints.
*/
SELECT
a1.table_name Target Table
,a1.constraint_name Target Constraint
,d1.column_name Target Column
,b1.table_name Referencing Table
,b1.constraint_name Referencing Constraint
,c1.column_name Referencing Column
FROM DBA_CONS_COLUMNS C1
, DBA_CONSTRAINTS A1
, DBA_CONSTRAINTS b1
,DBA_CONS_COLUMNS d1
where
a1.table_name=d1.table_name and
C1.Table_name=b1.table_name and
d1.constraint_name = a1.constraint_name and
C1.Constraint_Name = b1.constraint_name and
b1.constraint_name = a1.r_constraint_name and
A1.table_name =UPPER('TAB') AND a1.owner=UPPER('OWNER')
order by 3,4;
PILOTTO Diego
TECSIS To: Multiple recipients of list ORACLE-L
TCSPIL [EMAIL PROTECTED]
@SIDERAR.COMcc:
Sent by: rootSubject: RE: Constraints Lookup
04/23/2002
06:23 PM
Please
respond to
ORACLE-L
Try this
SELECT a1.constraint_name NAME,
DECODE ( a1.CONSTRAINT_TYPE, 'C', 'Check', 'P', 'Primary Key',
'R', 'Referential Integrity', 'U', 'Unique Key', 'V', 'Check Option on a
view') TYPE ,
a1.r_constraint_name RNAME, a1.status, a1.delete_rule,
c1.column_name, c1.position, R_CONSTRAINT_NAME, R_OWNER ,
a1.search_condition CONSTEXT
FROM USER_CONS_COLUMNS C1, USER_CONSTRAINTS A1
WHERE a1.CONSTRAINT_TYPE = 'R' -Only Ref. Const.
AND C1.Table_name=A1.table_name
AND C1.Constraint_Name = A1.constraint_name
AND C1.owner = A1.owner
AND A1.table_name =TAB
AND a1.owner=OWNER
ORDER BY 1,7
-Original Message-
Sent: Tuesday, April 23, 2002 5:53 PM
To: Multiple recipients of list ORACLE-L
Yes, this should be easy
I need to find all the tables that referance a given table in their
constraints. Can somebody help, it's been a long day
TIA,
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Weatherman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: PILOTTO DiegoTECSIS
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like
Constraints Lookup
Yes, this should be easy I need to find all the tables that referance a given table in their constraints. Can somebody help, it's been a long day TIA, John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints Lookup
Try this SELECT a1.constraint_name NAME, DECODE ( a1.CONSTRAINT_TYPE, 'C', 'Check', 'P', 'Primary Key', 'R', 'Referential Integrity', 'U', 'Unique Key', 'V', 'Check Option on a view') TYPE , a1.r_constraint_name RNAME, a1.status, a1.delete_rule, c1.column_name, c1.position, R_CONSTRAINT_NAME, R_OWNER , a1.search_condition CONSTEXT FROM USER_CONS_COLUMNS C1, USER_CONSTRAINTS A1 WHERE a1.CONSTRAINT_TYPE = 'R' -Only Ref. Const. AND C1.Table_name=A1.table_name AND C1.Constraint_Name = A1.constraint_name AND C1.owner = A1.owner AND A1.table_name =TAB AND a1.owner=OWNER ORDER BY 1,7 -Original Message- Sent: Tuesday, April 23, 2002 5:53 PM To: Multiple recipients of list ORACLE-L Yes, this should be easy I need to find all the tables that referance a given table in their constraints. Can somebody help, it's been a long day TIA, John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PILOTTO DiegoTECSIS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
deferred constraints
Hello, Env: 8.1.6.0.0 on Solaris 2.7 From reading the docs regarding constraint states, it sounds like there is no difference in constraint behaviour between a constraint created as not deferred vs a constraint created as deferred and initially immediate. Is this correct? The only difference I can see is that a constraint created as deferred can subsequently be altered to other constraint states, whereas a constraint created as not deferred cannot. If the above is correct, is it a good practice to make the default for constraint creation deferred and initially immediate? The justification for this would be the flexibility of altering the constraint state, should the need ever arise. How do most of you create your constraints, as deferred or not deferred? Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: script to rename constraints
I don't have one, but would love to have one. ( hint, hint ) Jared On Tuesday 19 February 2002 03:28, John Dunn wrote: Anyone got a script that will drop table constraints which have system generated names and recreate them with names based upon the table name? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Import Behaviour on constraints
Title: Import Behaviour on constraints Hi list, Here is a quick question which often bothers me I have a schema A, B and C All three have integrity constraints referring to each other like, Schema A has some child tables whose parent tables are in schema B Schema B has some child tables whose parent tables are in schema C Schema C has some child tables whose parent tables are in schema A Means all schemas are having parent and child tables, Now if I have to move the fresh data made in schema A on production db to the test db, what is the most appropriate way, The changes on the production database may be in terms of DDL/DML/Constraints/Triggers etc. If I drop all the objects in schema A(using cascade constraints) on Test Box, and then import it from production db then what will happen to the foreign keys in schema B and C which referred to the master tables in schema A. Are they lost forever ( I mean the constraints).??? Or should I try some better way to have one schema refreshed. Whole DB cloning at Test box is one way, but I want just one schema to be refreshed at a time. Any suggestions? Thanks in Advance Arslan Zaheer Dar [EMAIL PROTECTED] Database Administrator Shaukat Khanum Memorial Cancer Hospital Research Centre www.shaukatkhanum.org.pk
Constraints
Tell me if I have this right .. There are not supposed to be any Duplicate Constraint names in your Database, right ?? Right now I have a database that has 48 constraints that are duplicated in name. They are constraints that have a system generated name. Its on Oracle 8.0.5. Anyone know of any known bug that would allow this ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraints
Can we see the script that allowed you to determine this? Are you sure you're accounting for different users? Brian Kevin Lange wrote: Tell me if I have this right .. There are not supposed to be any Duplicate Constraint names in your Database, right ?? Right now I have a database that has 48 constraints that are duplicated in name. They are constraints that have a system generated name. Its on Oracle 8.0.5. Anyone know of any known bug that would allow this ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group | || | mailto:[EMAIL PROTECTED] | | http://bmcgraw.home.mindspring.com | -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian McGraw INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
OK. My problem . and a bad error message from Oracle.. Never mind. -Original Message- Sent: Tuesday, January 08, 2002 2:53 PM To: Multiple recipients of list ORACLE-L Tell me if I have this right .. There are not supposed to be any Duplicate Constraint names in your Database, right ?? Right now I have a database that has 48 constraints that are duplicated in name. They are constraints that have a system generated name. Its on Oracle 8.0.5. Anyone know of any known bug that would allow this ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
CREATE UNIQUE INDEX SYS.I_CON1 ON SYS.CON$(OWNER#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE SYSTEM STORAGE(INITIAL 16K NEXT 96K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING NOPARALLEL / -Original Message- Sent: Tuesday, January 08, 2002 3:53 PM To: Multiple recipients of list ORACLE-L Tell me if I have this right .. There are not supposed to be any Duplicate Constraint names in your Database, right ?? Right now I have a database that has 48 constraints that are duplicated in name. They are constraints that have a system generated name. Its on Oracle 8.0.5. Anyone know of any known bug that would allow this ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
Title: RE: Constraints
-Original Message-
From: Kevin Lange [mailto:[EMAIL PROTECTED]]
There are not supposed to be any Duplicate Constraint names in your
Database, right ??
Right now I have a database that has 48 constraints that are
duplicated in
name. They are constraints that have a system generated name.
Its on Oracle 8.0.5. Anyone know of any known bug that
would allow this ?
The constraints probably have different owners. See example below.
LQS create table wolfgang.t (n number not null) ;
Table créée.
LQS select constraint_name from dba_constraints
2 where owner = 'WOLFGANG' and table_name = 'T' ;
CONSTRAINT_NAME
--
SYS_C001631
LQS create table amedeus.t (n number constraint sys_c001631 not null) ;
Table créée.
LQS select owner, constraint_name
2 from dba_constraints
3 where owner in ('WOLFGANG', 'AMEDEUS') and table_name = 'T' ;
OWNER CONSTRAINT_NAME
-- --
WOLFGANG SYS_C001631
AMEDEUS SYS_C001631
Re: Constraints
who is the owner of the duplicated constraints? if the owner names are different, could you have created one set by import from the other user? just like any other object, the name can be the same if the owners are different --- Kevin Lange [EMAIL PROTECTED] wrote: Tell me if I have this right .. There are not supposed to be any Duplicate Constraint names in your Database, right ?? Right now I have a database that has 48 constraints that are duplicated in name. They are constraints that have a system generated name. Its on Oracle 8.0.5. Anyone know of any known bug that would allow this ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
Are they all in the same schema? -Original Message- Sent: Tuesday, January 08, 2002 12:53 PM To: Multiple recipients of list ORACLE-L Tell me if I have this right .. There are not supposed to be any Duplicate Constraint names in your Database, right ?? Right now I have a database that has 48 constraints that are duplicated in name. They are constraints that have a system generated name. Its on Oracle 8.0.5. Anyone know of any known bug that would allow this ? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Disabling Constraints
Here is a script which generates all of the 'alter table' statements to drop
the referential constrains. Spool if or cut and paste and you should be all
set.
select 'alter table schema.'||table_name||' disable constraint
'||constraint_name||';'
from dba_constraints
where CONSTRAINT_TYPE in ('R')
and owner='SCHEMA';
HTH,
Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, November 26, 2001 5:31 PM
I need to disable some constraints to load table data for my DB
conversion.
I cannot find the syntax to do this. I think it should be ALTER TABLE
something.
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient System, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ruth Gramolini
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Disabling Constraints
Ruth:
Thanks, I found this to work also.
ALTER TABLE 'TABLE_NAME
DISABLE CONSTRAINGT CONSTRAINT_NAME;
Ken
-Original Message-
Sent: Tuesday, November 27, 2001 7:00 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: Disabling Constraints
Here is a script which generates all of the 'alter table' statements to drop
the referential constrains. Spool if or cut and paste and you should be all
set.
select 'alter table schema.'||table_name||' disable constraint
'||constraint_name||';'
from dba_constraints
where CONSTRAINT_TYPE in ('R')
and owner='SCHEMA';
HTH,
Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, November 26, 2001 5:31 PM
I need to disable some constraints to load table data for my DB
conversion.
I cannot find the syntax to do this. I think it should be ALTER TABLE
something.
Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient System, Inc.
Minneapolis, MN
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ruth Gramolini
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ken Janusz
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Disabling Constraints
Ken, ALTER TABLE mytable DISABLE CONSTRAINT myconstraint. This should work Ramon E. Estevez [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 809-565-3121 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ken Janusz Enviado el: Monday, 26 November, 2001 5:31 PM Para: Multiple recipients of list ORACLE-L Asunto: Disabling Constraints I need to disable some constraints to load table data for my DB conversion. I cannot find the syntax to do this. I think it should be ALTER TABLE something. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Disabling Constraints
I need to disable some constraints to load table data for my DB conversion. I cannot find the syntax to do this. I think it should be ALTER TABLE something. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Disabling Constraints
To disable a constraint: alter table table-name disable constraint constraint-name ; should do the trick. Assuming you don't have any constraint violations, the following should work just fine to enable the constraint: alter table table-name enable constraint constraint-name ; Kent At 02:31 PM 11/26/01 -0800, you wrote: I need to disable some constraints to load table data for my DB conversion. I cannot find the syntax to do this. I think it should be ALTER TABLE something. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kent Wayson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
renaming Constraints
Title: renaming Constraints Hi list, is there any easiar way to rename a constraint other then dropping and then recreating it, cuz in our environment, developers mess up the constraints with naming them using system assigned names and then after finilizing the tables and relations, i have to drop/recreate all the constraints following the naming convetion for them. is there any easiar way, like updating the data dictionary directly, any harm in that, if not, which are the key tables i am supposed to play with? Thanks in Advance Arslan
RE: renaming Constraints
Arslan, This is from the SearchDatabase.com DBA Tip Newsletter (www.searchdatabase.com). I haven't tried it myself though.. -- Renaming foreign keys By Terry Plantz Here is a script that renames foreign keys from system-assigned constraint names to more intelligible names. The names use the current table name and the referenced table name. This greatly improves readability, especially for error messages. The complete script is too long to list here, so click the link below to view it online: http://www.searchDatabase.com/tip/1,289483,sid13_gci759997,00.html -- HTH Mark -Original Message- Sent: 19 November 2001 11:11 To: Multiple recipients of list ORACLE-L Hi list, is there any easiar way to rename a constraint other then dropping and then recreating it, cuz in our environment, developers mess up the constraints with naming them using system assigned names and then after finilizing the tables and relations, i have to drop/recreate all the constraints following the naming convetion for them. is there any easiar way, like updating the data dictionary directly, any harm in that, if not, which are the key tables i am supposed to play with? Thanks in Advance Arslan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
enabling foreign key constraints
Hi, When we do alter table table_name disable primary key cascade;..it also disable all the foreign key constraints but when we after do alter table table_name enable primary key..it does not enable foreign keys is there any syntax that we can enable all th foreign keys referencing particular table.. Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ENABLING FOREIGN KEY CONSTRAINTS
Hi, When we do alter table table_name disable primary key cascade;..it also disable all the foreign key constraints but when we after do alter table table_name enable primary key..it does not enable foreign keys is there any syntax that we can enable all th foreign keys referencing particular table.. Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ENABLING FOREIGN KEY CONSTRAINTS
Harvinder, Spool this to a file, then run it after re-enabling your PK. BTW, you could do the reverse of this to disable the FKs, too. Select 'Alter Table ' || Table_Name || ' Enable Constraint ' || Constraint_Name || ' ;' From User_Constraints Where R_Constraint_Name = Re-Enabled_PK_Name ; Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Singh Sent: Wednesday, September 26, 2001 11:40 AM To: Multiple recipients of list ORACLE-L Hi, When we do alter table table_name disable primary key cascade;..it also disable all the foreign key constraints but when we after do alter table table_name enable primary key..it does not enable foreign keys is there any syntax that we can enable all th foreign keys referencing particular table.. Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ENABLING FOREIGN KEY CONSTRAINTS
Title: RE: ENABLING FOREIGN KEY CONSTRAINTS -Original Message- From: Harvinder Singh [mailto:[EMAIL PROTECTED]] When we do alter table table_name disable primary key cascade;..it also disable all the foreign key constraints but when we after do alter table table_name enable primary key..it does not enable foreign keys is there any syntax that we can enable all th foreign keys referencing particular table.. Use the SQL from SQL approach: select 'alter table ' || b.owner || '.' || b.table_name || ' enable constraint ' || b.constraint_name || ' ;' as sql_text from dba_constraints a, dba_constraints b where a.owner = 'TABLE_WITH_PK_OWNER' and a.table_name = 'TABLE_WITH_PK_NAME' and a.constraint_type = 'P' and b.constraint_type = 'R' and b.r_owner = a.owner and b.r_constraint_name = a.constraint_name and b.status = 'DISABLED' ; The query will generate all the SQL statements you need to re-enable the foreign key constraints.
RE: constraints in create table..
Title: Message Try inserting into it, you will not be able to insert anything. Although Oracle allows it, it will not let you put data in it. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message-From: Saurabh Sharma [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 29, 2001 2:31 PMTo: Multiple recipients of list ORACLE-LSubject: constraints in create table.. hi all, do anybody explain me what is happening when i'm creating a table with the following constraints.. CREATE TABLE TAB1( ENUM NUMBER PRIMARY KEY CHECK(ENUM IS NULL), ENAME VARCHAR2(10) ); why i'm being able to create table with such contradicting constraints. both can't be true at the same time. i can't have null in aprimary key. what's the significance of this DDL. thanks in advance.. Saurabh Sharma
constraints in create table..
hi all, do anybody explain me what is happening when i'm creating a table with the following constraints.. CREATE TABLE TAB1( ENUM NUMBER PRIMARY KEY CHECK(ENUM IS NULL), ENAME VARCHAR2(10) ); why i'm being able to create table with such contradicting constraints. both can't be true at the same time. i can't have null in aprimary key. what's the significance of this DDL. thanks in advance.. Saurabh Sharma
dyn sql(get the constraints script)
for those of you with metalstink, its Doc ID: Note:1016836.6 for those of you who dont(and want it, email me). have a good weekend everyone(for those of you already into the weekend in Australia and NZ, what does the weekend look like, good? joe
foreign key constraints
Let's say I had two tables: CREATE TABLE t1 ( col1 NUMBER PRIMARY KEY); CREATE TABLE t2 ( col2 REFERENCES t1(col1), col3 VARCHAR2(40)); Then I ran the query: select a.table_name, b.table_name, b.column_name from user_constraints a, user_cons_columns b where a.r_constraint_name = b.constraint_name and a.constraint_type='R'; I will get back a result set containing a.table_name = t2 b.table_name = t1 b.column_name = col1 But what query would I run to find col2? Thanks, g -- Guy Hammond AVT Technologies 12-16 Westland Place London N1 7LP UK Email: [EMAIL PROTECTED] Office: +44 (0) 207 454 1224 Mobile: +44 (0) 7966 164687 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guy Hammond INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: foreign key constraints
Actually, I have it: select a.table_name, b.table_name, b.column_name, c.column_name from user_constraints a, user_cons_columns b, user_cons_columns c where a.r_constraint_name = b.constraint_name and a.constraint_name = c.constraint_name and b.column_name != c.column_name and a.constraint_type='R'; :0) g -Original Message- From: Guy Hammond Sent: Tuesday, August 21, 2001 2:44 PM To: '[EMAIL PROTECTED]' Subject: foreign key constraints Let's say I had two tables: CREATE TABLE t1 ( col1 NUMBER PRIMARY KEY); CREATE TABLE t2 ( col2 REFERENCES t1(col1), col3 VARCHAR2(40)); Then I ran the query: select a.table_name, b.table_name, b.column_name from user_constraints a, user_cons_columns b where a.r_constraint_name = b.constraint_name and a.constraint_type='R'; I will get back a result set containing a.table_name = t2 b.table_name = t1 b.column_name = col1 But what query would I run to find col2? Thanks, g -- Guy Hammond AVT Technologies 12-16 Westland Place London N1 7LP UK Email: [EMAIL PROTECTED] Office: +44 (0) 207 454 1224 Mobile: +44 (0) 7966 164687 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guy Hammond INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Deferred constraints (?)
Do we have the concept of deferred CHECK constraints? For a constraint of this nature, I don't Oracle to check the whole table as I have done the fixes to ensure there is no occurrence of invalid condition. I only want the future ones to be checked. ALTER TABLE KB.table ADD CONSTRAINT CHECK_DATES CHECK (ENDTIME = STARTTIME AND STOPTIME = NENDTIME); Thanks Kumanan ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper and Nortons Anti-Virus, for the presence of computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kumanan Balasundaram INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: primary/ foreign key constraints for oltp, overhead?...soluti
Title: RE: primary/ foreign key constraints for oltp, overhead?...soluti Thanks Jared -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 16, 2001 11:40 PM To: Multiple recipients of list ORACLE-L Subject: Re: primary/ foreign key constraints for oltp, overhead?...soluti On Friday 15 June 2001 21:50, Amar Kumar Padhi wrote: No indexes on foreign key. still the same result. Of course, because you don't have the indexes on the FK constraints. Now it's time to RTFM please. Read the concepts manual on relational integrity. Build sample tables and play with RI until you have a solid understanding of it. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: primary/ foreign key constraints for oltp, overhead?...soluti
Title: RE: primary/ foreign key constraints for oltp, overhead?...soluti Thanks Anita -Original Message- From: A. Bardeen [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 16, 2001 5:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: primary/ foreign key constraints for oltp, overhead?...soluti Amar, That's the whole point Jared was trying to make: your locking issue is because you *don't* have indexes on your foreign keys. You might want to review the following notes: 11828.1 FOREIGN KEYS, INDEXES AND PARENT TABLE LOCKING 33453.1 (V7) REFERENTIAL INTEGRITY AND LOCKING 38373.1 TECH: Example Table Locks During Referential Integrity Enforcement 61552.1 DIAGNOSING DATABASE HANGING ISSUES 15476.1 Detecting and Resolving Locking Conflicts HTH, -- Anita --- Amar Kumar Padhi [EMAIL PROTECTED] wrote: No indexes on foreign key. still the same result. rgds amar -Original Message- Sent: Monday, June 11, 2001 2:05 PM To: Multiple recipients of list ORACLE-L overhead?...soluti Jared has a point. Is there an index on the foreign key? Another minor detail: You should of course never update a primary key column, of course :-) Amar Kumar Padhi wrote: Jared, I tried again and got the same result. on oracle 8.1.7 (RBO). session1 : delete from emp where deptno = 10; session2 : delete from dept where deptno = 30; session 2 hanged and waited till transaction in session one was committed. the max_trans on both tab is set to 255, incase this raises doubt. are you getting a different result? Is the FK present on the detail table you are trying with? rgds amar -Original Message- Sent: Monday, June 11, 2001 10:25 AM To: Multiple recipients of list ORACLE-L overhead?...soluti On Sunday 10 June 2001 21:50, Amar Kumar Padhi wrote: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. You should probably test this scenario, as it works fine for me. Delete the a child detail row. Delete an unrelated parent row, with delete cascade on the child table. Works fine. Possibly are missing an index? Some of these rules have changed I know, but I'll let you look up the details. Bottom line, it does work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: primary/ foreign key constraints for oltp, overhead?...soluti
Amar, That's the whole point Jared was trying to make: your locking issue is because you *don't* have indexes on your foreign keys. You might want to review the following notes: 11828.1 FOREIGN KEYS, INDEXES AND PARENT TABLE LOCKING 33453.1 (V7) REFERENTIAL INTEGRITY AND LOCKING 38373.1 TECH: Example Table Locks During Referential Integrity Enforcement 61552.1 DIAGNOSING DATABASE HANGING ISSUES 15476.1 Detecting and Resolving Locking Conflicts HTH, -- Anita --- Amar Kumar Padhi [EMAIL PROTECTED] wrote: No indexes on foreign key. still the same result. rgds amar -Original Message- Sent: Monday, June 11, 2001 2:05 PM To: Multiple recipients of list ORACLE-L overhead?...soluti Jared has a point. Is there an index on the foreign key? Another minor detail: You should of course never update a primary key column, of course :-) Amar Kumar Padhi wrote: Jared, I tried again and got the same result. on oracle 8.1.7 (RBO). session1 : delete from emp where deptno = 10; session2 : delete from dept where deptno = 30; session 2 hanged and waited till transaction in session one was committed. the max_trans on both tab is set to 255, incase this raises doubt. are you getting a different result? Is the FK present on the detail table you are trying with? rgds amar -Original Message- Sent: Monday, June 11, 2001 10:25 AM To: Multiple recipients of list ORACLE-L overhead?...soluti On Sunday 10 June 2001 21:50, Amar Kumar Padhi wrote: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. You should probably test this scenario, as it works fine for me. Delete the a child detail row. Delete an unrelated parent row, with delete cascade on the child table. Works fine. Possibly are missing an index? Some of these rules have changed I know, but I'll let you look up the details. Bottom line, it does work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com --
RE: primary/ foreign key constraints for oltp, overhead?...soluti
No indexes on foreign key. still the same result. rgds amar -Original Message- Sent: Monday, June 11, 2001 2:05 PM To: Multiple recipients of list ORACLE-L overhead?...soluti Jared has a point. Is there an index on the foreign key? Another minor detail: You should of course never update a primary key column, of course :-) Amar Kumar Padhi wrote: Jared, I tried again and got the same result. on oracle 8.1.7 (RBO). session1 : delete from emp where deptno = 10; session2 : delete from dept where deptno = 30; session 2 hanged and waited till transaction in session one was committed. the max_trans on both tab is set to 255, incase this raises doubt. are you getting a different result? Is the FK present on the detail table you are trying with? rgds amar -Original Message- Sent: Monday, June 11, 2001 10:25 AM To: Multiple recipients of list ORACLE-L overhead?...soluti On Sunday 10 June 2001 21:50, Amar Kumar Padhi wrote: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. You should probably test this scenario, as it works fine for me. Delete the a child detail row. Delete an unrelated parent row, with delete cascade on the child table. Works fine. Possibly are missing an index? Some of these rules have changed I know, but I'll let you look up the details. Bottom line, it does work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - SSN( Enforced Constraints (sic) ??
Hell, when I joined the gym they asked for it. I asked them what they needed for and they said they didn't and I did not have to supply it. Duh... I find it amazing how much they ask for the SSN here in the US. I don't recall being asked for my Social Insurance number this much back home in Canada. Hum, and there is less stealing of identities there. Hey, I may be onto something there -Original Message- Sent: Tuesday, June 12, 2001 3:33 PM To: Multiple recipients of list ORACLE-L When the DMV first asked for my SSN, I gave them an incorrect one, because as far as I'm concerned they don't need to know it. Now that they actually check the number with the SSA I was forced to correct it. Most places are forbidden to require that you supply your SSN unless they need it to report taxable earnings. I regularly refuse to supply mine. At 10:58 AM 6/12/01 -0800, you wrote: It's interesting that SSNs are not recycled. My mother works for the SSA and says they are after the person is deceased for a certain period of time (I forget the specifics). Also, a lot of people get nervous when you use their SSN for an identifier. I've noticed that most State Driver's Licenses do not use the SSN at the license number anymore because people were getting their identities stolen. The same thing goes for having it printed permanently on your checks. Let's calculate quickly on this one: 250,000,000 million in the U.S. (Legal) 999,999,999 possible numbers (xxx-xx-) - equals mandatory recycling. --Michael -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 2:18 PM To: Multiple recipients of list ORACLE-L There is also a problem with SSNs, if you deal with Foreign nationals. We had that problem with a US Army program in Europe and Korea. We had to make up Unique numbers to fit the program. Georgette -Original Message- Sent: Tuesday, June 12, 2001 12:41 To: Multiple recipients of list ORACLE-L SSN's are not recycled. From the SSA OIG's testimony on SSN use and misuse before congress - The SSN is a unique identifier http://www.ssa.gov/oig/Testimony05222001.htm SSN is a perfectly good PK for personnel systems. If you find duplicates your employer certainly needs to know about it. I once had a personnel database with 25 years of personnel data (about 1,200,000 individuals). When we loaded it into Oracle with the SSN as PK, there was one duplicate SSN. Of course, that employer was very conscientious about requiring a valid SSN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cornio, Georgette Ms USACFSC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenkins, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
RE: Enforced Constraints (sic) ??
There is also a problem with SSNs, if you deal with Foreign nationals. We had that problem with a US Army program in Europe and Korea. We had to make up Unique numbers to fit the program. Georgette -Original Message- Sent: Tuesday, June 12, 2001 12:41 To: Multiple recipients of list ORACLE-L SSN's are not recycled. From the SSA OIG's testimony on SSN use and misuse before congress - The SSN is a unique identifier http://www.ssa.gov/oig/Testimony05222001.htm SSN is a perfectly good PK for personnel systems. If you find duplicates your employer certainly needs to know about it. I once had a personnel database with 25 years of personnel data (about 1,200,000 individuals). When we loaded it into Oracle with the SSN as PK, there was one duplicate SSN. Of course, that employer was very conscientious about requiring a valid SSN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cornio, Georgette Ms USACFSC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Enforced Constraints (sic) ??
It's interesting that SSNs are not recycled. My mother works for the SSA and says they are after the person is deceased for a certain period of time (I forget the specifics). Also, a lot of people get nervous when you use their SSN for an identifier. I've noticed that most State Driver's Licenses do not use the SSN at the license number anymore because people were getting their identities stolen. The same thing goes for having it printed permanently on your checks. Let's calculate quickly on this one: 250,000,000 million in the U.S. (Legal) 999,999,999 possible numbers (xxx-xx-) - equals mandatory recycling. --Michael -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 2:18 PM To: Multiple recipients of list ORACLE-L There is also a problem with SSNs, if you deal with Foreign nationals. We had that problem with a US Army program in Europe and Korea. We had to make up Unique numbers to fit the program. Georgette -Original Message- Sent: Tuesday, June 12, 2001 12:41 To: Multiple recipients of list ORACLE-L SSN's are not recycled. From the SSA OIG's testimony on SSN use and misuse before congress - The SSN is a unique identifier http://www.ssa.gov/oig/Testimony05222001.htm SSN is a perfectly good PK for personnel systems. If you find duplicates your employer certainly needs to know about it. I once had a personnel database with 25 years of personnel data (about 1,200,000 individuals). When we loaded it into Oracle with the SSN as PK, there was one duplicate SSN. Of course, that employer was very conscientious about requiring a valid SSN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cornio, Georgette Ms USACFSC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenkins, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Enforced Constraints (sic) ??
Close. You forgot about the violation of one of the key rules in creating unique keys. Do not embed meaning in the key. The first 3 numbers are determined on where you apply for the SSN. That reduces the usable numbers. Someone once told me that the two numbers in the middle mean something too, but I have not been able to confirm it. 250,000,000 million in the U.S. (Legal) 999,999,999 possible numbers (xxx-xx-) - equals mandatory recycling. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Page, Bruce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - SSN( Enforced Constraints (sic) ??
When the DMV first asked for my SSN, I gave them an incorrect one, because as far as I'm concerned they don't need to know it. Now that they actually check the number with the SSA I was forced to correct it. Most places are forbidden to require that you supply your SSN unless they need it to report taxable earnings. I regularly refuse to supply mine. At 10:58 AM 6/12/01 -0800, you wrote: It's interesting that SSNs are not recycled. My mother works for the SSA and says they are after the person is deceased for a certain period of time (I forget the specifics). Also, a lot of people get nervous when you use their SSN for an identifier. I've noticed that most State Driver's Licenses do not use the SSN at the license number anymore because people were getting their identities stolen. The same thing goes for having it printed permanently on your checks. Let's calculate quickly on this one: 250,000,000 million in the U.S. (Legal) 999,999,999 possible numbers (xxx-xx-) - equals mandatory recycling. --Michael -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 2:18 PM To: Multiple recipients of list ORACLE-L There is also a problem with SSNs, if you deal with Foreign nationals. We had that problem with a US Army program in Europe and Korea. We had to make up Unique numbers to fit the program. Georgette -Original Message- Sent: Tuesday, June 12, 2001 12:41 To: Multiple recipients of list ORACLE-L SSN's are not recycled. From the SSA OIG's testimony on SSN use and misuse before congress - The SSN is a unique identifier http://www.ssa.gov/oig/Testimony05222001.htm SSN is a perfectly good PK for personnel systems. If you find duplicates your employer certainly needs to know about it. I once had a personnel database with 25 years of personnel data (about 1,200,000 individuals). When we loaded it into Oracle with the SSN as PK, there was one duplicate SSN. Of course, that employer was very conscientious about requiring a valid SSN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cornio, Georgette Ms USACFSC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenkins, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT - SSN( Enforced Constraints (sic) ??
except check out the ssn faq and alot of those numbers are not used. joe PS: http://www.faqs.org/faqs/privacy/ssn-faq/ Regina Harter wrote: When the DMV first asked for my SSN, I gave them an incorrect one, because as far as I'm concerned they don't need to know it. Now that they actually check the number with the SSA I was forced to correct it. Most places are forbidden to require that you supply your SSN unless they need it to report taxable earnings. I regularly refuse to supply mine. At 10:58 AM 6/12/01 -0800, you wrote: It's interesting that SSNs are not recycled. My mother works for the SSA and says they are after the person is deceased for a certain period of time (I forget the specifics). Also, a lot of people get nervous when you use their SSN for an identifier. I've noticed that most State Driver's Licenses do not use the SSN at the license number anymore because people were getting their identities stolen. The same thing goes for having it printed permanently on your checks. Let's calculate quickly on this one: 250,000,000 million in the U.S. (Legal) 999,999,999 possible numbers (xxx-xx-) - equals mandatory recycling. --Michael -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 2:18 PM To: Multiple recipients of list ORACLE-L There is also a problem with SSNs, if you deal with Foreign nationals. We had that problem with a US Army program in Europe and Korea. We had to make up Unique numbers to fit the program. Georgette -Original Message- Sent: Tuesday, June 12, 2001 12:41 To: Multiple recipients of list ORACLE-L SSN's are not recycled. From the SSA OIG's testimony on SSN use and misuse before congress - The SSN is a unique identifier http://www.ssa.gov/oig/Testimony05222001.htm SSN is a perfectly good PK for personnel systems. If you find duplicates your employer certainly needs to know about it. I once had a personnel database with 25 years of personnel data (about 1,200,000 individuals). When we loaded it into Oracle with the SSN as PK, there was one duplicate SSN. Of course, that employer was very conscientious about requiring a valid SSN. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cornio, Georgette Ms USACFSC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenkins, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing) -- Joe Testa
RE: primary/ foreign key constraints for oltp, overhead?...soluti
Jared, I tried again and got the same result. on oracle 8.1.7 (RBO). session1 : delete from emp where deptno = 10; session2 : delete from dept where deptno = 30; session 2 hanged and waited till transaction in session one was committed. the max_trans on both tab is set to 255, incase this raises doubt. are you getting a different result? Is the FK present on the detail table you are trying with? rgds amar -Original Message- Sent: Monday, June 11, 2001 10:25 AM To: Multiple recipients of list ORACLE-L overhead?...soluti On Sunday 10 June 2001 21:50, Amar Kumar Padhi wrote: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. You should probably test this scenario, as it works fine for me. Delete the a child detail row. Delete an unrelated parent row, with delete cascade on the child table. Works fine. Possibly are missing an index? Some of these rules have changed I know, but I'll let you look up the details. Bottom line, it does work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: primary/ foreign key constraints for oltp, overhead?...soluti
Jared has a point. Is there an index on the foreign key? Another minor detail: You should of course never update a primary key column, of course :-) Amar Kumar Padhi wrote: Jared, I tried again and got the same result. on oracle 8.1.7 (RBO). session1 : delete from emp where deptno = 10; session2 : delete from dept where deptno = 30; session 2 hanged and waited till transaction in session one was committed. the max_trans on both tab is set to 255, incase this raises doubt. are you getting a different result? Is the FK present on the detail table you are trying with? rgds amar -Original Message- Sent: Monday, June 11, 2001 10:25 AM To: Multiple recipients of list ORACLE-L overhead?...soluti On Sunday 10 June 2001 21:50, Amar Kumar Padhi wrote: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. You should probably test this scenario, as it works fine for me. Delete the a child detail row. Delete an unrelated parent row, with delete cascade on the child table. Works fine. Possibly are missing an index? Some of these rules have changed I know, but I'll let you look up the details. Bottom line, it does work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: primary/ foreign key constraints for oltp, overhead?...soluti
Thanks to all who replied. following is what I have gathered after some rd: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. -imp exp While importing and exporting, oracle throws an error if the table structure already exits and the detail records are being inserted first. This error can be avoided by disabling the foreign key. No error is generated if the table structure is being recreated from the dump, as oracle takes care of enabling the foreign key constraint after importing rows. - triggers Delete (for each row) trigger on master table will mutate if detail table is being referred in the trigger. This will only happen if the foreign key on the detail table is created with on delete cascade option. No mutation occurs if master table is being referred in a trigger on the detail table. rgds amar -Original Message- Hi, my current project database has no primary/ foreign key constraints. Curious about this basic check missing in the system, I was informed that these constraints result in lot of locking issues and would be a bottleneck to huge OLTP Systems. As per my knowledge, locking of master as well as detail would only occur if one is trying to delete a master record the corresponding details records exists. In this case both master and detail records are locked (on delete cascade option). Has anyone come across such issues in huge dbs, where using these constraints actually resulted in performance issues? rgds amar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: primary/ foreign key constraints for oltp, overhead?...soluti
Thanks to all who replied. following is what I have gathered after some rd: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. -imp exp While importing and exporting, oracle throws an error if the table structure already exits and the detail records are being inserted first. This error can be avoided by disabling the foreign key. No error is generated if the table structure is being recreated from the dump, as oracle takes care of enabling the foreign key constraint after importing rows. - triggers Delete (for each row) trigger on master table will mutate if detail table is being referred in the trigger. This will only happen if the foreign key on the detail table is created with on delete cascade option. No mutation occurs if master table is being referred in a trigger on the detail table. rgds amar -Original Message- Sent: Saturday, June 09, 2001 7:54 AM To: '[EMAIL PROTECTED]' Hi, my current project database has no primary/ foreign key constraints. Curious about this basic check missing in the system, I was informed that these constraints result in lot of locking issues and would be a bottleneck to huge OLTP Systems. As per my knowledge, locking of master as well as detail would only occur if one is trying to delete a master record the corresponding details records exists. In this case both master and detail records are locked (on delete cascade option). Has anyone come across such issues in huge dbs, where using these constraints actually resulted in performance issues? rgds amar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amar Kumar Padhi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: primary/ foreign key constraints for oltp, overhead?...soluti
On Sunday 10 June 2001 21:50, Amar Kumar Padhi wrote: - locks Oracle generates internal locks on dict tables to maintain the data integrity. e.g.: I deleted a record from detail table in one session. In another session I tried to delete a master record not related to the deleted detail record. The 2nd session hanged. There were row-exclusive locks present on the detail table and an exclusive lock present on an internal table (I couldn't access). The same is the case with update. Oracle locks both the master detail table records when the master record is deleted and the foreign key is created with on delete cascade option. Bottomline, Oracle doesn't allow altering of master table primary key when active transactions exists on detail table. Oracle will allow update of columns other than the primary key. You should probably test this scenario, as it works fine for me. Delete the a child detail row. Delete an unrelated parent row, with delete cascade on the child table. Works fine. Possibly are missing an index? Some of these rules have changed I know, but I'll let you look up the details. Bottom line, it does work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Conventions for naming indexes, constraints etc.
Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Conventions for naming indexes, constraints etc.
Hi! How it is in our case http://www.itsystems.lv/gints/eng/naming_conventions.htm But this was not mentioned earlier because I created it some minutes ago ;) Gints Plivna "Ranganath K" ranganathk@subexTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] group.com cc: Sent by: Subject: Conventions for naming indexes, constraints etc. [EMAIL PROTECTED] 01.04.18 12:20 Please respond to ORACLE-L Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Conventions for naming indexes, constraints etc.
Hi Gints Plivna, Thanks for the URL. BTW where are you now? Whom are you working for? Regards, Ranganath -Original Message- [EMAIL PROTECTED] Sent: Wednesday, April 18, 2001 4:06 PM To: Multiple recipients of list ORACLE-L Hi! How it is in our case http://www.itsystems.lv/gints/eng/naming_conventions.htm But this was not mentioned earlier because I created it some minutes ago ;) Gints Plivna "Ranganath K" ranganathk@subexTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] group.com cc: Sent by: Subject: Conventions for naming indexes, constraints etc. [EMAIL PROTECTED] 01.04.18 12:20 Please respond to ORACLE-L Hi listers, A couple of days back somebody in the list had sent an URL about the convention to be followed for naming indexes, constraints etc. I have lost it somewhere. Could any of you who have saved and have it now send it to me? I am in need of it very badly. TIA and regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Constraints
Roland, Unique or primary key constraints will drop the associated index if disabled. If reenabled they would need to be done with the storage clause or will just go in the default tablespace. Iain Nicoll -Original Message- Sent: 05 April 2001 11:21 To: Multiple recipients of list ORACLE-L If I disable a constraint for tables in Toad, is it possible that also the index for any of the tables disappear? Roland Skldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (metering) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Check the constraints
Hallo Anyoine know about any program that can check all the constraints after the database is designed? Roland Skldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Check the constraints
Roland, Oracle Designer can reverse engineer an Oracle database and capture all constraint (and other) info. You can then run various reports and/or display the data model in diagrams to allow you to validate the accuracy and completeness of the constraints. It can do much, much more as well. However, it is a complex tool that takes a while to learn and use productively. A CASE tool such as ErWin can capture Oracle DB PKs and FKs on a diagram. I don't know if it can capture Check Constraints, though. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Wednesday, April 04, 2001 9:21 AM To: Multiple recipients of list ORACLE-L Hallo Anyoine know about any program that can check all the constraints after the database is designed? Roland Skldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Check the constraints
Title: RE: Check the constraints Hi, The SQL impact from Toad maybe can help. rgds, -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, April 04, 2001 10:21 AM To: Multiple recipients of list ORACLE-L Subject: Check the constraints Hallo Anyoine know about any program that can check all the constraints after the database is designed? Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Constraints with Synonyms?
Hi, I have a need to create a synonym in a schema (A) for a table that is another schema (B). This is no problem. However, the table in question is a "parent" table and I cannot create the "references" constraint from the "child" to the "parent" when the "parent" is a synonym. When I attempt to do so I get an ORA-1031 error (Insufficient Privileges). Is this a known limitation of synonyms or am I missing something here? I find the 1031 error a strange one to receive if this a limitation of synonyms--schema A has DBA role granted to it so the problem shouldn't be one of permissions. Any suggestions/feedback would be appreciated. Thanks!! -w PS: Recreating the table from schema B in schema A is not an option, unfortunately.
Re: FK Constraints
Hi All, Nobody has proposed this before so let me throw it out for discussion. Think about a compromise between having and not having referential integrity constraints. The approach is that during development and testing, all RIs are enabled. But when you push to production, disable or drop all of them. The advantage of not having or enabling RIs in production is not portability in this case; it's performance instead, however marginal it is. I think I read somewhere about Oracle's official answer to this little performance hit due to RIs. Think about it. There must be some. It shouldn't be hard to measure but I have not done it. Yong Huang [EMAIL PROTECTED] you wrote: We have a situation where are no relationships are defined at the database level. i.e no foreign keys constraints have established at the Database. The application is still at the Development Stage. Everything is controlled at the application level. I as the DBA appose this design for Data security and also cannot reverse engineer from the tables into Designer. Can you please share you pros / Cons. Thanks __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FK Constraints
Rachel Carmichael wrote: the DBA is not responsible for the data?? Could you PLEASE come to my office and explain that (I'll bring the heavy club) to my developers, users and management? They seem to believe that my primary function is to dig information out of the database for them. Backup and recovery? Capacity planning? Why on earth would I want to do THAT? you sure we don't work of rhte same company?;-) [don't i just wish.;-)] and then there's [at 10AM] "oh bill we need a new ORACLE instance up and running for us." "ok, when do you need it by?" "noon." -- Bill Thater Certifiable ORACLE DBA Telergy, Inc [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FK Constraints
Ramani: I live daily with the negative effects of referential integrity enforced (sometimes) at the application level. From my standpoint, there have been no "pros" to this method. I need to use a lot of data from our mainframe DB2 database, where all of the RI is written into their applications. Now I access the data from another avenue - Oracle's transparent gateway - and there is no way for me to tell what tables should join, what the valid values are, and even what codes within a column represent! Of course, when they designed this database 10 years ago, they never took into consideration that another database would access this data; they only thought that their COBOL programs would touch the data. Years later, programmers have moved on, the world has changed, but their database stays the same. The have one huge LOV table where they store codes based on what program/screen needs them (as far as I can figure). This entire design shows a lack of vision. Sorry to ramble, but this is a sore point with me. The bottom line here is that every time someone else needs to access the data, it takes at least 5 times longer to understand the relationships and make things work. And, we are never sure that what we are getting is right. I have at least 20 scientists accessing this data. Imagine not only the productivity that is lost, but can we be sure that the results produced from the analysis of this data is correct based on its lack of credibility? I would never, ever, ever, ever agree to a database without referential integrity imposed by the RDBMS that was in my control. I thank you for the opportunity to vent (-_-) ___ Sherrie Kubis Southwest Florida Water Management District 2379 Broad Street Brooksville FL 34604-6899 Phone: (352) 796-7211, Ext. 4033 Fax: (352) 754-6776 Email: Mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FK Constraints
Rachel, One way I got this across to my developers and users was in creating new users for the DB. Every time I got a new user request, I would forward it to the director of the group that officially "owns" the data to get his permission to allow them in HIS data. After a few round robins of this the people finally realized that when I said Ops, Acctg, etc. owned the data, I also made them responsible for THEIR data. We still have fun every now and then with "Can you recover this for me" type reqests, but most of the time they recognize that I keep the system going and they keep the data correct. On Monday 19 March 2001 18:10, you wrote: the DBA is not responsible for the data?? Could you PLEASE come to my office and explain that (I'll bring the heavy club) to my developers, users and management? -- Rodd Holman Oracle DBA (605) 988-1373 [EMAIL PROTECTED] Comments made are my own opinions and views. They do not represent views, policies, or procedures of LodgeNet Entertainment Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FK Constraints
I don't get new user requests.. I get requests from end-users for data. But I understand. And I know that it's a question of coming in with the baseball bat and assorted heavy weaponry and making a stand (or as my CTO is fond of saying "putting a stake in the ground") Just haven't collected all the cudgels yet :) From: Rodd Holman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: FK Constraints Date: Tue, 20 Mar 2001 07:35:32 -0800 Rachel, One way I got this across to my developers and users was in creating new users for the DB. Every time I got a new user request, I would forward it to the director of the group that officially "owns" the data to get his permission to allow them in HIS data. After a few round robins of this the people finally realized that when I said Ops, Acctg, etc. owned the data, I also made them responsible for THEIR data. We still have fun every now and then with "Can you recover this for me" type reqests, but most of the time they recognize that I keep the system going and they keep the data correct. On Monday 19 March 2001 18:10, you wrote: the DBA is not responsible for the data?? Could you PLEASE come to my office and explain that (I'll bring the heavy club) to my developers, users and management? -- Rodd Holman Oracle DBA (605) 988-1373 [EMAIL PROTECTED] Comments made are my own opinions and views. They do not represent views, policies, or procedures of LodgeNet Entertainment Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:FK Constraints
I for one will say your right. Just because the application is suppose to be used in a particular manner does not mean that it always will be or that someone will not come around through the back door to "patch" something. Also since I'm a firm believer in data having a birth, life, retirement, and death how do you cleanup without leaving orphans laying around? Sure is nice to smack the record in the master table and have the foreign keys cascade the delete for you. Dick Goulet Reply Separator Author: ramani akhil [EMAIL PROTECTED] Date: 3/19/2001 10:05 AM Hi all: We have a situation where are no relationships are defined at the database level. i.e no foreign keys constraints have established at the Database. The application is still at the Development Stage. Everything is controlled at the application level. I as the DBA appose this design for Data security and also cannot reverse engineer from the tables into Designer. Can you please share you pros / Cons. Thanks __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ramani akhil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
