Posted by There was a time in the past where whenever anyone used the term “fragmentation” they would draw the ire of the more experienced members of the Oracle community because the usage of the term “fragmentation” was commonly associated with a mistaken belief in how the Oracle database works. This would come from countless forum posts, AskTom questions and the like where the gist of the question was “Please help me my table is fragmented. How do I reorganise it to gain more performance?” or “My index is fragmented how do I rebuild it so that it is more balanced?” etc
Thankfully it appears that the community’s knowledge has grown to the extent nowadays where we know that a fragmented index or a fragmented table generally is going to run just fine because indexes and tables are explicitly designed to work that way. Holes appear in the in the space utilisation for the tables and indexes, and these holes are plugged up by incoming data and therefore there is no maintenance requirement needed by DBAs.
But it is possible that we have swayed perhaps a little too far in this respect in that there are indeed some legitimate cases where you do want to “defragment” a object on the database in order to improve the management of the database overall. This is when you want to reduce the size of a database data file. It may be the case that the data file is getting to the OS limits of how big a file can be, or you want to reclaim some space in a data file in order to make it’s backups faster, or it might just be you have to got to a point where the data has reached a static size and you want to compact it as efficiently as possible and have no free space remaining because that space would never be utilised anyway.
One additional case I’m seeing more of is in the area of Express Edition or Database Free where there are caps on the total size of a data file. If a data file is getting toward that size you have a vested interest in reducing the size of that file in order to ensure that you can continue operations on your Oracle Database.
One of the nice things with locally managed tablespaces is that if you move or rebuild a segment then typically the database will relocate that segment into the first appropriately sized free space chunk in the data file. Thus if you do this with lots of segments at the tail of the data file, then at the end of the exercise you theoretically should have some free space now at the end of the data file and you will have filled in the holes at the start of the file therefore letting you resize the data file to a smaller size.
Here’s an example of that in action. I’ll create an 8G tablespace and load it with some tables and indexes.
SQL> create tablespace demo1 datafile 'X:\ORADATA\DB21\PDB21A\DEMO1.DBF' size 8g;
Tablespace created.
SQL> begin
2 for i in 1 .. 8 loop
3 execute immediate 'create table t'||i||' tablespace demo1 as '||
4 'select
5 rownum c1,
6 rownum c2,
7 rpad(rownum,100) c3,
8 rpad(rownum,100) c4
9 from
10 ( select 1 from dual connect by level <= 1000),
11 ( select 1 from dual connect by level <= 1000)';
12
13 execute immediate 'create index ttxa'||i||' on t'||i||' ( c3 ) tablespace demo1';
14 execute immediate 'create index ttxb'||i||' on t'||i||' ( c4 ) tablespace demo1';
15
16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> select segment_name, bytes
2 from user_segments
3 where tablespace_name = 'DEMO1';
SEGMENT_NAME BYTES
------------------------------ ----------
T1 251658240
T2 251658240
T3 251658240
T4 251658240
T5 251658240
T6 251658240
T7 251658240
T8 251658240
TTXA1 134217728
TTXB1 134217728
TTXA2 134217728
TTXB2 134217728
TTXA3 134217728
TTXB3 134217728
TTXA4 134217728
TTXB4 134217728
TTXA5 134217728
TTXB5 134217728
TTXA6 134217728
TTXB6 134217728
TTXA7 134217728
TTXB7 134217728
TTXA8 134217728
TTXB8 134217728
24 rows selected.
SQL>
SQL> select segment_name, min(block_id), max(block_id)
2 from dba_extents
3 where tablespace_name = 'DEMO1'
4 group by segment_name
5 order by 2;
SEGMENT_NAME MIN(BLOCK_ID) MAX(BLOCK_ID)
------------------------------ ------------- -------------
T1 128 29824
TTXA1 30848 46208
TTXB1 47232 62592
T2 63616 93312
TTXA2 94336 109696
TTXB2 110720 126080
T3 127104 156800
TTXA3 157824 173184
TTXB3 174208 189568
T4 190592 220288
TTXA4 221312 236672
TTXB4 237696 253056
T5 254080 283776
TTXA5 284800 300160
TTXB5 301184 316544
T6 317568 347264
TTXA6 348288 363648
TTXB6 364672 380032
T7 381056 410752
TTXA7 411776 427136
TTXB7 428160 443520
T8 444544 474240
TTXA8 475264 490624
TTXB8 491648 507008
24 rows selected.
You can see that the highest point in the datafile at which an extent commences is block 507008, which equates to (507008*8192/1024/1024) the 3960MB mark in the datafile.
Now I’ll drop some of the tables, leaving some free space gaps throughout the datafile.
SQL>
SQL> drop table t2 purge;
Table dropped.
SQL> drop table t4 purge;
Table dropped.
SQL> drop table t6 purge;
Table dropped.
Now I will loop through the remaining segments and issue an appropriate MOVE or REBUILD command.
SQL> begin
2 for i in (
3 select segment_name, segment_type
4 from user_segments
5 where tablespace_name = 'DEMO1'
6 )
7 loop
8 if i.segment_type = 'TABLE' then
9 execute immediate 'alter table '||i.segment_name||' move';
10 elsif i.segment_type = 'INDEX' then
11 execute immediate 'alter index '||i.segment_name||' rebuild';
12 end if;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> select segment_name, bytes
2 from user_segments
3 where tablespace_name = 'DEMO1';
SEGMENT_NAME BYTES
------------------------------ ----------
T1 251658240
T3 251658240
T5 251658240
T7 251658240
T8 251658240
TTXA1 134217728
TTXB1 134217728
TTXA3 134217728
TTXB3 134217728
TTXA5 134217728
TTXB5 134217728
TTXA7 134217728
TTXB7 134217728
TTXA8 134217728
TTXB8 134217728
15 rows selected.
SQL>
SQL> select segment_name, min(block_id), max(block_id)
2 from dba_extents
3 where tablespace_name = 'DEMO1'
4 group by segment_name
5 order by 2;
SEGMENT_NAME MIN(BLOCK_ID) MAX(BLOCK_ID)
------------------------------ ------------- -------------
T3 128 29824
T5 30848 60544
T7 61568 121984
T1 63616 93312
T8 123008 152704
TTXA1 153728 169088
TTXB1 170112 185472
TTXA3 186496 201856
TTXB3 202880 218240
TTXA5 219264 234624
TTXB5 235648 251008
TTXA7 252032 267392
TTXB7 268416 283776
TTXA8 284800 300160
TTXB8 301184 316544
15 rows selected.
Now when I look at the distribution of segments, the last extent begins at block 316544 (2470MB) so I can reduce the datafile size down to 2500MB to reclaim that space. (I’m assuming there that I don’t plan on any additional growth in these segments, because otherwise reclaiming that space is just a waste of effort because the file will need to grow again shortly anyway).
SQL> alter database datafile 'X:\ORADATA\DB21\PDB21A\DEMO1.DBF' resize 2500m;
Database altered.
This all looks very impressive but this demo is a little artificial in that I created my tables and indexes in a simple order and populated them in a common sequence. This means that each table grew in a predicted fashion to a common size so that when I dropped a few of those tables I had perfectly sized holes in my data file freed up in which to relocate the existing segments. In reality this is often not the case. Even though locally managed tablespaces will guarantee a consistent set of extent sizes obviously each table in your database is not identical in size some tables are huge some tables are small and therefore as you drop them you will end up with a myriad of different sized free space chunks in your data files. For this reason when you do a reorganisation of tables and indexes that remain it can sometimes be disheartening to find that the database has not perfectly relocated segments toward the front of the file. And all it takes is a single extent to be left our the “boundary” of a file to prevent you from re-sizing it.
Thus rather than relying on the database to correctly fill the empty holes in a file in the way that I would like it to I’ll often adopt a different strategy which is just create a brand new tablespace from scratch with auto extend on. Now I adjust my routine to move or rebuild segments from the original table space into the new one and the new one will grow from scratch only up to the required size to hold the segments you have moved into it.
SQL>
SQL>
SQL> create tablespace demo1 datafile 'X:\ORADATA\DB21\PDB21A\DEMO1.DBF' size 8g;
Tablespace created.
SQL> create tablespace demo2 datafile 'X:\ORADATA\DB21\PDB21A\DEMO2.DBF' size 1g
2 autoextend on next 256m;
Tablespace created.
SQL>
SQL> begin
2 for i in 1 .. 8 loop
3 execute immediate 'create table t'||i||' tablespace demo1 as '||
4 'select
5 rownum c1,
6 rownum c2,
7 rpad(rownum,100) c3,
8 rpad(rownum,100) c4
9 from
10 ( select 1 from dual connect by level <= 1000),
11 ( select 1 from dual connect by level <= 1000)';
12
13 execute immediate 'create index ttxa'||i||' on t'||i||' ( c3 ) tablespace demo1';
14 execute immediate 'create index ttxb'||i||' on t'||i||' ( c4 ) tablespace demo1';
15
16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> select segment_name, bytes
2 from user_segments
3 where tablespace_name = 'DEMO1';
SEGMENT_NAME BYTES
------------------------------ ----------
T1 251658240
T2 251658240
T3 251658240
T4 251658240
T5 251658240
T6 251658240
T7 251658240
T8 251658240
TTXA1 134217728
TTXB1 134217728
TTXA2 134217728
TTXB2 134217728
TTXA3 134217728
TTXB3 134217728
TTXA4 134217728
TTXB4 134217728
TTXA5 134217728
TTXB5 134217728
TTXA6 134217728
TTXB6 134217728
TTXA7 134217728
TTXB7 134217728
TTXA8 134217728
TTXB8 134217728
24 rows selected.
SQL>
SQL> select segment_name, min(block_id), max(block_id)
2 from dba_extents
3 where tablespace_name = 'DEMO1'
4 group by segment_name
5 order by 2;
SEGMENT_NAME MIN(BLOCK_ID) MAX(BLOCK_ID)
------------------------------ ------------- -------------
T1 128 29824
TTXA1 30848 46208
TTXB1 47232 62592
T2 63616 93312
TTXA2 94336 109696
TTXB2 110720 126080
T3 127104 156800
TTXA3 157824 173184
TTXB3 174208 189568
T4 190592 220288
TTXA4 221312 236672
TTXB4 237696 253056
T5 254080 283776
TTXA5 284800 300160
TTXB5 301184 316544
T6 317568 347264
TTXA6 348288 363648
TTXB6 364672 380032
T7 381056 410752
TTXA7 411776 427136
TTXB7 428160 443520
T8 444544 474240
TTXA8 475264 490624
TTXB8 491648 507008
24 rows selected.
SQL>
SQL> drop table t2 purge;
Table dropped.
SQL> drop table t4 purge;
Table dropped.
SQL> drop table t6 purge;
Table dropped.
SQL>
SQL> begin
2 for i in (
3 select segment_name, segment_type
4 from user_segments
5 where tablespace_name = 'DEMO1'
6 )
7 loop
8 if i.segment_type = 'TABLE' then
9 execute immediate 'alter table '||i.segment_name||' move tablespace demo2';
10 elsif i.segment_type = 'INDEX' then
11 execute immediate 'alter index '||i.segment_name||' rebuild tablespace demo2';
12 end if;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> select segment_name, bytes
2 from user_segments
3 where tablespace_name = 'DEMO2';
SEGMENT_NAME BYTES
------------------------------ ----------
T1 251658240
T3 251658240
T5 251658240
T7 251658240
T8 251658240
TTXA1 134217728
TTXB1 134217728
TTXA3 134217728
TTXB3 134217728
TTXA5 134217728
TTXB5 134217728
TTXA7 134217728
TTXB7 134217728
TTXA8 134217728
TTXB8 134217728
15 rows selected.
SQL>
SQL> select segment_name, min(block_id), max(block_id)
2 from dba_extents
3 where tablespace_name = 'DEMO2'
4 group by segment_name
5 order by 2;
SEGMENT_NAME MIN(BLOCK_ID) MAX(BLOCK_ID)
------------------------------ ------------- -------------
T1 128 29824
T3 30848 60544
T5 61568 91264
T7 92288 121984
T8 123008 152704
TTXA1 153728 169088
TTXB1 170112 185472
TTXA3 186496 201856
TTXB3 202880 218240
TTXA5 219264 234624
TTXB5 235648 251008
TTXA7 252032 267392
TTXB7 268416 283776
TTXA8 284800 300160
TTXB8 301184 316544
15 rows selected.
SQL> select tablespace_name, file_name, lpad(round(bytes/1024/1024)||'m',10) bytes
2 from dba_data_files
3 where tablespace_name like 'DEMO%'
4 order by 1,2;
TABLESPACE_NAME FILE_NAME BYTES
------------------------ ------------------------------------------------------ ------------
DEMO1 X:\ORADATA\DB21\PDB21A\DEMO1.DBF 8192m
DEMO2 X:\ORADATA\DB21\PDB21A\DEMO2.DBF 2560m
The DEMO2 tablespace has grown only to the size needed to hold the objects, and I could drop my (now empty) original tablespace DEMO1.
Please be aware that this is not an operation to be undertaken lightly. Just like a DataP{ump unload and reload totally changes the organisation of your tables and index data, shifting data around within a data file is in effect a similar exercise. It may have an impact on your execution plans and therefore an impact on the performance of queries access these objects. But if you need to reclaim that space, my general advice would be create a new table space, move things into it, and then ensure your optimizer statistics are up to date and monitor for any possible regressions.
Learning is not a spectator sport 
That’s true!
A few years back we used to have maintenance windows to defragment tables and rebuild indexes. This was considered critical by most people. Fragmentation was something to be afraid of.
Within time, this started to phase out, with more and more DBAs realizing this is not a good practice and definitely not worth the effort.
I still see cases as you shown for tablespace reorganization and rare specific performance issues, such as a massive purge.
As of today people really accepted that this is a natural thing, in the sense that even if there was a small performance gain, the same fragmentation would build up quickly due to the objects’ usage pattern.