Re: Using DROP TABLESPACE INCLUDING CONTENTS

In article <kpalm.696187890_at_athena>, kpalm_at_athena.qualcomm.com (Kent Palm) writes:
> So, I configured the database such that I now have a tablespace called
> INDEX1. The only objects allowed in this tablespace are indexes. Now, the
> question is, can I take that tablespace offline and drop it without first
> dropping all the indexes it contains? In other words, will DROP TABLESPACE
> INCLUDING CONTENTS update the data dictionary? Of course I have the scripts
> to re-create the indexes. I see this as an easy, fast way of defragmenting
> this specific tablespace. What do you think? We're running v. 6.0.33. I
> got mixed reaction at our local users group meeting concerning this one.
> As for my opinion --- I think it will work, but I haven't tried it --- yet.
>
> Kent

What kind of reaction did you get from the user group ??? This is a perfectly acceptable method for reorganizing indexes. However, I have had better luck, time wise, with dropping all the indexes first before dropping and re-creating the tablespace. Note: you will notice that the name of the tablespace that was dropped will still appear in dba_tablespaces even though you used the same name for the new tablespace. For instance, if you dropped index1 and recreated it, you would find two entries for index1 in dba_tablespaces. One of them would have a status of 'INVALID'.

Jim Brooks Received on Mon Jan 27 1992 - 18:26:25 CET