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