Re: dropping foreign key constraints

Bear in mind that when you re-enable the constraints, you must specify the storage clause again, otherwise the constraint will be rebuilt in the user's default tablespace with 20Kb/20Kb/50%.

A somewhat more challenging exercise :-)

Graham

Patrick Suppes wrote in message <371CEADF.A871877A_at_lucent.com>...
>Jeff,
>
>I don't have a script to drop and recreate the foreign keys, but there is a
>simpler
>method to get there from here.
>
>Instead of dropping the constraints, just disable them.
>
>Syntax:
>
>ALTER TABLE schema.tablename DISABLE CONSTRAINT constraintname
>
>After you are done with your bashing, run
>
>ALTER TABLE schema.tablename ENABLE CONSTRAINT constraintname
>
>If you wait until after your reloads before enabling the constraints, you
will
>probably find the reloads run faster.
>
>As for the magic tool, run
>
> select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME ||
> ' DISABLE CONSTRAINT ' || constraint_name
> from all_constraints
>where owner = 'schema'
> and table_name = 'tablename'
> and constraint_type = 'R'
>
>The ENABLE variant is left as an exercise for the student.
>
>Patrick Suppes
>
>Jeff wrote:
>
>> I need nuke all the data in my the development instance of my Oracle 8
server
>> and use SQL*Loader to re-load accurate data from an outside non-Oracle
system.
>>
>> My question is... does anyone have a script that drops all foreign key
>> constraints in a database and then another script that can re-add them?
I'm
>> wondering if there's a way to do this by using the system catalog tables
such
>> as user_constraints ... maybe?
>>
>> --
>> Jeff Avellanet
>> Database Application Developer
>> Washington, DC
>>
>> -----------== Posted via Deja News, The Discussion Network ==----------
>> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
Received on Tue Apr 20 1999 - 16:58:04 CDT