How to claim unused space in datafile

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

How did you move the table?

Thanx,
Sam

Alter table Table_A move tablespace TS_02;

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.

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

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

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

Thanx,
Sam

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

What does Oracle say is still being stored in TS_01?

i.e.:

Code:

---

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

---

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.

I have a gut feeling some lock is being held on one/many blocks of the moved table on the old tablespace. Since you are the only one in the database, can you bounce the database and check the segments status?

Sambavan,
I'm at a loss here. The space has still not been freed even after I bounced the database. I even restarted the server just in case. I guess the only thing left for me to try is to export the user, drop user cascade, and check if the space is freed. Then import the user back.
Thanks for all your help

It's hard to say without seeing the output from sqlplus. What objects does Oracle report being in TS_01? Is it possible the indexes are still there?

Not sure if this will help, but here is a script that I use to see what the extents look like in my datafiles.

Maybe you've ran into a bug? I've never seen this behavior before.

I was finaly able to reclaim the unused space by droping the user and importing it back. Thanks all for your help and time. The one information I left out was this db was setup as one of the standby databases for prod and got activated a couple of days ago to be used as a dev server. I wonder if the free space could not have been reclaimed due to this, but again I could reclaim the space when I move other tables owned by this same user. I was only having problem with this one huge table.
Thank you