How to claim unused space in datafile

[rmgirma]

Hello,
I have a tablespace (TS_01) that consists of 5 data files. The tablespace is housing 200+ tables but 99% of the space is used by 1 table (Table_A) storing temporary documents that get deleted the following year. I have just deleted 90% of the rows in Table_A for data older than 2005 with should free up at least 10GB of space. (the export file was 10GB for this table before the row deletion, it's 47MB now). I have moved table_A to a new tablespace TS_02 and rebuilt the indexes but the space it used on TS_01 has still not been freed up and all 5 datafiles are still 95% full. How do I reclaim the unused space ?
Thank you

[sambavan]

How did you move the table?

Thanx,
Sam

[rmgirma]

Alter table Table_A move tablespace TS_02;

[sambavan]

That should have released the segments that was used by the table_A on ts_01. What is your DB & OS environments and versions?

BTW, check for any locks being held on that table, that was prior to the move? if so, upon releasing that lock or the session would release the space for you.

[rmgirma]

That's what I thought it would do too... unless Enterprise Manager is not displaying the correct information.
os : Win 2003 Server
db : Oracle 9.2.0.6

[rmgirma]

no current lock on this or any other user table. It's a dev server and I'm the only one connected to it.

[sambavan]

What does your tablespace mapping on the OEM show? Can you see the table in the new tablespace, from the sqlplus query?

Thanx,
Sam

[rmgirma]

OEM shows the correct ts name and also shows the correct number of rows once analyzed.

[wdfloyd]

What does Oracle say is still being stored in TS_01?

i.e.:

select segment_name, bytes
from dba_segments
where tablespace_name = 'TS_01'
order by bytes

[rmgirma]

no, oracle says that it's in TS_02, the correct size is also being reported in OEM or SQL statement. I don't think the issue is with the table not releasing the space. I have dropped the table but the space has still not been freed up. I can move other tables and space have been freed for those but not for table_a, and like I said table_a was the largest and used up 99% of all available space.