Is always annoying to resize a datafile with empty space but some blocks occupied at the end of the file.
You get the
ERROR at line 1: ORA-03214: File Size specified is smaller than minimum required
There are may brute force ways to solve this as rebuild or move everthing in the tablespace or exp/imp.
Sometimes you just want resize one datafile to archive some monitoring limits with no impact to production.
Find the last object in the datafiles for a specific tablespace (the is slow, checkout Metalink Note 422730)
select owner,segment_name,partition_name,segment_type from dba_extents where (file_id,block_id) in (select file_id,max(block_id) from dba_extents group by file_id) and tablespace_name like 'INDEX_TBSP_NAME%'
If you have the object, you can do:
Alter table move; -- ! table unavailable, indexes need to be rebuild
alter index rebuild; -- index unavailable
alter index rebuild online; -- index available during rebuild