Re: Defragmenting tablespace.
Date: Tue, 11 Jan 1994 22:50:40 GMT
Message-ID: <CJHM4H.IqB_at_freenet.carleton.ca>
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 ###Received on Tue Jan 11 1994 - 23:50:40 CET