Reducing datafile size to recover free space

select value from v$parameter where name = 'db_block_size'

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

When I attempt to let go of space by resizing my datafile I get ORA-03297.  How do I get around this ORA-03297 error and resize my data file?

ALTER DATABASE DATAFILE '/d001/pi2006_01.dbf' RESIZE 3000M;

ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

FAST SOLUTION: Purge the recycle bin and try again.

FULL SOLUTION: https://clck.ru/FUsUH