If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

tablespace COALESCE or shrinking datafile

We have quarterly and yearly processes that deletes, archives millions of rows
from different tables. To make room on the file system I would either like
to shrink back down the datafiles (because auto extend is on) or coalesce them.

Is there a rule of thumb I should follow when shrinking or coalescing? Can somebody provide provide a query that would show me the free and used
extents in a TS, and when I should coalesce or a query to show me how much free space is in a datafile so I can shrink it.

Oracle will not allow you to re-size the data file (10g and above) if it has data in it. I didn't remember any query that tells you how much free size is available in each DATA FILE. If you have past data file growth data with you then you can re-size up to that point in a smaller chunks like 5GB one time till Oracle warns you.

Thanks,

Vijay Tummala

Try hard to get what you like OR you will be forced to like what you get.

This view shows that there is adjacent free space in tabsp_4 (for example, blocks starting with BLOCK_IDs 2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the ALTER TABLESPACE statement shown previously, the results of this query would read: