Solved

Alter Table Shrink Space Question

Posted on 2013-06-10
4
760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

615 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