Reorgs of tablespaces

From: Steven Monaghan <MonaghaS_at_mscdirect.com>
Date: Tue, 3 Oct 2000 11:04:13 -0400
Message-Id: <10638.118379@fatcity.com>

I took over a database a few months ago that had used some horrible default extent sizes (initial 40K next 40K pctincrease 50). So, by tablespaces are pretty mangled. I have since decided on an extent sizing scheme recommended in the Oracle 8 Advanced Administration and Tuning by Loney et al. The scheme is to have a minimum extent size which is block size x multiblock read count. And all other extent sizes a multiple.

So my list of extent sizes is: 128K, 256K, 512K, 1024K, ..., 65536K.

Now, I have set all of my pctincreases to 0 and my next extents to reasonable values. But now, my tablespaces are getting pretty fragmented. Every time I rebuilt an index (Which many needed), I left holes behind. And I am having the same problem with the tables. I want to try and fix this with no downtime on version 8.0.5 (cannot alter table move!).

For indexes my plan is pretty straight forward:

  1 - Create an interim tablespace to hold the objects
  2 - Rebuild all indexes for a tablespace into the interim tablespace
  3 - Rebuild the indexes back into the correct tablespace with appropriate

storage.
4 - Repeat for all index tablespaces
5 - Drop the interim tablespace and delete the datafile. This will result in only extents that are a multiple of 128K and can always be re-used.

For tables it is more complicated to do without downtime. I would think I would need to create copies of tables, use views to direct the users to the correct tables and maybe triggers to keep the tables in synch, but I am not sure exactly how. Has anyone ever done this before while the database is in use? It will be an off time, so performance of the app during the reorgs isn't a huge concern, but the data must be available. Any suggestions?

Steve

Steven Monaghan
Oracle DBA
MSC Industrial Direct Co., Inc.
Melville, NY
MonaghaS_at_mscdirect.com

Received on Tue Oct 03 2000 - 10:04:13 CDT