Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Anwar.

Asked: July 21, 2000 - 9:17 am UTC

Answered by: Tom Kyte - Last updated: February 21, 2012 - 7:10 pm UTC

Category: Database - Version: 7.3.2

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I've entered the following command to coalesce my 'users' tablespace but it returned without any coalescing:

alter tablespace users coalesce;

What is the reason?

and we said...

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

for example, the following query shows that in my database, my system tablespace has 2 extents right next to eachother that could be combined:

ops$tkyte@DEV8I.WORLD> 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 = 'SYSTEM'
4 and b.tablespace_name = 'SYSTEM'
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
8 /

TABLESPACE_NAME FILE_ID BLOCK_ID BLOCKS BLOCK_ID
------------------- ---------- ---------- ---------- ----------
SYSTEM 1 66568 2 66570



So I coalesce it:

ops$tkyte@DEV8I.WORLD> alter tablespace system coalesce;
Tablespace altered.

And now I see the contigous extents have been merged

ops$tkyte@DEV8I.WORLD> 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 = 'SYSTEM'
4 and b.tablespace_name = 'SYSTEM'
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
8 /

no rows selected

ops$tkyte@DEV8I.WORLD>

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

May 04, 2007 - 12:43 pm UTC

Reviewer: A reader

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!
Tom Kyte

Followup  

May 04, 2007 - 1:30 pm UTC

there is no coalesce of a tablespace in that sense.


you want to shrink space compact to "reorg" a table
and shrink space to reduce the high water mark.

May 04, 2007 - 1:15 pm UTC

Reviewer: A reader

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


Tom Kyte

Followup  

May 04, 2007 - 3:53 pm UTC

is that a locally managed one

May 04, 2007 - 4:32 pm UTC

Reviewer: A reader

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!
Tom Kyte

Followup  

May 08, 2007 - 9:53 am UTC

what "first query"

but basically, you seem to know that a locally managed tablespace doesn't need to be coalesced, so I'm wondering why you are even going down this path....

Have I understood the explanation of Coalesce correctly ?

February 20, 2012 - 4:07 pm UTC

Reviewer: Andy from UK (England)

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

Tom Kyte

Followup  

February 21, 2012 - 7:10 pm UTC

coalesce with regards to tablespaces is totally obsolete today. With locally managed tablespaces - it does not come into play. You do not need to concern yourself with it at all. It only applied to legacy dictionary managed tablespace.


A delete will NOT create a free extent. So, no, it would not happen the way you depict.