Re: Coalesce problem

In article <Pine.GSO.4.21.0010231825002.24382-100000_at_loisto.uwasa.fi>,   kt <h78816_at_loisto.uwasa.fi> wrote:
> Hi all!
>
> I'm quit new with the Oracle databases and would like to know what
> is the best way to find out that fragmentation of space is
> high (contiguous space on your disk appears as non-contiguous)?
>
> If I execute ALTER TABLESPACE <dbspace> COALESCE, what's happening to
> extents?
>
> Do I get more larger contiguous extents on a per tablespace basis by
> using that command?
>
> Or is there another ways to reorganisize tablespaces, except exp-imp?
>
> Thank's in forward.
>
> KT
>
>

First, 'ALTER TABLESPACE ... COALESCE;' only affects the free space in the tablespace, coalescing the contiguous blocks of free space into one uninterrupted piece, as long as the pieces are contiguous (next to each other) and are in the same datafile (as indicated by the File ID). To clarify a bit, let us say you have a tablespace named TEST and when you examine the tablespace you find the following:

                                              File
OWNER        OBJECT                             Id   BLOCK_ID   BLOCKS
------------ -------------------------------- ---- ---------- --------
free space                                       8          2     5120
free space                                       8       5122     5120
free space                                       8      10242    53631

Three contiguous blocks of free space. You know that the blocks are contiguous because the block id of the next block equals the starting block id plus the blocks in the fragment -- block id 5122 = block id 2 + 5120 blocks. Likewise adding the blocks to the block id for the second entry gives the block id for the third fragment (5122 + 5120 = 10242). This works because all three fragments are in the same file. 'ALTER TABLESPACE TEST COALESCE;' will make these three fragments one continuous (and contiguous) piece:

                                              File
OWNER        OBJECT                             Id   BLOCK_ID   BLOCKS
------------ -------------------------------- ---- ---------- --------
free space                                       8          2    63871

There is now only one segment of free space in the tablespace.

'ALTER TABLESPACE ... COALESCE' does nothing to extents occupied by valid objects; it ONLY affects the free space fragments, and only if those fragments are in the same datafile and next to each other. If you had the following:

                                              File
OWNER        OBJECT                             Id   BLOCK_ID   BLOCKS
------------ -------------------------------- ---- ---------- --------
free space                                       8          2     5120
ORA_USER     TEST_TBL                            8       5122     5120
free space                                       8      10242    53631

and you executed 'ALTER TABLESPACE TEST COALESCE;' you would see absolutely no difference afterwards:

                                              File
OWNER        OBJECT                             Id   BLOCK_ID   BLOCKS
------------ -------------------------------- ---- ---------- --------
free space                                       8          2     5120
ORA_USER     TEST_TBL                            8       5122     5120
free space                                       8      10242    53631

Since there was no free space to coalesce nothing was changed.

Since extents are not changed what you DO get is larger contiguous portions of free space. If your tablespaces are locally managed with uniform extents free space fragmentation will not pose a problem since ORACLE will have no trouble using the free space pieces left behind when objects are dropped and 'ALTER TABLESPACE ... COALESCE' will not be necessary. If, on the other hand, you are not using uniform extents, either through locally managed tablespaces or through explicit table design (making certain that all extents for all tables are uniform throughout a tablespace by properly setting the tablespace and table storage parameters), the 'ALTER TABLESPACE ... COALESCE' will be necessary to provide sufficiently sized free space fragments for ORACLE to use.

'ALTER TABLESPACE ... COALESCE' does NOT reorganize the objects in tablespaces, just the free space fragments. To reorganize a tablespace you will need to use exp/imp along with planned creation of the tables, i.e., create the tables in an order that will ensure that certain tables (extremely large, dynamic tables) will have dedicated datafiles and that smaller, less volatile tables will be grouped together so that the extents for each table are contiguous (at least at the time of creation).

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.