Until Oracle 10gR1, when the High Water Mark (HWM) of the
table moved forward (as new rows were inserted into the table), it could not be
moved backwards in order to decrease the table size and de-allocate space,
releasing it back to the tablespace free space. When we deleted many rows from
the table, the HWM and the table size remained and the only way to decrease its
size was by truncating the table. In 10gR1, Oracle introduced an exciting
feature called “shrink table”.
How does it work? When we perform the shrink command, Oracle
uses row movement (which must be enabled on the table) to move rows from the
last blocks of the table to the beginning of the table. After moving the rows,
a table lock takes place while Oracle moves the HWM backwards. Then, the blocks
after the HWM can be released and the table size is reduced.
The shrink table command works only for segments located in
a locally managed tablespace with automatic segment space management
Now we’ll try to understand how much space is released back
to the tablespace. In order to do this, we will perform the following:
·
Create two tablespaces, one (tbs_uni) will use
uniform extent allocation of 1MB, the other one (tbs_sys) will use system
extent allocation.
·
Create a table in each tablespace (with the same
structure and data).
·
Enable row movement for both tables.
·
Check the extents of the tables.
·
Delete rows from both tables.
·
Shrink both tables.
·
Check the extents of the tables to see the
released space.
rajesh@PDB1> select tablespace_name,initial_extent,extent_management,allocation_type
2 from dba_tablespaces
3 where tablespace_name in
('TBS_SYSTEM','TBS_UNIFORM') ;
TABLESPACE_NAME INITIAL_EXTENT EXTENT_MAN ALLOCATIO
--------------- -------------- ---------- ---------
TBS_UNIFORM
1048576 LOCAL UNIFORM
TBS_SYSTEM
65536 LOCAL SYSTEM
2 rows selected.
rajesh@PDB1>
rajesh@PDB1> create table tbl_uniform (id number, x
char(1024))
2 enable row movement
3 pctfree 0 tablespace tbs_uniform;
Table created.
rajesh@PDB1>
rajesh@PDB1> create table tbl_system (id number, x
char(1024))
2 enable row movement
3 pctfree 0 tablespace tbs_system;
Table created.
rajesh@PDB1> begin
2 insert into tbl_uniform(id,x)
3 select level , '*'
4 from dual
5 connect by level <= 100000;
6
7 insert into tbl_system(id,x)
8 select level , '*'
9 from dual
10 connect by level <= 100000;
11
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
rajesh@PDB1>
rajesh@PDB1> select segment_name, (blocks*8) as size_KB
,count(*)
2 from user_extents
3 where segment_name ='TBL_UNIFORM'
4 group by segment_name,blocks
5 order by 2;
SEGMENT_NAME
SIZE_KB COUNT(*)
------------------------------ ---------- ----------
TBL_UNIFORM 1024 113
1 row selected.
rajesh@PDB1>
rajesh@PDB1> select segment_name, (blocks*8) as size_KB
,count(*)
2 from user_extents
3 where segment_name ='TBL_SYSTEM'
4 group by segment_name,blocks
5 order by 2;
SEGMENT_NAME
SIZE_KB COUNT(*)
------------------------------ ---------- ----------
TBL_SYSTEM 64 16
TBL_SYSTEM 1024 63
TBL_SYSTEM 8192 7
3 rows selected.
rajesh@PDB1>
For table TBL_UNIFORM, which is located in uniform tablespace, we see
113 extents each 1MB
For table TBL_SYSTEM, which is located in system allocation we see total
86 extents of which first 16 are 64Kb, next 63 are 1MB and last 7 are 8MB.
Now we will delete rows from the tables and shrink the tables to see the extent map.
rajesh@PDB1> delete from tbl_uniform where id <= 2500;
2500 rows deleted.
rajesh@PDB1> delete from tbl_system where id <= 2500;
2500 rows deleted.
rajesh@PDB1> commit;
Commit complete.
rajesh@PDB1> alter table tbl_uniform shrink space;
Table altered.
rajesh@PDB1> alter table tbl_system shrink space;
Table altered.
rajesh@PDB1> select segment_name, (blocks*8) as size_KB
,count(*)
2 from user_extents
3 where segment_name ='TBL_UNIFORM'
4 group by segment_name,blocks
5 order by 2;
SEGMENT_NAME
SIZE_KB COUNT(*)
------------------------------ ---------- ----------
TBL_UNIFORM 1024 111
1 row selected.
rajesh@PDB1>
rajesh@PDB1> select segment_name, (blocks*8) as size_KB
,count(*)
2 from user_extents
3 where segment_name ='TBL_SYSTEM'
4 group by segment_name,blocks5 order by 2;
SEGMENT_NAME
SIZE_KB COUNT(*)
------------------------------ ---------- ----------
TBL_SYSTEM 64 16
TBL_SYSTEM 1024 63
TBL_SYSTEM 6208 1
TBL_SYSTEM 8192 5
4 rows selected.
rajesh@PDB1>
Now the extent maps shows,
For table TBL_UNIFORM we see 111 extents, each 1MB (two extents less
than before)
For table TBL_SYSTEM we see 85 extents (one extent less than before),
the first 84 extents are exactly as before, while the last one was reduced to
over 6Mb.
The last thing we’ll do is to add 2500 rows back to the tables
rajesh@PDB1> begin
2 insert into tbl_uniform(id,x)
3 select level , '*'
4 from dual
5 connect by level <= 2500;
67 insert into tbl_system(id,x)
8 select level , '*'
9 from dual
10 connect by level <= 2500;
11
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
rajesh@PDB1> select segment_name, (blocks*8) as size_KB
,count(*)
2 from user_extents
3 where segment_name ='TBL_UNIFORM'
4 group by segment_name,blocks
5 order by 2;
SEGMENT_NAME
SIZE_KB COUNT(*)
------------------------------ ---------- ----------
TBL_UNIFORM 1024 113
1 row selected.
rajesh@PDB1>
rajesh@PDB1> select segment_name, (blocks*8) as size_KB
,count(*)
2 from user_extents
3 where segment_name ='TBL_SYSTEM'
4 group by segment_name,blocks
5 order by 2;
SEGMENT_NAME
SIZE_KB COUNT(*)
------------------------------ ---------- ----------
TBL_SYSTEM 64 16
TBL_SYSTEM 1024 63
TBL_SYSTEM 6208 1
TBL_SYSTEM 8192 6
4 rows selected.
rajesh@PDB1>
For table TBL_UNIFORM we see 111 extents, each 1MB (exactly as in the beginning)
For table TBL_SYSTEM we see 86 extents again, the first 84 are exactly
the same, the new one is 8Mb but the 85th extent stayed just over
6Mb and did not grow to 8Mb again.
So where is the problem? ,the problem resides in tablespace, To find
the problem we need to add to the extent map the physical location of the
extent. The location of the extent in the file is according to the block_id
column in DBA_EXTENTS table
rajesh@PDB1>
rajesh@PDB1> select file_id,segment_name,tablespace_name,
(blocks*8)as size_Kb,
2 min(block_id) min_blks, max(block_id)
max_blks,
3 count(block_id) as cnt_blks
4 from (5 select file_id,segment_name,tablespace_name,blocks,block_id
6 from dba_extents
7 where segment_name ='TBL_UNIFORM'
8 and owner = user
9 union all10 select file_id,'Free_Space',tablespace_name,blocks,block_id
11 from dba_free_space
12 where tablespace_name ='TBS_UNIFORM'
13 )
14 group by file_id,segment_name,tablespace_name,
blocks
15 order by file_id,max_blks;
FILE_ID
SEGMENT_NAME TABLESPACE_NAME SIZE_KB
MIN_BLKS MAX_BLKS CNT_BLKS
---------- --------------- --------------- ---------- ----------
---------- ----------
13
TBL_UNIFORM TBS_UNIFORM 1024 128
14464 113
13 Free_Space TBS_UNIFORM 1980416 14592
14592 1
2 rows selected.
rajesh@PDB1>
All extents come one after the other, while the free space is at the
end of the file.
rajesh@PDB1> select file_id,segment_name,tablespace_name,
(blocks*8)as size_Kb,
2 min(block_id) min_blks, max(block_id)
max_blks,
3 count(block_id) as cnt_blks
4 from (
5 select
file_id,segment_name,tablespace_name,blocks,block_id
6 from dba_extents
7 where segment_name ='TBL_SYSTEM'
8 and owner = user
9 union all
10 select
file_id,'Free_Space',tablespace_name,blocks,block_id
11 from dba_free_space
12 where tablespace_name ='TBS_SYSTEM'
13 )
14 group by
file_id,segment_name,tablespace_name, blocks
15 order by file_id,max_blks;
FILE_ID
SEGMENT_NAME TABLESPACE_NAME SIZE_KB
MIN_BLKS MAX_BLKS CNT_BLKS
---------- --------------- --------------- ---------- ----------
---------- ----------
14 TBL_SYSTEM TBS_SYSTEM 64 128 248 16
14 TBL_SYSTEM TBS_SYSTEM 1024 256 8192 63
14 TBL_SYSTEM TBS_SYSTEM 6208 13440
13440 1
14 Free_Space
TBS_SYSTEM 960 14216
14216 1
14 TBL_SYSTEM TBS_SYSTEM 8192 8320
14336 6
14 Free_Space TBS_SYSTEM 1974272 15360
15360 1
6 rows selected.
rajesh@PDB1>
Note the free space after the 6MB extent. Remember the flow that got us
to this situation. We filled the table, then deleted rows, shrank the table and
inserted more rows. At first, the last extent of the table was 8MB in size, and
when we shrank the table, the extent was reduced to about 6MB, releasing about
2MB back to the tablespace. When we inserted new rows, Oracle needed to
allocate a new extent for them; however, it seems that extents cannot start at
any block in the file.
Since this tablespace uses system allocation extent management, other
tables that need small extents may allocate this free space. Large tables,
however, will not be able to allocate this space, and when allocating their
next extent will leave an unused area.
To sum up the problem, when we shrink a table in a system allocation
extent management tablespace, a part at the end of the table is released back
to the tablespace’s free space. Usually, this leaves a smaller than usual last
extent in the table. The next extent of the table will not always be able to
start at exactly the next block, depending on the size of the requested extent.
If this scenario happens, the space left between the extents can be allocated
only for small extents, so usually not for extents of the same table or other
large tables.
The best thing to do is to avoid this problem altogether. It depends on
your specific system and how you use the database. These are some points that
might be worth considering:
·
If you use system allocation tablespaces with
big tables, and you delete and insert to these tables, you should consider not
using the “shrink table” feature. That way new rows can use the space the
deleted rows left without any problem or fragmentation
·
If you only have large tables on a system
allocation tablespace and you have to shrink the tables periodically, consider
moving to uniform allocation with adequate extent size to avoid the
fragmentation problem.
