Hello everyone,
I have a problem with one of my tablespaces. first, I am using Oracle 11.2.0.4 EE on exadata.
I have a tablespace which contains partitions of one table only. I compressed and move some old partitions into another tablespace and there is only 1 partition on this tablespace now. then I moved that partition (alter table xxx move partiton sys_xxx) and then tried to resize datafile of this tablepsace (there is just one datafile for this tablespace) but I got the error "ORA-03297: file contains used data beyond requested RESIZE value" which I know what it is but problem is I already know all partitions (except one) has been moved to another tbs and last one is just moved (which is moved to beginning of the datafile) so I could be able to resize datafile but can not. I know you can say how do I know this partition is at the beginning of the tbs, I checked it it is there and my last partition is very small but datafile size is really big.
aynway, then I selected DBA_SEGMENTS for this tablespace and I saw, TEMPORARY objects named like "214.12345" I know that these are the created objects for moving operation but they should be removed after the move partition operations but they didn't, so search for them and used some document explaining how to remove. I used DBMS_SPACE_ADMIN and mark them as corrupted and then remove corrupted segments. after that I tried to resize but same error again. when I check DBMS_SEGMENTS there were only my partition, temporary objects was gone.
so I checked enterprise manager console to show tbs content and saw this:
as you can see there is a black area which listed as "Unmapped". I searched for this too and many people says it is the objects in recyclebin. I already purged DBA_RECYCLEBIN and there is nothing in there now but I still can not resize the datafile.
I rebuild the tablespace bitmaps using DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS also used assm_tablespace_verify and tablespace_verify procedures but nothing changed.
I found this thread many years ago: but it didn't help, generally recyclebin issue.
any idea?
thanks.