Oracle tablespace resizing with consecutive free blocks

Question

I often reduce tablespace in our Oracle instance (11g).

Using the following script (from system user) I can know exactly the allocated space for each object in choosen tablespace (e.g. MY_TABLESPACE):

select
    tablespace_name, file_id, block_id,
    block_id + blocks - 1  end_block, owner,
    segment_name, partition_name, segment_type
from
    dba_extents
where 1=1
and   tablespace_name = 'MY_TABLESPACE'
union all
select
    tablespace_name, file_id, block_id,
    block_id + blocks - 1   end_block,
    'free' owner, 'free' segment_name,
    null partition_name, null segment_type
from
    dba_free_space
where 1=1
and   tablespace_name = 'MY_TABLESPACE'
order by
    file_id desc,
    block_id desc;

Something I try the following strange thing, consecutive free block that I can't resize:

Consecutive free blocks

This means which we have 19095 free blocks (about 150 Mb) that can't resize.

To avoid the complete drop and create back of tablespace, could someone help me?

Vampiro Vampiro · Accepted Answer · 2016-01-04T16:49:25

How are you trying to "resize" the tablespace?

Looks like all you need to do is run the Tablepsace level Shrink option of the Segment Advisor, which will do the shrinking for you and reclaim that space.

Easy to do from OEM if you have it - just run Segment Advisor for your tablespace.

If you need a script to do it, check the DBMS_ADVISOR package usage here: https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm

Oracle tablespace resizing with consecutive free blocks

1 Answers