Re: ORA-03297 and tablespace coalesce

  Hi,

I would recommend to review Metalink Note #130866.1 - How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark.   **--romas

On 12/18/07, Ujang Jaenudin <ujang.jaenudin_at_gmail.com> wrote:
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l