Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are planning to run the following space management command:

alter table XXX shrink space cascade

on a production database (Oracle 10g). The XXX table is a transactional table heavily used for updates/inserts/deletes by many applications.

I am wondering if running the shrink command during the peak time (online) will cause a severe performance degradation, or may lock the table and so block all the applications?

Can you clarify the impact of the shrink command on the access to the databese table XXX?

share|improve this question

migrated from stackoverflow.com May 25 '13 at 7:49

This question came from our site for professional and enthusiast programmers.

1  
Regardless of whether it's safe or not, don't do segment administration during peak hours. That's just not a good practice at all. –  Mat May 25 '13 at 8:04

1 Answer 1

As I understand it the resizing of the high water mark requires an exclusive table lock, which in theory might not be a problem as it would be of very short duration. However if it has to wait on other transactions before it gets the lock it will block other transactions.

https://forums.oracle.com/forums/thread.jspa?threadID=2453756

However as the docs point out, you could run a shrink space compact, which does not move the HWM, and run the shrink later on when the system is known to be quiet: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2192484

share|improve this answer
    
Nice answer +1 @David Aldridge –  jim mcnamara May 24 '13 at 19:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.