Re: how to drop a tablespace
Date: 5 Jan 94 13:05:54 +1000
Message-ID: <1994Jan5.130554.1_at_cbr.hhcs.gov.au>
In article <2gcon4$aif_at_werple.apana.org.au>, gharriso_at_werple.apana.org.au (Guy Harrison) writes:
> 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
Another variation is to create your drop script so that it
- drop all constraints on the objects in the tablespace
- drop ALL objects (in one pass)
- drop the tablespace.
Be careful that you don't have rollback segments in the tablespace as I have had a corrupted V6 database from this AND be careful that the tablespace isn't being used by some other user as their temporary tablespace (for intermediate sorts etc).
-- Bruce... pihlab_at_cbr.hhcs.gov.au "If you swallow a live frog first thing in the morning ... Nothing worse will happen to either of you for the rest of the day." ******************************************************************* * Bruce Pihlamae -- Database Administration * * Commonwealth Department of * * Health, Housing, Local Government & Community Services * * Canberra, Australia (W) 06-289-7056 * ******************************************************************* * These are my own thoughts and opinions, few that I have. * *******************************************************************Received on Wed Jan 05 1994 - 04:05:54 CET