Re: how to drop a tablespace
Date: 5 Jan 1994 08:56:52 +1100
Message-ID: <2gcon4$aif_at_werple.apana.org.au>
shatzman_at_netcom.com (Barry Shatzman) writes:
>Don Erickson (derickso_at_garden.csc.calpoly.edu) wrote:
>: I need to drop several tablespaces that are no longer used.
>: The command "drop tablespace ts3 including contents" fails with
>: a warning about the presence of foreign keys.
>Don -
>Dropping a tablespace including contents can be very slow and might also
>require a good deal of rollback (due to space maint). I've always
>preferred to drop all the objects myself, then just drop the tablespace.
>An easy way to drop a lot of objects is using SQL to create SQL...
...
>then start drop.sql (REVIEW IT FIRST!!!)
>Now, about those constraints. You'll still hit em. You can be elegant if
>you want, but the brut-force approach of running drop.sql several times
>will also take care of the problem (each time you run it you'll get rid
>of more fk dependencies until there are no fk's left).
>Ugly, but it'll get the job done.
A better way (in V7, anyway) is to make use of the 'cascade constraints' clause of the drop table statement. Add something like
decode(object_type,'TABLE',' CASCADE CONSTRAINTS','') to your drop.sql
guy
Guy Harrison gharriso_at_werple.apana.org.au ORACLE Database Consultant gharriso_at_vitgwms1.telecom.com.auSynchronous Software P/L
Melbourne Australia Received on Tue Jan 04 1994 - 22:56:52 CET