See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:153612348067 <code>
It'll provide a script to resize this file as small as we can -- which may be 465meg.
Think of the file as a collection of extents (it is). Let's say the layout is like this (X = allocated extent, O = free extent):
0meg 465m
XXXX0000000000X000000000000XXXXXX0000000000XX000000000000000000X
So, as you can see here -- that last X out by 465m will prevent us from shrinking this file. If you can determine what object is out there (see query following for how to do that) you can perhaps "move or rebuild" that object -- or perhaps just drop it and recreate it later.
To see what object is hanging out out there -- run:
column tablespace_name format a20
column "Name" format a45
break on file_id skip 1
ttitle &1
select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper('&1')
UNION
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name = upper('&1')
order by 1,2,3
/
and pass it in the name of the tablespace. Look for the large block_id at the end of the query and it'll show you the object you are looking for.