tablespace COALESCE or shrinking datafile

[BeefStu]

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.

[vnktummala]

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,

[BeefStu]

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.

[vnktummala]

what is your oracle version?

Thanks,

[BeefStu]

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

[vnktummala]

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

Thanks,

[BeefStu]

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;