?
Solved

Alter Table Shrink Space Question

Posted on 2013-06-10
4
Medium Priority
?
788 Views
Last Modified: 2013-06-10
If I execute an Alter Table Shrink Space and it takes hours, will it lock the table so that the table cannot be INSERT UPDATE or DELETE?

Thanks!!
0
Comment
Question by:gswitz
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39235089
The docs talk about this:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/schema003.htm#ADMIN10161

Shrinking Database Segments Online

Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operations are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.
0
 

Author Closing Comment

by:gswitz
ID: 39235092
Thanks, SlightWV
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39235098
yes it does require locks and existing transactions with locks on the table will prevent it from running
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39235109
oops too slow  and interesting because it's a simple test case will show that DML does create blocking locks


-- in session 1
alter dept set deptno=deptno;

-- in session 2

alter table dept shrink space;   -- this statement will wait

-- in session 1

rollback;  -- or commit

-- in session 2

-- the shrink will now continue
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question