Submitted on 21-Jul-2000 9:17 UTC
Last updated 21-Feb-2012 19:10
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>
|
 May 4, 2007 - 12pm 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!
Followup May 4, 2007 - 1pm 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 4, 2007 - 1pm 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
Followup May 4, 2007 - 3pm UTC:
is that a locally managed one
May 4, 2007 - 4pm 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!
Followup May 8, 2007 - 9am 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 - 4pm 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
Followup February 21, 2012 - 7pm 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.
|