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.

THanks in advance to all who answer.