Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

Alter Table Shrink Space Question

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
gswitz
Asked:
gswitz
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
gswitzAuthor Commented:
Thanks, SlightWV
0
 
sdstuberCommented:
yes it does require locks and existing transactions with locks on the table will prevent it from running
0
 
sdstuberCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now