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?
Life is a journey, not a destination!
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
Still not clear
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.
Click Here to Expand Forum to Full Width