can't shrink the tablespaces

[wengv]

I have one tablespace which contains 4 data files. The usage is about 0.2% but its size is 2G. I failed to shrink it. Here is the error message from oracle

ORA-03297: file contains used data beyond requested RESIZE value.

I wanted to know a save way to shrink this tablespace.

[gbrabham]

You need to look at a map of the tablespace (datafile in question). Just because it is only x% full, doesn't mean that the space is at the end of the tablespace. Say a datafile contained 100 Oracle blocks. Say 1 of the tables in this tablespace was located on the 99th block, but the other 99 blocks were available. You could only shrink it down 1 block.

Find out where the objects are in the tablespace. Ensure the freespace blocks are together (coalesce). If it's an index, then you can rebuild it. If it's a table, you may be able to deallocate the unused space of the table (up to highwater mark), or you may have to rebuild the table (COPY or EXPORT/IMPORT) to get the table(s) where you want them. ]

HTH
Gregg

[Mr.Hanky]

If it is empty, offline it, drop it, remove the datafiles and (if neccassary) re-create it.

No?

MH

[wengv]

thanks guys. It seems there is no easy way to set it free.

[engiri]

Originally posted by gbrabham
Say 1 of the tables in this tablespace was located on the 99th block, but the other 99 blocks were available. You could only shrink it down 1 block. HTH
Gregg

Can you help us how to find which table is sitting at the 99 th block.

Thanks
Giri

[slimdave]

The system view DBA_EXTENTS has this information.

SQL> desc dba_extents
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------

 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

Most of the columns should be pretty obvious -- block_id is the starting block for the extent, and blocks tells you how big it is.

You can find the segments that occupy the last space in the files on a tablespace with ...

Select
   segment_name,
   partition_name, -- exclude if you don't partition
   segment_type,
   block_id,
   blocks
From
   (
   Select
      segment_name,
      partition_name, -- exclude if you don't partition
      segment_type,
      block_id,
      blocks,
      max(block_id) 
         over (partition by file_id) max_block_id
   From
      dba_extents
   where
      tablespace_name ='MY_TABLESPACE'
   )
Where
   block_id = max_block_id

For inconveniently placed tables, you might try "ALTER TABLE MY_TABLE MOVE", and for indexes try "ALTER INDEX MY_INDEX REBUILD" to get them to shiftsomewhereelse. For locally managed tablespaces with uniform extent sizes, this will move them to the beginning of the files -- for other TS types you might not be so lucky.

[engiri]

This info is very useful to us.