Tablespace Reorganization

Tablespace Reorganization

Tablespace reorganization is a process of reorganizing the contents of a tablespace to reclaim free space from the datafiles of a tablespace. When a datafiles is resized (shrink), the size of a datafiles can only be reduced till the high water mark (last allocated extent to
a segment) of the datafile. This does not guarantee that all free space have been reclaimed from within the datafile because there could be free space available in the datafile bellow the high water mark which can not be reclaimed until tablespace data is made compact after reorganizing. Tim Hall has demonstrated it with example here,

While replying to a thread (click here) in OTN forums, I tried to explain that using same tablespace for move/rebuild of segments won't help releasing all free space from within the datafile. I am copying and pasting same contents from the forums here in the following which explain how using same tablespace for rebuilding/moving segments would leave free space “islands” in the datafiles of tablespace.

While allocating extents during data load, it does allocate space in round robin fashion across the datafiles, but it does not guarantee that if you are rebuilding/moving segment in the same tablespace, it would allocate the extents from the beginning of the datafiles because extents from the beginning of the file might already have been allocated to same segment being rebuilt/moved, and can't be de-allocated until its rebuilt is completed (by allocating next available extents in a datafile). And once segment rebuild/move finishes, previously allocated extents (which might be at the beginning of the datafile(s) ), would be de-allocated leaving - free space at the beginning of the datafile(s) . Have a look at the following example where I am trying to simulate same what I just said.

1) A tablespace with one datafile with a size of 25 MB (1 MB uniform extent size allocation - so total 25 extents or 1MB can be allocated in the datafile).

2) 3 tables in the tablespace having allocated total 15 MB as follows allocated to them as follows. "E" means extent.

Table1: E5, E9, E13, E15, E17, E18

Table2: E2, E4, E8, E20

Table3: E1, E3, E6, E23, E24

3) Since allocation is uniform 1M, I am also considering free space as an "extent of 1M" for simplicity and denoting it as "F"

Extent map of datafile is as follows

E1, E2, E3, E4, E5, F6, F7, E8, E9, F10, F11, F12, E13, E14, E15, F16, E17, E18, F19, E20, F21, F22, E23, E24, F25

So ideally we should be able to reclaim 10 MB from the datafile, we can reclaim only 1 MB because last allocated extent is E24. Now we rebuild all 3 tables one by one. Rebuilding each table would allocate it new extents first and then drop the previously allocated extents.

Table1

Rebuilding Table1 will have following new extents allocated from free space

Table1: E6(F6), E7(F7), E10(F10), E11(F11), E12(F12), E16(F16)

Now it will free up the extents: F5(E5), F9(E9), F13(E13), F15(E15), F17(E17), F18(E18)

New extent map will be as follows

E1, E2, E3, E4, F5, E6, E7, E8, F9, E10, E11, E12, F13, E14, F15, E16, F17, F18, F19, E20, F21, F22, E23, E24, F25

Table2

Rebuilding Table2 will have following new extents allocated from free space

Table2: E5(F5), E9(F9), E13(F13), E15(F15)

Now it will free up the extents: F2(E2), F4(E4), F8(E8), F20(E20)

New extent map will be as follows

E1, F2, E3, F4, E5, E6, E7, F8, E9, E10, E11, E12, E13, E14, E15, E16, F17, F18, F19, F20, F21, F22, E23, E24, F25

Table3

Rebuilding Table3 will have following new extents allocated from free space

Table2: E2(F2), E4(F4), E8(F8), E17(F17), E18 (F18)

Now it will free up the extents: F1(E1), F3(E3), F6(E6), F23(E23), F24(E24)

New extent map will be as follows

F1, E2, F3, E4, E5, F6, E7, E8, E9, E10, E11, E12, E13, E14, E15, E16, E17, E18, F19, F20, F21, F22, F23, F24, F25

We can see that we still have free space at the beginning of the datafile . This was a simple example, and in more complex environment; we might have more unused space space left at the beginning of the datafile(s).

Another Example

Following is the example which shows a single table rebuilt/move in same tablespace and how it leaves free space at the beginning of the datafile after a rebuilt

SQL> create table test as select * from dba_tables;

Table created.

SQL> alter table test move tablespace testtbs;

Table altered.

SQL> select min(block_id),max(block_id) from dba_extents where tablespace_name='TESTTBS';

MIN(BLOCK_ID) MAX(BLOCK_ID)

---------------------    -------------------------

          128           224

SQL> alter table test move tablespace testtbs ;

Table altered.

SQL> select min(block_id),max(block_id) from dba_extents where tablespace_name='TESTTBS';

MIN(BLOCK_ID) MAX(BLOCK_ID)

---------------------    -------------------------

          232           328

You can see above that during rebuild of table, new extents were allocated after the last extent already occupied by the same table. In the following, when we rebuild/move of table again, now it will allocate extents again from the beginning of the datafile.

SQL> alter table test move tablespace testtbs ;

Table altered.

SQL> select min(block_id),max(block_id) from dba_extents where tablespace_name='TESTTBS';

MIN(BLOCK_ID) MAX(BLOCK_ID)

---------------------    -------------------------

          128           224