shrink_me

Here is an example showing what I tried to draw above:

ops$tkyte@ORA9IR2> create tablespace shrink_me
2 datafile '/tmp/shrink_me.dbf' size 704k
3 segment space management manual
4 uniform size 64k
5 /

Tablespace created.

we have exactly 10 64k extents we can use. (the 11th 64k block of space is used by
Oracle to manage these locally managed tablespaces in the datafile)


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( x int, a char(2000) default 'a', b char(2000)
default 'b', c char(2000) default 'c') tablespace shrink_me;

Table created.

ops$tkyte@ORA9IR2> create table t2 ( x int, a char(2000) default 'a', b char(2000)
default 'b', c char(2000) default 'c') tablespace shrink_me;

Table created.

Each row in these tables will consume a block (8 rows/extent - but don't forget the
first block is borrowed by Oracle to manage space in the segment...)

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 88 free

so, we have the starting scenario - T1 has an extent, T2 has one and the rest of the
file is "free space", now lets fill up t1:


ops$tkyte@ORA9IR2> insert into t1 (x) select rownum from all_objects where rownum <= 56;

56 rows created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 32 T1
13 33 40 T1
13 41 48 T1
13 49 56 T1
13 57 64 T1
13 65 72 T1
13 73 80 T1
13 81 88 free

10 rows selected.

we have the middle scenario - if we dropped T1 now, all of the T1's would become free
space and we could shrink the file, however:


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 (x) select rownum from all_objects where rownum <= 8;

8 rows created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 32 T1
13 33 40 T1
13 41 48 T1
13 49 56 T1
13 57 64 T1
13 65 72 T1
13 73 80 T1
13 81 88 T2

10 rows selected.

Now the entire tablespace is full - no more free space - but we drop t1 and get LOTS of
free space:

ops$tkyte@ORA9IR2> drop table t1;

Table dropped.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 free
13 17 24 T2
13 25 80 free
13 81 88 T2

the tablespace is 80% empty - but we cannot shrink it - we cannot shrink it at all, let
alone to 300k - because the LAST EXTENT is taken by T2

ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;
alter database datafile '/tmp/shrink_me.dbf' resize 300k
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


but lets move T2 around...

ops$tkyte@ORA9IR2> alter table t2 move;

Table altered.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T2
13 17 24 free
13 25 32 T2
13 33 88 free

now we have lots of free space at the end of the file and we can resize:

ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;

Database altered.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T2
13 17 24 free
13 25 32 T2


don't forget, if you move a table, you have to then rebuild the indexes