Resizing datafiles and understanding the High Water Mark

Resizing datafiles is something that I seem to do a lot of.  Usually, I am increasing them to allow for data growth but sometimes I need to shrink them to reclaim unused space.  When shrinking a datafile, you cannot make it smaller than the highest allocated extent in the datafile, aka the High Water Mark (HWM).  If you do, you will get a message like this:

alter database datafile '/oradata/DB10201/test.dbf' resize 120m;
alter database datafile '/oradata/DB10201/test.dbf' resize 120m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Below is a quick demo that shows how this works and how to determine exactly how small you can make your datafile, eliminating the trial-and-error approach.  You can also determine exactly which objects are preventing you from shrinking the datafile. First, I will create a 500MB tablespace and a couple of tables.

 create tablespace test2 datafile '/oradata/DB10201/test.dbf' size 500m uniform size 1m;

 create table test (
COL1 NUMBER,
COL2 VARCHAR2(2000),
COL3 VARCHAR2(2000),
COL4 VARCHAR2(2000)) tablespace test2;

declare
v_string varchar2(2000);|
begin
for i in 1..1000 loop
v_string := v_string||'XX';
end loop;
for i in 1..10000 loop
      insert into test values (i,v_string,v_string,v_string);     
end loop;
commit;
end;
/

 how big is the TEST table?

select sum(bytes)/1024/1024 from dba_segments where segment_name='TEST'
and tablespace_name='TEST2'; 
SUM(BYTES)/1024/1024
--------------------
                  80

What is the HWM for TEST?

select
max((block_id + blocks-1)*8192))/1024/1024 "HWM (MB)"
from dba_extents  where owner='SYS' and file_id=6
and segment_name='TEST';

HWM (MB)
----------
   81.0625

(multiply by 8192 because that is the block size.  Divide by 1024/1024 to convert to MB)

 This makes sense.  The table is 80MB and the HWM is 81MB (with a bit of overhead).  Now I will create another table in the same tablespace, moving the HWM towards the end of the datafile.

 create table TEST2 tablespace TEST2 as select * from test;

 Check the HWM again

select (max((block_id + blocks-1)*8192))/1024/1024 "HWM (MB)"
from dba_extents where file_id=6;

  HWM (MB)
----------
  160.0625

 Again, this makes sense.  Now I will drop the first table and check the HWM and the total segment size in the tablespace.

drop table test purge;
Table dropped.

select sum(bytes)/1024/1024 "Segment Size (MB)" from dba_segments
where tablespace_name='TEST2';

Segment Size (MB)
-----------------
               80

select (max((block_id + blocks-1)*8192))/1024/1024 "HWM (MB)"
from dba_extents
where file_id=6;

  HWM (MB)
----------
  160.0625

So, at this point there is only 80MB worth of data in the tablespace but the HWM is 160MB.  If you came into this situation not knowing the history, it would reasonable to think you could shrink the datafile to something a little more than 80MB.  You would be incorrect.  Because the HWM is 160MB, you won’t be able to shrink it lower than that unless you re-org the table.

alter database datafile '/oradata/DB10201/test.dbf' resize 159m;
alter database datafile '/oradata/DB10201/test.dbf' resize 159m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

alter database datafile '/oradata/DB10201/test.dbf' resize 161m;
Database altered.

Now lets look at it from a different angle.  Suppose I try to shrink the datafile to 100MB and get the ORA-03297 error.  You can use this query to find out exactly which objects are preventing it from happening.

select segment_name, segment_owner from dba_extents
where file_id=6
and ((block_id + blocks-1)*8192 > 104857600;

Of course, we already know the answer because this is just a silly demo.  There are various ways to reset the HWM for a datafile. 

Refer :- Max Possible datafile shrink script Max possible datafile shrink based on HWM