Re: Using DROP TABLESPACE INCLUDING CONTENTS

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

Yes it should work BUT I have had problems with the INCLUDING CONTENTS option under V6.0.30 when a "non-active" rollback segment was in the tablespace. The end result being an unrecoverable database with massive block corruption. Luckily we had a recent backup and the loss of a dya of data on this database was not crucial (simply reentered by users). I tend to shy away from that option these days.

I believe that this bug has been fixed in a later release (possibly 33).

At the SYSTEM level we have 3 tablespaces

       1  SYSTEM    Which contains the data dictionary and other objects
                    belonging to the SYSTEM and SYS users.

       2  SYS_TS2   Which contains all the ROLLBACK segments for the database
                    and allows us to easily defragment this storage when 
                    needed.

       3  SYS_TS3   The temporary tablespace. where all temporary objects built
                    to complete a query (eg sorts etc) are located.  You point
                    all users except SYS and SYSTEM here.  Again this makes it
                    very easy to defragment the space and you don't have to
                    worry about the SYSTEM tablespace defragmenting all over 
                    the place.

In our production databases we create a minimum of 2 tablespaces for each application. One to hold the tables and the second to hold the indexes. If the application is really large then we split the objects further into other tablespaces and can easily resize and defragment tablespaces which are of a manageable size. This also allows us to spread the I/O intensive tablespaces among our disk drives to reduce bottlenecks.

Bruce... pihlab_at_hhcs.gov.au Received on Fri Jan 24 1992 - 16:25:27 CET