Re: Can't drop Tablespace???

From: Roderick Manalac <rmanalac_at_oracle.COM>
Date: Sun, 21 Feb 1993 23:17:43 GMT
Message-ID: <1993Feb21.231743.24214_at_oracle.us.oracle.com>

dhiltz_at_whsun1.whoi.edu (David Hiltz) writes:
|>
|> I'm trying to drop a tablespace, but whenever I issue the DROP command,
|> it just hangs.
|>

When dropping a tablespace, the following steps will generally work without fail on any V6 database:

 0.  invoke sqldba
 1.  connect internal
 2.  select count('x') from dba_free_space;
 3.  select segment_name from dba_rollback_segs
        where tablespace_name = <tablespace name>;

 4. shutdown [normal or immediate]
 5. edit the appropriate INIT.ORA file for the instance.

1. set dc_free_extents = value returned by step #3 + fudge factor (100-200)
2. set row_cache_enqueues = value returned by step #3 + fudge factor
3. dereference any rollback segments returned by step #4.
4. sqldba startup
5. connect internal
6. check dba_rollback_segs to make sure the status of the rollback segments returned in step #4 are AVAILABLE and not IN USE.
7. [< V6.0.33 required / >= V6.0.33 optional] drop the rollback segments
8. drop tablespace <tablespace name> [including contents];
9. if an ORA-1555 is encountered repeat step #9 until successful.

It appears that the steps Oracle goes through when dropping a tablespace is to first drop all objects in that tablespace (if the including contents clause was specified) to clean them out of the data dictionary. Then it begins to clean out the free space entries in the data dictionary. Since these entries are also kept in the dictionary cache of the SGA, it speeds things up tremendously to have the DC_FREE_EXTENTS parameter high enough to hold all the information in memory and set ROW_CACHE_ENQUEUES high enough so Oracle can lock all the relevent entries in the SGA while it drops the tablespace.

Note step #8a is required in releases prior to 6.0.33 (and maybe 6.0.33 as well), to avoid some of the bugs with drop tablespace and rollback segments that existed in those older versions.

The only situation not covered is a drop tablespace including contents that fails because a table in the tablespace is referred to by foreign tables.

Hope this helps.
Roderick Manalac
Oracle Corporation
[DISCLAIMER: opinions expressed are strictly my own] Received on Mon Feb 22 1993 - 00:17:43 CET