Defragmenting tablespace.

From: <pihlab_at_cbr.hhcs.gov.au>
Date: 11 Jan 94 11:22:19 +1000
Message-ID: <1994Jan11.112219.1_at_cbr.hhcs.gov.au>


Defragmenting tablespace.

Something strange happened to me at work this morning ...

I was cleaning out our Acceptance Testing database to load in a new set of test data.

I created a script to drop all objects belonging to the test account and ran it successfully.

Then I did a

   SQL> select * from dba_free_space;

to see if I needed to defragment any tablespaces. We have about 16 tablespaces for this test application. The report showed one tablespace in three fragments but to be sure I ran the script again to order and show only the ones I was interested in. I ran the script

   SQL> select * from dba_free_space

>> where tablespace_name like 'CRS%'
>> order by tablespace_name;

and the report showed NO TABLESPACES FRAGMENTED.

I ran the original simple query again just to be sure but it showed the change as well.

It would appear that Oracle7 (well 7.0.13.0 at least) actually merges adjacent free space chunks when you use the "ORDER BY" and/or "WHERE --- LIKE" clause. My database is now defragmented so I can't easily test which caused this to happen but I think its the "ORDER BY".

Is this a documented feature? Is it consistent with later releases?

Could somebody check which clause is causing the defragmentation to occur and if it works on later releases please.

I had originally planned to build a utility to merge adjacent free space in Oracle7 databases but if this works then I just need to automate the running of this script.

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of                                      * 
*          Health, Housing, Local Government & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************
Received on Tue Jan 11 1994 - 02:22:19 CET

Original text of this message