Re: how to drop a tablespace

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.
 

: There are several hundred users and over 2000 tables involved.
: All data has been removed. Now I need to clean up the data
: dictionary.

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...

set termout off
set pagesize 0
SELECT 'drop', object_type, object_name, ';'   FROM sys.dba_objects
 WHERE tablespace....
spool drop.sql
/
spool off

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. Received on Tue Jan 04 1994 - 05:47:00 CET