So, coalesce tablespace is similar to defregmentation, it doesn't drop the high water mark and release free space, is that right?

SQL> ALTER TABLE SCOTT.EMP SHRINK SPACE;
Does the above command do both coalesce and move downward HWM?

Thanks!

Also I run the above script and coalesce and nothing happens, can you please tell me what went wrong?

SQL> ed
Wrote file afiedt.buf

1 select a.tablespace_name, a.file_id, a.block_id, a.blocks, b.block_id
2 from dba_free_space a, dba_free_space b
3 where a.tablespace_name = 'TEST2'
4 and b.tablespace_name = 'TEST2'
5 and a.tablespace_name = b.tablespace_name
6 and a.file_id = b.file_id
7* and a.block_id+a.blocks = b.block_id
SQL> /

TABLESPACE_NAME FILE_ID BLOCK_ID BLOCKS BLOCK_ID
------------------------------ ---------- ---------- ---------- ----------
TEST2 9 249 8 257
TEST2 9 25 8 33
TEST2 9 33 8 41
TEST2 9 41 8 49
TEST2 9 49 8 57
TEST2 9 57 8 65
TEST2 9 65 8 73
TEST2 9 121 8 129
TEST2 9 129 8 137
TEST2 9 137 8 145
TEST2 9 145 8 153
TEST2 9 153 8 161
TEST2 9 17 8 25

13 rows selected.

SQL> alter tablespace TEST2 coalesce;

Tablespace altered.

1 select a.tablespace_name, a.file_id, a.block_id, a.blocks, b.block_id
2 from dba_free_space a, dba_free_space b
3 where a.tablespace_name = 'TEST2'
4 and b.tablespace_name = 'TEST2'
5 and a.tablespace_name = b.tablespace_name
6 and a.file_id = b.file_id
7* and a.block_id+a.blocks = b.block_id
SQL> /

TABLESPACE_NAME FILE_ID BLOCK_ID BLOCKS BLOCK_ID
------------------------------ ---------- ---------- ---------- ----------
TEST2 9 249 8 257
TEST2 9 25 8 33
TEST2 9 33 8 41
TEST2 9 41 8 49
TEST2 9 49 8 57
TEST2 9 57 8 65
TEST2 9 65 8 73
TEST2 9 121 8 129
TEST2 9 129 8 137
TEST2 9 137 8 145
TEST2 9 145 8 153
TEST2 9 153 8 161
TEST2 9 17 8 25

Yes, it is locally managed tablespace.

And for Locally Managed Tablespaces, it eliminate the need to periodically coalesece free space (automatically tracks adjacent free space).

Then why the first query shows tablespace that can be coalesced?

Thanks!

>>> Coalesce simply takes contigous free extents and makes them into a single bigger free extent.

>>> It is not a reorganization tool.
>>> It is a command to take any free extents that are right next to some other free extent and makes one bigger free extent out of them.
>>> It is useful after a drop command when you want to create another object right away.
>>> SMON will normally perform this coalescing in the background but if you need it to happen "right now", the coalesce command will do it.

Therefor ...

D = Data
N = New Object data
0 = Free extent
[0] = Large free extent

----------- Extents -------------
000000000011111111111222222222223
123456789012345678901234567890123
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
001 DD0DDD0DDDDDDDDDDDDDDDDDD0DDDD0DD

002 DELETE FROM T1 WHERE x=y

----------- Extents -------------
000000000011111111111222222222223
123456789012345678901234567890123
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
003 000D0000DDDD00000DDD000DD0DDDDDDD

004 ALTER TABLESPACE [TABLESPACE_NAME] COALESCE
----------- Extents -------
0--00---0---0----0--0--0-11
1--23---4---5----6--7--8-90
v--vv---v---v----v--v--v-vv
005 [0]D[0 ]DDDD[0 ]DDD[0]DD0DDDDDDD

006 CREATE TABLE t2 AS SELECT * FROM t1;
----------- Extents -------
0--00---0---0----0--0--0-11
1--23---4---5----6--7--8-90
v--vv---v---v----v--v--v-vv
007 [N]D[N ]DDDD[ N ]DDD[N]DDNDDDDDDD