You might try running this query to see how that tablespace is
'mapped':

Select
    'free space' owner,
    '    ' object,
    file_id,
    block_id,
    blocks
from
    sys.dba_free_space
where
    tablespace_name = upper('&&1')
union
select
    substr(owner,1,20),
    substr(segment_name,1,32),
    file_id,
    block_id,
    blocks
from
    sys.dba_extents
where
    tablespace_name = upper('&&1')
order by 3,4;

Then, create this procedure as SYS and run it for every table in the
suspect tablespace:

create or replace
procedure show_space
( p_segname in varchar2,
 p_owner   in varchar2 default user,
 p_type    in varchar2 default 'TABLE' )
as
   l_free_blks                 number;

   l_total_blocks              number;
   l_total_bytes               number;
   l_unused_blocks             number;
   l_unused_bytes              number;
   l_LastUsedExtFileId         number;
   l_LastUsedExtBlockId        number;
   l_LAST_USED_BLOCK           number;
   procedure p( p_label in varchar2, p_num in number )
   is
   begin
       dbms_output.put_line( rpad(p_label,40,'.') ||
                             p_num );
   end;
begin
   dbms_space.free_blocks
   ( segment_owner     => p_owner,
     segment_name      => p_segname,
     segment_type      => p_type,
     freelist_group_id => 0,
     free_blks         => l_free_blks );

   dbms_space.unused_space
   ( segment_owner     => p_owner,
     segment_name      => p_segname,
     segment_type      => p_type,
     total_blocks      => l_total_blocks,
     total_bytes       => l_total_bytes,
     unused_blocks     => l_unused_blocks,
     unused_bytes      => l_unused_bytes,
     LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
     LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
     LAST_USED_BLOCK => l_LAST_USED_BLOCK );

   p( 'Free Blocks', l_free_blks );
   p( 'Total Blocks', l_total_blocks );
   p( 'Total Bytes', l_total_bytes );
   p( 'Unused Blocks', l_unused_blocks );
   p( 'Unused Bytes', l_unused_bytes );
   p( 'Last Used Ext FileId', l_LastUsedExtFileId );
   p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
   p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/

Spool the output from the following query to a file:

select 'exec show_space('''||object_name||''','''||owner||''','''||
object_type||''')'
from dba_objects
where tablespace_name = 'USER_CDC_DATA'
and object_type in ('TABLE','INDEX','CLUSTER','PARTITION');

then run the resulting script, spooling that output to a file.  I
don't know what you'll find, but you might find what is consuming your
space.

David Fitzjarrell

Do you have OEM?  If on version 9, it has a real informative
tablespace map graphic.  10R2 EM has something too, my brain isn't
remembering it right now, you must be on 10 if you mention shrink?
What exact (like 10.2.0.4) version are you on?  Have you done any mass
deletes?  Have you tried a coalesce?  Do you purge deleted tables?
What is your temporary tablespace?

jg
--
@home.com is bogus.
http://arstechnica.com/news.ars/post/20080415-gone-in-60-seconds-spambot-cracks-
livehotmail-captcha.html