V7 Create Table Question, Please Help.

From: Robert DuMoulin <rdumouli_at_lanier.com>
Date: 1995/04/04
Message-ID: <D6IJt6.363_at_lanier.com>#1/1


Oracle Nerds Unite! (and please help me).

I have created an application which cleans up free blocks in tablespaces by creating then dropping tables with initial extents equal to the size of contiguous blocks of free space. This is run on our V6 production databases daily to eliminate some fragmentation caused by dropping and creating temp tables. (For those of you who don't know it, Oracle CREATE TABLE command is smart enough to notice two adjacent blank free space holes as one space, but not when looking for a next extent. This causes further fragmentation and can make a tablespace appear to run out of room when it really is not.)

Anyway, this works fine in V6. If I ask for a table with initial size of 20480 (10 blocks at 2048/block), I get exactly that. In V7, the same request results in an attempt to allocate more. I assume this is for V7 overhead, but can't find anywhere that describes how much overhead is needed. The trials seem to indicate that the percentage varies to the size of the initial extent requested. I know that if I request .91 the size of the free space that it works for all of my test cases. Even though this can help in some cases, it really doesn't help overall because it creates a lot more smaller holes (the space left over after the temp table creation) which defeats my purpose. Is there a utility out there which does this for V7? I know that the TRUNCATE command will help avoid this situation once we get to V7, but it won't clean up the tablespace.

BTW, working with 7.0.16 and 6.0.37 on Sequent

                          Many Many thanks in advance.
Received on Tue Apr 04 1995 - 00:00:00 CEST

Original text of this message