How to reclaim space in oracle datafile

2 lines of code:

alter table yourTableName deallocate unused space;
alter index IndexName deallocate unused space;

doesn't the high water mark need to be raised in order to be able to resize a datafile smaller?

You can locate the objects holding extents that cause the resize to fail and 'move' them to a different tablespace, then resize and move them back.

sammySeltzer...unless i'm mistaken, for what i've read, issuing a table 'shrink' does not change the HWM...  can you reference a doc so i can read it?

what if there is no space to add another tablespace... then how is it resolved?

I was under the impression that SHRINK doesn't actually change the HWM and is basically equivalent to moving (delete+insert) rows at/near the top of the table.

Take a look at this (for 10gR2):
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#CBBBCHJI

Goal:
Shrink tables and the resize datafiles in order to regain some space

The following is what I have determined needs to be done in order to size down a datafile after receiving error ORA-03297..  

Let me know if these steps will accomplish the 'goal'...

1) alter table <table_name> enable row movement;

2) alter table <<tbl> shrink space compact;

   a)  reads the table from the bottom up
   b)  when it hits a row, it deletes it and does a positioned insert of it at the "top" of the table - in the first free slot it finds.
   c)  it keeps doing that until the first free slot it finds is the one the row was on in the first place (eg: there is no more free space above that row)

   moves the rows from the bottom to the top of the table, then the shrink space only has to redraw the high water mark.
   
3) alter table <table_name> shrink space cascade;
    this command will re-pack the rows, move down the HWM, and releases unused extents.  
    With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:

shrink space releases extents that do not contain data
shrink does not MOVE EXTENTS
shrink de-allocates existing extents.


At this point in order to be able to shrink the DATAFILE (if you get error ORA-03297: file contains used data beyond requested RESIZE value),  then you need to find the object at the END OF THE DATAFILE via DBA_EXTENTS.  Once found do #4.  then you should be able to resize the DATAFILE.

4) alter table <table_name> move; -- For all tables

   moves rows down into un-used space and adjusts the HWM but does not
   adjust the segments extents, and the table size remains the same.  
   The alter table move syntax also preserves the index and constraint definitions.

  alter index <index_name> rebuild; -- For all indexes

>>I have determined needs to be done in order to size down a datafile after receiving error ORA-03297..  

Are you asking or letting us know?  I personally don't think the shrink will do much here since it only consolidates data into blocks.  If you are going to do the MOVE, it seems like a wasted step.

i am asking what you need to do to avoid getting an ora-03297

This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.