patternMinor
Is shrink a safe online operation?
Viewed 0 times
shrinkoperationsafeonline
Problem
We are planning to run the following space management command:
on a production database (Oracle 10g). The
I am wondering if running the
Can you clarify the impact of the
alter table XXX shrink space cascadeon 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?Solution
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
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
Context
StackExchange Database Administrators Q#43038, answer score: 4
Revisions (0)
No revisions yet.