Sometimes you can make things worse not better. Despite the clinical messing around listed in this article, if you scale up it shows that where you have large amounts of data, it’s not always best to assume you can improve things simply by re-organising.
Of course, there will be times when it does benefit.
Many clients ask us to do this first when they feel pressure on storage. Not always the best answer.
SQL> select segment_name, segment_type, extent_id, block_id, block_id+blocks-1 endblock from dba_extents where tablespace_name = ‘F’ order by block_id;no rows selected
This is my seg.sql file. Where you see @seg, that’s the query running.
Ok, so we have no segments, and our user has no segments. We default to tablespace F, so all our queries can run on user_segments.
SQL> create table tst(id number, c varchar2(3999) default lpad(‘a’,3990,’a')) tablespace f;Table created.
SQL> create index ind_one on tst(c) tablespace f;
Index created.
SQL> create index ind_two on tst(id) tablespace f;
Index created.
So, what did oracle create for us ?
SQL> select segment_name, segment_type, extent_id, block_id, block_id+blocks-1 endblock from dba_extents where tablespace_name = ‘F’ order by block_id;no rows selected
This is because 11g does not create the segment until data has been added.
SQL> insert into tst(id) values (1);1 row created.
SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID ENDBLOCK
—————————— —————— ———- ———- ———-
TST TABLE 0 128 135
IND_ONE INDEX 0 136 143
IND_TWO INDEX 0 144 151
Add a little data.
SQL> begin2 for i in 1 .. 4 loop
3 insert into tst(id) values (i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed. SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID ENDBLOCK
——————– —————— ———- ———- ———-
TST TABLE 0 128 135
IND_TWO INDEX 0 136 143
IND_ONE INDEX 0 144 151
IND_ONE INDEX 1 152 159
Now we can see the ind_one, the first index has overflowed, and this occured before the table extended another extent. What if we roll back this transaction ?
SQL> rollback;Rollback complete.
SQL> @seg
order by block_id;
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID ENDBLOCK
——————– —————— ———- ———- ———-
TST TABLE 0 128 135
IND_TWO INDEX 0 136 143
IND_ONE INDEX 0 144 151
IND_ONE INDEX 1 152 159
The storage, once allocated remains there.
SQL> create sequence dumseq start with 5;Sequence created.
Repeatedly insert data until the next extent is allocated.
SQL> declare2 cnt number;
3 ccnt number;
4 begin
5 select count(*) into cnt from user_extents;
6 ccnt:=cnt;
7 while (cnt = ccnt) loop
8 insert into tst(id) values (dumseq.nextval);
9 select count(*) into ccnt from user_extents;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID ENDBLOCK
——————– —————— ———- ———- ———-
TST TABLE 0 128 135
IND_TWO INDEX 0 136 143
IND_ONE INDEX 0 144 151
IND_ONE INDEX 1 152 159
TST TABLE 1 160 167
Table TST has extended. Let’s place something else in there, table tst2.
SQL> create table tst2 as select rownum id, lpad(‘a’,3990,’a') c from dual connect by level < 10000;Table created.
SQL> @seg
I’ll break this down here to show only the pertinant rows. The start of the table remains the same.
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK——————– —————— ———- ———- ———- ———-
TST TABLE 0 128 8 135
IND_TWO INDEX 0 136 8 143
IND_ONE INDEX 0 144 8 151
IND_ONE INDEX 1 152 8 159
TST TABLE 1 160 8 167
TST2 TABLE 0 168 8 175
There’s the start of the TST2 segments.
This increments from segment 0 to 15, at which point the blocks allocated change from 8 to 128.
TST2 TABLE 15 288 8 295TST2 TABLE 16 384 128 511
At towards the end we see the size raise from 128K to 1M.
TST2 TABLE 78 8320 128 8447TST2 TABLE 79 8448 1024 9471
So the segment size is growing.
Now, to clear the problem, let’s remove table tst from the datafile.
SQL> drop table tst purge;Table dropped.
So our segments will start from block 168, as expected.
SQL> @segSEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
——————– —————— ———- ———- ———- ———-
TST2 TABLE 0 168 8 175
TST2 TABLE 1 176 8 183
Now, we know TST went into the recyclebin, so we should check in there.
SQL> select * from user_recyclebin;OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME
—————————— ——————————– ——— ————————- ——————————
CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT
——————- ——————- ———- ——————————– — — ———- ———– ————
SPACE
———-
BIN$Uv/Wu9DmRKelLSEsJfHKAA==$0 IND_ONE DROP INDEX F
2011-04-01:12:28:04 2011-04-01:12:30:01 11046094 NO YES 88364 88364 88365
16
BIN$OywpZjUISzyvhXfGTMFFZg==$0 IND_TWO DROP INDEX F
2011-04-01:12:28:08 2011-04-01:12:30:01 11046098 NO YES 88364 88364 88366
8
BIN$NX/1EyAbSwCLZPRwy5bSEA==$0 TST DROP TABLE F
2011-04-01:12:27:59 2011-04-01:12:30:01 11046102 YES YES 88364 88364 88364
16
SQL> create table tst3(id number, c varchar2(3999) default lpad(‘a’,3990,’a')) tablespace f;
Table created.
SQL> create index ind_three on tst3(c) tablespace f;
Index created.
SQL> create index ind_four on tst3(id) tablespace f;
Index created.
SQL> insert into tst3(id) values (1);
1 row created.
Now check the segments query again and you will see tst3 going into the middle of the datafile. I’ve shortened the output again.
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK——————– —————— ———- ———- ———- ———-
TST2 TABLE 15 288 8 295
TST3 TABLE 0 296 8 303
IND_THREE INDEX 0 304 8 311
IND_FOUR INDEX 0 312 8 319
TST2 TABLE 16 384 128 511
Why ? When the segment size of TST2 broke from 8 to 128, oracle had to find a multiple block of 128 in which to place the new segment. This leaves space from block 296 onwards as empty. Indeed you can see block 320 to 383 are not in use. We’d have to use this up to keep going. Let’s run our segment extend block again.
declarecnt number;
ccnt number;
begin
select count(*) into cnt from user_extents;
ccnt:=cnt;
while (cnt = ccnt) loop
insert into tst3(id) values (dumseq.nextval);
select count(*) into ccnt from user_extents;
end loop;
end;
/
Keep running this until we use new blocks at the end of the listing.
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK——————– —————— ———- ———- ———- ———-
TST2 TABLE 80 9472 1024 10495
IND_THREE INDEX 6 10496 8 10503
TST3 TABLE 4 10504 8 10511
IND_THREE INDEX 7 10512 8 10519
IND_THREE INDEX 8 10520 8 10527
96 rows selected.
Now we have reached the end of the allocated listing and started to use space at the end, still in 8k block sizes. Our end block is at 10527.
Time to ask a couple of questions.
Why did the first blocks not get re-used for table TST3 ?
What do you think re-organising the TST2 table will achieve now ?
Firstly, the blocks for table TST3 are still in use. The object has been dropped into the recyclebin, but the data remains on disk. Check the start of the output of our segments query.
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK——————– —————— ———- ———- ———- ———-
TST2 TABLE 0 168 8 175
TST2 TABLE 1 176 8 183
Now flashback the table and check again.
SQL> flashback table tst to before drop;
Flashback complete.
And check the segments.
SQL> @segSEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
——————– —————— ———- ———- ———- ———-
TST TABLE 0 128 8 135
BIN$Uv/Wu9DmRKelLSEs INDEX 0 136 8 143
JfHKAA==$0
BIN$OywpZjUISzyvhXfG INDEX 0 144 8 151
TMFFZg==$0
BIN$Uv/Wu9DmRKelLSEs INDEX 1 152 8 159
JfHKAA==$0
TST TABLE 1 160 8 167
TST2 TABLE 0 168 8 175
Hmm, interesting. The segments are back in the original place and the indexs are there too. But they are called something else. These we have to rename.
SQL> alter index “BIN$Uv/Wu9DmRKelLSEsJfHKAA==$0″ rename to ind_one;Index altered.
SQL> alter index “BIN$OywpZjUISzyvhXfGTMFFZg==$0″ rename to ind_two;
Index altered.
SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
——————– —————— ———- ———- ———- ———-
TST TABLE 0 128 8 135
IND_ONE INDEX 0 136 8 143
IND_TWO INDEX 0 144 8 151
IND_ONE INDEX 1 152 8 159
TST TABLE 1 160 8 167
TST2 TABLE 0 168 8 175
so, the space was not released and reused. Drop again using PURGE to clear the space as available on disk, and extend the tst3 table segments.
SQL> drop table tst purge;Table dropped.
This is bearing in mind that the table tst3 will keep extending at 8k blocks until block 16.
SQL> @ext_tst3PL/SQL procedure successfully completed.
SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
——————– —————— ———- ———- ———- ———-
IND_THREE INDEX 9 128 8 135
TST2 TABLE 0 168 8 175
And you can see segment IND_THREE has taken some of the space previously allocated to table TST. So, let’s see how fragmented this datafile really is.
What we need is a count of all blocks which are used or not. Let’s do it this way. Note the – 128 to remove the datafile header storage.
select maxblock-sblocks-128 from (select sum(blocks) sblocks from dba_extents where tablespace_name = ‘F’) a,(select max(block_id+blocks) maxblock from dba_extents where tablespace_name = ‘F’) b;
MAXBLOCK-SBLOCKS-128
——————–
32
So, from the base of the file to the last occupied block, we have 32 free blocks not in use. That’s not bad. Let’s now try to re-organise the tablespace with the supposed aim of saving space.
Let’s move table TST2 first. This is the very large table by comparison.
SQL> alter table tst2 move tablespace f;Table altered.
SQL> select maxblock-sblocks-128 from (select sum(blocks) sblocks from dba_extents where tablespace_name = ‘F’) a,
2 (select max(block_id+blocks) maxblock from dba_extents where tablespace_name = ‘F’) b;
MAXBLOCK-SBLOCKS-128
——————–
10360
Ouch. That’s made things much much worse in regard to free space in the datafile. Note that we cannot undo this move, be we can re-perform it again.
SQL> alter table tst2 move tablespace f;Table altered.
SQL> select maxblock-sblocks-128 from (select sum(blocks) sblocks from dba_extents where tablespace_name = ‘F’) a,
2 (select max(block_id+blocks) maxblock from dba_extents where tablespace_name = ‘F’) b;
MAXBLOCK-SBLOCKS-128
——————–
32
Why did it do that ? It’s because of the distribution of the data in the datafile which mean that to rebuild the data, oracle had to go to the end of the datafile, write the table there, drop the original table and change the storage to point to the copied data. Because tst2 already occupied the space up to the highest extent, it created new extents to store the copied table in.
Here it is before the move. Where an object occupied contiguous blocks, I’ve just listed the first and last entries.
SQL> @segSEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
—————————— —————— ———- ———- ———- ———-
IND_THREE INDEX 9 128 8 135
TST2 TABLE 0 168 8 175
……
TST2 TABLE 15 288 8 295
TST3 TABLE 0 296 8 303
IND_THREE INDEX 0 304 8 311
IND_FOUR INDEX 0 312 8 319
IND_THREE INDEX 1 320 8 327
TST3 TABLE 1 328 8 335
IND_THREE INDEX 2 336 8 343
IND_THREE INDEX 3 344 8 351
TST3 TABLE 2 352 8 359
IND_THREE INDEX 4 360 8 367
TST3 TABLE 3 368 8 375
IND_THREE INDEX 5 376 8 383
TST2 TABLE 16 384 128 511
…….
TST2 TABLE 80 9472 1024 10495
IND_THREE INDEX 6 10496 8 10503
TST3 TABLE 4 10504 8 10511
IND_THREE INDEX 7 10512 8 10519
IND_THREE INDEX 8 10520 8 10527
TST3 TABLE 5 10528 8 10535
SQL> alter table tst2 move tablespace f;
Table altered. SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
—————————— —————— ———- ———- ———- ———-
IND_THREE INDEX 9 128 8 135
TST2 TABLE 0 136 8 143
…
TST2 TABLE 3 160 8 167
TST3 TABLE 0 296 8 303
IND_THREE INDEX 0 304 8 311
IND_FOUR INDEX 0 312 8 319
IND_THREE INDEX 1 320 8 327
TST3 TABLE 1 328 8 335
IND_THREE INDEX 2 336 8 343
IND_THREE INDEX 3 344 8 351
TST3 TABLE 2 352 8 359
IND_THREE INDEX 4 360 8 367
TST3 TABLE 3 368 8 375
IND_THREE INDEX 5 376 8 383
IND_THREE INDEX 6 10496 8 10503
TST3 TABLE 4 10504 8 10511
IND_THREE INDEX 7 10512 8 10519
IND_THREE INDEX 8 10520 8 10527
TST3 TABLE 5 10528 8 10535
TST2 TABLE 4 10536 8 10543
…..
TST2 TABLE 80 19840 1024 20863
98 rows selected.
The original table occupied space between 384 and 10495. Oracle copied the data to space 10536 to 20863 before dropping the old segments. There’s the cause of the high increase in unused blocks. We therefore increased the table fragmentation in the datafile by re-organising.
To conclude, if you have any doubts left, lets drop everything but tst2.
SQL> drop table tst3 purge;Table dropped.
SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
—————————— —————— ———- ———- ———- ———-
TST2 TABLE 0 136 8 143
TST2 TABLE 1 144 8 151
TST2 TABLE 2 152 8 159
TST2 TABLE 3 160 8 167
TST2 TABLE 4 10536 8 10543
TST2 TABLE 5 10544 8 10551
TST2 TABLE 6 10552 8 10559
TST2 TABLE 7 10560 8 10567
TST2 TABLE 8 10568 8 10575
TST2 TABLE 9 10576 8 10583
TST2 TABLE 10 10584 8 10591
TST2 TABLE 11 10592 8 10599
TST2 TABLE 12 10600 8 10607
TST2 TABLE 13 10608 8 10615
TST2 TABLE 14 10616 8 10623
TST2 TABLE 15 10624 8 10631
TST2 TABLE 16 10752 128 10879
TST2 TABLE 17 10880 128 11007
TST2 TABLE 18 11008 128 11135
TST2 TABLE 19 11136 128 11263
TST2 TABLE 20 11264 128 11391
TST2 TABLE 21 11392 128 11519
TST2 TABLE 22 11520 128 11647
TST2 TABLE 23 11648 128 11775
TST2 TABLE 24 11776 128 11903
TST2 TABLE 25 11904 128 12031
TST2 TABLE 26 12032 128 12159
TST2 TABLE 27 12160 128 12287
TST2 TABLE 28 12288 128 12415
TST2 TABLE 29 12416 128 12543
TST2 TABLE 30 12544 128 12671
TST2 TABLE 31 12672 128 12799
TST2 TABLE 32 12800 128 12927
TST2 TABLE 33 12928 128 13055
TST2 TABLE 34 13056 128 13183
TST2 TABLE 35 13184 128 13311
TST2 TABLE 36 13312 128 13439
TST2 TABLE 37 13440 128 13567
TST2 TABLE 38 13568 128 13695
TST2 TABLE 39 13696 128 13823
TST2 TABLE 40 13824 128 13951
TST2 TABLE 41 13952 128 14079
TST2 TABLE 42 14080 128 14207
TST2 TABLE 43 14208 128 14335
TST2 TABLE 44 14336 128 14463
TST2 TABLE 45 14464 128 14591
TST2 TABLE 46 14592 128 14719
TST2 TABLE 47 14720 128 14847
TST2 TABLE 48 14848 128 14975
TST2 TABLE 49 14976 128 15103
TST2 TABLE 50 15104 128 15231
TST2 TABLE 51 15232 128 15359
TST2 TABLE 52 15360 128 15487
TST2 TABLE 53 15488 128 15615
TST2 TABLE 54 15616 128 15743
TST2 TABLE 55 15744 128 15871
TST2 TABLE 56 15872 128 15999
TST2 TABLE 57 16000 128 16127
TST2 TABLE 58 16128 128 16255
TST2 TABLE 59 16256 128 16383
TST2 TABLE 60 16384 128 16511
TST2 TABLE 61 16512 128 16639
TST2 TABLE 62 16640 128 16767
TST2 TABLE 63 16768 128 16895
TST2 TABLE 64 16896 128 17023
TST2 TABLE 65 17024 128 17151
TST2 TABLE 66 17152 128 17279
TST2 TABLE 67 17280 128 17407
TST2 TABLE 68 17408 128 17535
TST2 TABLE 69 17536 128 17663
TST2 TABLE 70 17664 128 17791
TST2 TABLE 71 17792 128 17919
TST2 TABLE 72 17920 128 18047
TST2 TABLE 73 18048 128 18175
TST2 TABLE 74 18176 128 18303
TST2 TABLE 75 18304 128 18431
TST2 TABLE 76 18432 128 18559
TST2 TABLE 77 18560 128 18687
TST2 TABLE 78 18688 128 18815
TST2 TABLE 79 18816 1024 19839
TST2 TABLE 80 19840 1024 20863
81 rows selected.
SQL> select maxblock-sblocks-128 from (select sum(blocks) sblocks from dba_extents where tablespace_name = ‘F’) a,
2 (select max(block_id+blocks) maxblock from dba_extents where tablespace_name = ‘F’) b;
MAXBLOCK-SBLOCKS-128
——————–
10496
There’s more unoccupied space in the datafile then occupied.
SQL> alter table tst2 move tablespace f;Table altered.
SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
—————————— —————— ———- ———- ———- ———-
TST2 TABLE 0 128 8 135
TST2 TABLE 1 168 8 175
TST2 TABLE 2 176 8 183
TST2 TABLE 3 184 8 191
TST2 TABLE 4 192 8 199
TST2 TABLE 5 200 8 207
TST2 TABLE 6 208 8 215
TST2 TABLE 7 216 8 223
TST2 TABLE 8 224 8 231
TST2 TABLE 9 232 8 239
TST2 TABLE 10 240 8 247
TST2 TABLE 11 248 8 255
TST2 TABLE 16 256 128 383
TST2 TABLE 17 384 128 511
TST2 TABLE 18 512 128 639
TST2 TABLE 19 640 128 767
TST2 TABLE 20 768 128 895
TST2 TABLE 21 896 128 1023
TST2 TABLE 22 1024 128 1151
TST2 TABLE 23 1152 128 1279
TST2 TABLE 24 1280 128 1407
TST2 TABLE 25 1408 128 1535
TST2 TABLE 26 1536 128 1663
TST2 TABLE 27 1664 128 1791
TST2 TABLE 28 1792 128 1919
TST2 TABLE 29 1920 128 2047
TST2 TABLE 30 2048 128 2175
TST2 TABLE 31 2176 128 2303
TST2 TABLE 32 2304 128 2431
TST2 TABLE 33 2432 128 2559
TST2 TABLE 34 2560 128 2687
TST2 TABLE 35 2688 128 2815
TST2 TABLE 36 2816 128 2943
TST2 TABLE 37 2944 128 3071
TST2 TABLE 38 3072 128 3199
TST2 TABLE 39 3200 128 3327
TST2 TABLE 40 3328 128 3455
TST2 TABLE 41 3456 128 3583
TST2 TABLE 42 3584 128 3711
TST2 TABLE 43 3712 128 3839
TST2 TABLE 44 3840 128 3967
TST2 TABLE 45 3968 128 4095
TST2 TABLE 46 4096 128 4223
TST2 TABLE 47 4224 128 4351
TST2 TABLE 48 4352 128 4479
TST2 TABLE 49 4480 128 4607
TST2 TABLE 50 4608 128 4735
TST2 TABLE 51 4736 128 4863
TST2 TABLE 52 4864 128 4991
TST2 TABLE 53 4992 128 5119
TST2 TABLE 54 5120 128 5247
TST2 TABLE 55 5248 128 5375
TST2 TABLE 56 5376 128 5503
TST2 TABLE 57 5504 128 5631
TST2 TABLE 58 5632 128 5759
TST2 TABLE 59 5760 128 5887
TST2 TABLE 60 5888 128 6015
TST2 TABLE 61 6016 128 6143
TST2 TABLE 62 6144 128 6271
TST2 TABLE 63 6272 128 6399
TST2 TABLE 64 6400 128 6527
TST2 TABLE 65 6528 128 6655
TST2 TABLE 66 6656 128 6783
TST2 TABLE 67 6784 128 6911
TST2 TABLE 68 6912 128 7039
TST2 TABLE 69 7040 128 7167
TST2 TABLE 70 7168 128 7295
TST2 TABLE 71 7296 128 7423
TST2 TABLE 72 7424 128 7551
TST2 TABLE 73 7552 128 7679
TST2 TABLE 74 7680 128 7807
TST2 TABLE 75 7808 128 7935
TST2 TABLE 76 7936 128 8063
TST2 TABLE 77 8064 128 8191
TST2 TABLE 78 8192 128 8319
TST2 TABLE 79 8320 1024 9343
TST2 TABLE 80 9344 1024 10367
TST2 TABLE 12 10496 8 10503
TST2 TABLE 13 10504 8 10511
TST2 TABLE 14 10512 8 10519
TST2 TABLE 15 10520 8 10527
81 rows selected. SQL> select maxblock-sblocks-128 from (select sum(blocks) sblocks from dba_extents where tablespace_name = ‘F’) a,
2 (select max(block_id+blocks) maxblock from dba_extents where tablespace_name = ‘F’) b;
MAXBLOCK-SBLOCKS-128
——————–
160
Let’s try one more thing, supposing a table was highly spread with little data in it. Will it help to re-organise ?
SQL> select count(*) from tst2;COUNT(*)
———-
9999
SQL> delete from tst2 where rownum < 9000;
8999 rows deleted.
SQL> commit;
Commit complete
The segment query returns exactly the same display as before we deleted, oracle does not re-organise for you.
SQL> alter table tst2 move tablespace f;Table altered.
SQL> @seg
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS ENDBLOCK
—————————— —————— ———- ———- ———- ———-
TST2 TABLE 0 136 8 143
TST2 TABLE 1 144 8 151
TST2 TABLE 2 152 8 159
TST2 TABLE 3 160 8 167
TST2 TABLE 16 10368 128 10495
TST2 TABLE 4 10528 8 10535
TST2 TABLE 5 10536 8 10543
TST2 TABLE 6 10544 8 10551
TST2 TABLE 7 10552 8 10559
TST2 TABLE 8 10560 8 10567
TST2 TABLE 9 10568 8 10575
TST2 TABLE 10 10576 8 10583
TST2 TABLE 11 10584 8 10591
TST2 TABLE 12 10592 8 10599
TST2 TABLE 13 10600 8 10607
TST2 TABLE 14 10608 8 10615
TST2 TABLE 15 10616 8 10623
TST2 TABLE 17 10624 128 10751
TST2 TABLE 18 10752 128 10879
TST2 TABLE 19 10880 128 11007
TST2 TABLE 20 11008 128 11135
TST2 TABLE 21 11136 128 11263
TST2 TABLE 22 11264 128 11391
23 rows selected. SQL> select maxblock-sblocks-128 from (select sum(blocks) sblocks from dba_extents where tablespace_name = ‘F’) a,
2 (select max(block_id+blocks) maxblock from dba_extents where tablespace_name = ‘F’) b;
MAXBLOCK-SBLOCKS-128
——————–
10240
And that’s with 90% of the data deleted from the table. We’ve actually made the situation worse, the datafile has a high end of 11391 now, before with all the data in there we were at 10527.
The long and the short of this is that you don’t have great control over where segments are placed in a datafile. Checking the file for contiguous blocks and re-organising when there appears to be fragmentation will not always assist in freeing space in a datafile.
