So why would you choose shrink over move? Shrink is an online operation. It will maintain any indexes on the table this is running. With move, the indexes become unusable. Move is out of place. So it requires additional space to complete. Impdp is a way to move data between databases. To import back into the same database, either you need to: - Export, drop table, import or - Export, import to new table, switch new table and old Either way there's more work than either shrink or move.
And you'll probably need an outage to complete the process. So while it may reclaim space in most production system this method is infeasible. Rating 13 ratings Is this answer out of date?
You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels.
And of course, keep up to date with AskTOM via the official twitter account. More to Explore Administration Need more information on Administration? Check out the Administrators guide for the Oracle Database. Questions what is the difference between shrink ,move and impdp. Question and Answer.
You Asked Hi: I want to clean some space about some tables ,there are a few ways ,such as move ,shrink and impdp. Hi Chris: Thanks for answer me ,you said shrink is a online operation , it will lock table for a very little time. March 17, - am UTC. The table will be locked for the duration of the move. Cheers, Connor. April 14, - am UTC. Yes, that is the intent. Get "most" of the work done online, and thus reduce the length of time during which you'll need the full lock later. Very useful Stefano G.
Still relevant advice for 12c? I would like to know if this is still the recommendation for February 13, - am UTC. To my knowledge, shrink space functionality is unchanged. Under the covers we're still doing shifting rows around. However, with Different potential use cases though.
Small impact on resources. February 16, - am UTC. You can't move clustered objects from one tablespace to another, for that you will have to use expdp. So I will suggest expdp is the best option to move all objects to different tablesapce. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more.
Asked 6 years, 11 months ago. Active 3 years, 4 months ago. Viewed 25k times. I have another Tablespace TS2. Improve this question. Add a comment. Active Oldest Votes. Improve this answer. Marco Baldelli Marco Baldelli 3, 1 1 gold badge 22 22 silver badges 27 27 bronze badges.
First one is just move.. There are four ways to do that: A. Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause.
Further, if your application has any rowid-based triggers, you should disable them before issuing this clause. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the High Water Mark, and releases the recuperated space immediately.
The step to adjusts the high water mark is a DDL operation and requires a lock on the table. In high load DML Online environments, this might be an issue, and it would take a long time to perform this step. Segment data is compacted. High-water mark HWM is adjusted and unused space is deallocated from the segment.
0コメント