ORA-03297 and tablespace coalesce

all,

db 9.2.0.6 on solaris 9

I have done a house keeping job:
- expand partition to year 2008 & 2009
- move the old partition say the old partition in a TBS1 tablespace,
and this is Locally and ASSM tablespace to other tablespaces which I
make them smaller but many.
- add new partition to other tablespaces
- as per 9i db console & grid control there are many free space in TBS1 (120 GB)
- I query dba_free_space, there are many rows which many block_id
- when alter datafile '/path' resize to smaller size the error coming....

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

- I heard coalesce tablespace may work for this case.... is it true??
- how to move table from the old tablespace to other tablespace which
the table has partitions and I want to avoid index usable??? because
rebuilding indexes took times (8 hours).

thanks for your share....

-- 
regards
ujang
jakarta - indonesia
--
http://www.freelists.org/webpage/oracle-l

Other related posts: