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).
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
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
To disable a constraint: alter table disable constraint ; should do the trick. Assuming you don't have any constraint violations, the following should work just fine to enable the constraint: alter table enable constraint ; 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).
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 in a Schema!..??
Arul You can use the following generic script to disable all the primary key and the corresponding foreign key constraints. set pages 0 feed off spool fk_dis.sql select 'alter table '||table_name || ' DISABLE constraint ' ||constraint_name ||' CASCADE '||';' from user_constraints ; spool off However be aware that when you Enable the Primary key constraints they don't go to the proper tablespaces instead they go to the user's default tablespaces. At least that is the case with 8.1.5. So what I do for my datawarehouse load is to disable only the Foreign Keys , load the data and try to Re-Enable the foreign keys and write into the exceptions table if there are any. Hope that helps. Surjit Arul kumar <[EMAIL PROTECTED]>@fatcity.com on 02/03/2001 19:30:45 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi DBAs, We r using Oracle 8.1.6 . db. I would like to disable all the constraints on the Tables available under one schema. Is there any single command to do this in 8i . Any DBMS package available? Any other suggestions for doing the same?? Thank You. Arul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arul 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Surjit Sharma 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 in a Schema!..??
Spool the output of thie sql and then run the spooled file:
select 'alter table schema_name.'||table_name||' disable constraint
'||constraint_name||';'
from dba_constraints
where owner='schema_name'
and CONSTRAINT_TYPE in ('R','P','U') This line is optional.
This will disable the constraints in schema_name of choice. You can enable
all of the constraints by changing disable to enable. You can change
disable to drop to drop all constraints.
I got this from a lister on one of the lists. It works very well.
HTH,
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, March 02, 2001 3:30 AM
> Hi DBAs,
>
> We r using Oracle 8.1.6 . db.
> I would like to disable all the constraints on the Tables available
> under one schema.
> Is there any single command to do this in 8i . Any DBMS package
> available?
> Any other suggestions for doing the same??
>
> Thank You.
> Arul.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Arul 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).
--
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 in a Schema!..??
-- the FK select 'alter table '||table_name||' disable '||constraint_name||';' from user_constraints where constraint_type = 'R'; there is check constraints, primary key, and unique, but i'll not give you the entire answer. joe Arul kumar wrote: > > Hi DBAs, > > We r using Oracle 8.1.6 . db. > I would like to disable all the constraints on the Tables available > under one schema. > Is there any single command to do this in 8i . Any DBMS package > available? > Any other suggestions for doing the same?? > > Thank You. > Arul. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Arul 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). -- Joe Testa http://www.oracle-dba.com Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S. Testa 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 in a Schema!..??
Hi DBAs, We r using Oracle 8.1.6 . db. I would like to disable all the constraints on the Tables available under one schema. Is there any single command to do this in 8i . Any DBMS package available? Any other suggestions for doing the same?? Thank You. Arul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arul 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).
