How to reclaim space in oracle datafile

sqlnewbie08
sqlnewbie08 used Ask the Experts™
on
We have some tablespaces that are sized larger then needed.  I am aware you can shrink the table and of course you can 'resize' a datafile.  But, sometimes oracle will not let you resize the data file because objects are fragmented.  What needs to be done in order to be able to resize down a datafile?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
2 lines of code:

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

Author

Commented:
doesn't the high water mark need to be raised in order to be able to resize a datafile smaller?
Top Expert 2011
Commented:
since you are using oracle 10xxx, try these commands first to adjust HWM.

ALTER TABLE emp ENABLE ROW MOVEMENT;
ROWIDs are normally assigned to a row for the life time of the row at insert time.

After we have given Oracle the permission to change the ROWIDs
we can now issue a shrink statement.
ALTER TABLE tableName SHRINK SPACE;

The ENALBE ROW MOVEMENT will compact the segment.

 The SHRINK step will adjust the high watermark.

Then you try the two initial statements I posted.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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?

Commented:
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.

Author

Commented:
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


Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
i am asking what you need to do to avoid getting an ora-03297
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
It looks like shrink might work after all based on the following link.

I would set up some tests and try 1 and 2.  If that fails I doubt #3 will work and you might have to use move.

http://www.dba-oracle.com/t_ora_03297_file_contains_used_data_beyond_requested_resize_value.htm


Phillip Bracken notes these solutions on the ORA-03297 error:

"You are trying to shrink the tablespace below its high water mark. If you are running Oracle 10g and are using locally management tablespaces with Automatic Storage Management and row movement enabled on the tables, you could issue an Alter Table Shrink.

If you are not at Oracle 10g or are not using locally managed tablespaces, the only way around this is to drop or move the tables or indexes containing extents above region in the tablespace you are trying to resize it to, or try a different size that is not below the tablespace's high water mark."
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial