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.

THanks in advance to all who answer.

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,

FYI


http://download.oracle.com/docs/cd/B...21/tspaces.htm



Monitoring Free Space
You can use the following views for monitoring free space in a tablespace:

DBA_FREE_SPACE
DBA_FREE_SPACE_COALESCED
The following statement displays the free space in tablespace tabsp_4:

SELECT BLOCK_ID, BYTES, BLOCKS
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'TABSP_4'
ORDER BY BLOCK_ID;

BLOCK_ID BYTES BLOCKS
---------- ---------- ----------
2 16384 2
4 16384 2
6 81920 10
16 16384 2
27 16384 2
29 16384 2
31 16384 2
33 16384 2
35 16384 2
37 16384 2
39 8192 1
40 8192 1
41 196608 24
13 rows selected.


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:

BLOCK_ID BYTES BLOCKS
---------- ---------- ----------
2 131072 16
27 311296 38
2 rows selected.


The DBA_FREE_SPACE_COALESCED view displays statistics for coalescing activity. It is also useful in determining if you need to coalesce space.

what is your oracle version?

Thanks,

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
Running on an AIX 5.3 box

are you using locally managed TS with segment space management auto or dictionary managed TS?

Thanks,

Quote:

---

Originally Posted by vnktummala

are you using locally managed TS with segment space management auto or dictionary managed TS?

Thanks,

---

locally managed TS with segment space management.

Here is a typical example of how I create tablespaces. Depending on what tables are going in the TS most of the time I increase the maxsize, but try to stay away from "unlimited" option.

CREATE TABLESPACE "xxx"
DATAFILE '/oracle/data/pri/xxx01.dbf' SIZE 50M
autoextend on NEXT 50M maxsize 200M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;