I have Oracle 11.2 on Redhat linux. I recently dropped some schemas on a tablespace and I know there are lot space in the datafiles below the high water mark. This is supported by OEM, which tells me the tablespace is only be used 20%. Now I want reclaim the diskspace allocated to the tablespace.
I first tried
alter database datafile '/u01/app/oracle/oradata/TDEV/bi1.dbf' resize 22693m;
This reclaimed space above the high water mark. I can query the dba_extents and dba_datafiles to verify that.
Second I ran a loop to shrink each tables on the tablespace withr
alter table <tbl_name> enable row movement;
alter table <tbl_name> shrink space;
I expect this will consolidate each table and lower the high water mark. See article:
http://sysdba.wordpress.com/2006/04/28/how-to-adjust-the-high-watermark-in-oracle-10g-alter-table-shrink/
Then I use 'alter database datafile xxx resize ' to reduce the datafile to a size slightly larger than the sused space as seen in OEM. But the command failed.
alter database datafile '/u01/app/oracle/oradata/TDEV/bi1.dbf' resize 8693M;
ORA-03297: file contains used data beyond requested RESIZE value
I tried to increase the value of resize parameter, it all failed unless it is close to the old high water mark.
I also used
alter tablespace bi_data coalesce;
alter database datafile '/u01/app/oracle/oradata/TDEV/bi1.dbf' resize 8693M;
But got the same error.
Why this commands do not work for me?