Re: Defragmenting tablespace.

In a previous article, pihlab_at_cbr.hhcs.gov.au () says:

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

   My observation is that Oracle7 merges adjacent free extents automatically making manual defragmentation unnecessary.

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

   I doubt it. My guess is that SMON was defragmenting the tablespace in the background and you caught it in the act between your queries on DBA_FREE_SPACE.

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

  Well It's consistent with 7.0.15 and I doubt Oracle would de-support something which makes so much sense. (Then again.. 8-)

-- 
   - Doug Harris
     Database Administrator,
     System Development Division,
     Statistics Canada             ### Standard Disclaimer Applies ###