Skip to Main Content
  • Questions
  • what is the difference between shrink ,move and impdp

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, ezio.

Asked: March 16, 2016 - 9:33 am UTC

Answered by: Chris Saxon - Last updated: September 14, 2020 - 2:13 am UTC

Category: Database - Version: 10g 11g

Viewed 10K+ times! This question is

You Asked

Hi:
I want to clean some space about some tables ,there are a few ways ,such as move ,shrink and impdp.
I want to know which one is better regardless of space consideration and assume this tables can use all those methods.

Can you answer my question from space ,speed and complex ?
thanks.

and we said...

The only function of shrink is reclaiming space in a table. It recovers space above and below the high water mark.

To do this you need to have row movement enabled.

SQL> create table t as
  2    select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 10000;

Table created.

SQL>
SQL> select bytes from user_segments
  2  where  segment_name = 'T';

     BYTES
----------
   6291456

SQL>
SQL> delete t where x < 9900;

9899 rows deleted.

SQL>
SQL> select bytes from user_segments
  2  where  segment_name = 'T';

     BYTES
----------
   6291456

SQL>
SQL> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL>
SQL> alter table t enable row movement;

Table altered.

SQL>
SQL> alter table t shrink space;

Table altered.

SQL> select bytes from user_segments
  2  where  segment_name = 'T';

     BYTES
----------
    131072


For more information see:

http://docs.oracle.com/database/121/ADMIN/schema.htm#ADMIN10161

The primary purpose of move is changing a table or partition's storage attributes. For example:

- Moving to a new tablespace
- Change compression settings
- Modify pctfree, initrans, etc.

As a side effect it can also reclaim space.

SQL> create table t as
  2    select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 10000;

Table created.

SQL>
SQL> select bytes from user_segments
  2  where  segment_name = 'T';

     BYTES
----------
   6291456

SQL>
SQL> delete t where x < 9900;

9899 rows deleted.

SQL>
SQL> select bytes from user_segments
  2  where  segment_name = 'T';

     BYTES
----------
   6291456

SQL>
SQL> alter table t move;

Table altered.

SQL>
SQL> select bytes from user_segments
  2  where  segment_name = 'T';

     BYTES
----------
    131072


For more details read:

http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN11659

Move appears to do the same as shrink, but with more options.

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. So you have to rebuild them afterwards:

SQL> create table t enable row movement as
  2    select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 10000;

Table created.

SQL>
SQL> create index i on t(x);

Index created.

SQL>
SQL> alter table t shrink space;

Table altered.

SQL>
SQL> select status from user_indexes
  2  where  index_name = 'I';

STATUS
--------
VALID

SQL>
SQL> alter table t move;

Table altered.

SQL>
SQL> select status from user_indexes
  2  where  index_name = 'I';

STATUS
--------
UNUSABLE


Shrink also moves the data in place. Move is out of place. So it requires additional space to complete. For large tables this could be a problem:

SQL> create tablespace small_ts datafile size 1M;

Tablespace created.

SQL>
SQL> create table sm tablespace small_ts enable row movement as
  2     select rownum x, lpad('x', 500, 'x') xx from dual connect by level <= 1000;

Table created.

SQL>
SQL> alter table sm move;
alter table sm move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace SMALL_TS


SQL> alter table sm shrink space;

Table altered.


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.

and you rated our response

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

a little more

March 17, 2016 - 12:52 am UTC

Reviewer: ezio

Hi Chris:
Thanks for answer me ,you said shrink is a online operation , it will lock table for a very little time.
so what about move , it will lock table when move all the time?
Connor McDonald

Followup  

March 17, 2016 - 2:24 am UTC

The table will be locked for the duration of the move.

Cheers,
Connor

Shrink vs Shrink Compact

April 13, 2016 - 4:22 pm UTC

Reviewer: S from KCMO

Since SHRINK causes a full table lock when it moves the HWM, is there any harm in running a SHRINK COMPACT during business hours, and then a SHRINK without the COMPACT during maintenance hours when it's acceptable for it to lock the entire table?
Chris Saxon

Followup  

April 14, 2016 - 1:38 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

December 01, 2017 - 3:23 pm UTC

Reviewer: Stefano G. from Italy

Cleary and easy to understand.
Thanks Tom!

Still relevant advice for 12c?

February 12, 2018 - 8:23 am UTC

Reviewer: Scott Wesley from Perth, Australia

I would like to know if this is still the recommendation for 12.1, regarding locking the table during shrink?

alter table images shrink space compact; -- do not lock table, run online
alter table images shrink space ; -- lock table, run when users aren't


The documentation doesn't specify locking
https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF01001

This document is old & 10g, but the logic behind the reason for the locking still seems sound?
http://www.oracle.com/technetwork/issue-archive/2005/05-may/o35tuning-096075.html

Cheers.
Connor McDonald

Followup  

February 13, 2018 - 1:12 am UTC

To my knowledge, shrink space functionality is unchanged. Under the covers we're still doing shifting rows around.

However, with 12.2 and the advent of 'alter table move online' you could of course do a more thorough job with similarly minimal impact on transactional activity.

Different potential use cases though.

Example 1:
==========
X=data
. = free space

<code>
XXXXXXXXXXXXXXX.........................................XX
<code>

I've got a small fraction of rows up at the high water mark, then a shrink space only needs to move a small fraction of rows. Small impact on resources.

Example 2:
==========

<code>
X.X.X.X.X.X.X.X.X.X...XXXX....XXXX.X.X.X.X.X.X.X.X.X.XXX..
<code>

I'm going to be doing a large amount of work here, so 'alter table move online' probably a better option.

Testing results

February 16, 2018 - 1:31 am UTC

Reviewer: Scott Wesley from Perth, Australia

Testing this without the COMPACT clause in 12.1 caused no impact to a lighter amount of users trying to insert new images, and read existing.
alter table images modify lob (image) (shrink space);

And it had a lot of work to do.
Connor McDonald

Followup  

February 16, 2018 - 1:47 am UTC

Agreed, but think this post was about shrinking space in a table, not a lob.

Well explained, except for this one thing about shrink

April 27, 2019 - 7:14 pm UTC

Reviewer: Khalid Rahim from MD

Hi,

Shrink and No Index Rebuild?

I get that you are saying that "shrink" moves the rows within the blocks, adjusts the HWM, etc. yet an Index Rebuild is not needed.

If the rows have moved the RowID must have changed, yet the indexes remain usable, how?

Would you please explain?

Thanks,
-- kr

Chris Saxon

Followup  

April 29, 2019 - 12:36 pm UTC

The shrink process updates the relevant index entries. What specifically do you want to know about this?

That's all

April 29, 2019 - 7:46 pm UTC

Reviewer: A reader

What you have just explained is good enough.

Connor McDonald

Followup  

April 30, 2019 - 2:27 am UTC

glad we could help

Shrink table / Rollback?

August 18, 2019 - 6:07 pm UTC

Reviewer: John Fisher from Orlando, FL

Can you interrupt an Alter table shrink compact if it has been running too long? If so do the rows get rolled back? Or do the rows that have been moved, remain? In other words, is each row movement a single transaction?
Connor McDonald

Followup  

August 19, 2019 - 6:55 pm UTC

Can you interrupt an Alter table shrink compact if it has been running too long?



Not every row, but batches of rows are 'checkpointed' so you won't "lose" the progress you have made so far. But alter table shrink is (can) be an online operation, so long running should not be an issue.

(Check for the docs for the rules on whether it is online or offline)

Thank you!

August 20, 2019 - 4:08 pm UTC

Reviewer: John Fisher from Orlando, FL USA

Thanks, none of the Oracle documentation I've found makes it sound like it is OK to interrupt this "DDL" operation. I had a concern about corruption if I interrupted it. I guess that can always happen, but I feel a little better now.

Problem I have is that I'm trying to recover approx 200+ gig of unused LOB space on an 11GR2 instance within some pretty active tables (JDE Job related tables -Basic Lobs) and the DB server setup doesn't tolerate too much extra processing during normal business hours. Just looking for the most efficient way to accomplish this with no downtime and no impact during business hours.

I'll get through it, but looks like its going to take some time.

Thanks for all you guys do!

Shrink + move?

August 21, 2019 - 1:42 pm UTC

Reviewer: Leandro Martins de Lima from Canada

Hello, masters.

I'm trying to resize some datafiles to give the unused space back to the OS, so I'm experimenting with these techniques.

Since we have too much data to manipulate and we're running out of physical space, creating new tablespaces is not an option; we have to reorganize things on theirs actual tablesapce/datafile.

The main problem is obvious: we cannot perform a direct datafile resize since there are bits of data at the end of the datafiles. So initially I thought about using the shrink on all objects in the tablespace, but as far as I understood it will only free the the non-used blocks held by the objects; it won't effectivelly move the blocks used by them. So, the block in use at the end of the datafile will not be moved and it will prevent the datafile resize. Is my assumption right?

Also, I found some problems with the shrink technique, like LONG columns and function based indexes

The the move (into the same tablespace) looks more efficient for what I'm aiming: it will reorganize the tables moving theirs blocks into the empty holes inside the datafile and freeing its tail, possibiliting a better datafile resize. Does it make sense to you?

Finally I decided to go with both operations: to optimize the usage of non-used space I perform a shrink on all possible objects and have theirs free blocks liberated, then I perform the move to reorganize the tables using all effectivelly free blocks inside the datafile. I still have some trouble with some specific datatype like LOBs, but this plan sounds good to me.

So, finally my questions: is this approach really good? The theory is interesting, but it's looks a lot of work and I'm not sure if there are better ways to achive what I'm trying here.
Do you have any adivice or maybe a better idea?

Thanks in advance.
Connor McDonald

Followup  

August 23, 2019 - 5:54 pm UTC

If you were on 12.2 you could do 'alter table ... move online' which will save a lot of issues.

If not, then dbms_redefinition might be a useful tool to explore

estimate benefit?

September 11, 2020 - 1:03 pm UTC

Reviewer: JVdB from Flanders, Belgium, Europe

Is there a way to calculate the gains that can be obtained by shriking / moving a table? In other words: Can the value 131072 bytes from the sample be calculated before doing the shrink?
This would be nice in order to focus on the low hanging fruit: tables and where most pace can be reclaimed.
Also: Can similar gains be predicted for coalescing an index?
Connor McDonald

Followup  

September 14, 2020 - 2:13 am UTC

You can use statistics to get a reasonable guess, eg

SQL> create table t as
  2  select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> select num_rows, avg_row_len,  blocks, empty_blocks
  2  from user_tables
  3  where table_name = 'T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS 
---------- ----------- ---------- 
   1663380         132      32241      


So you can see this table has 32241 blocks of space. Lets use the size of the *data* to make an estimate to see how close we get

SQL> select num_rows*avg_row_len/8192*100/(100-pct_free) est_blocks
  2  from user_tables
  3  where table_name = 'T';

EST_BLOCKS
----------
29780.5664


We come up a bit short, but we can use the ratio to make an assumption that that there's around an 8% overhead for block, thus:

SQL> select num_rows*avg_row_len/8192*100/(100-pct_free)*1.08 est_blocks
  2  from user_tables
  3  where table_name = 'T';

EST_BLOCKS
----------
32163.0117


so now we're pretty close. Obviously we're putting aside things like compression, how much of pctfree is consumed over time etc, but we're in the ballpark.

Now lets delete 1/3 of the rows.

SQL> delete from t
  2  where mod(object_id,3) = 0;

554620 rows deleted.


At this point - we want to make a guess as to how much space we would reclaim. So we'll gather stats

SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows*avg_row_len/8192*100/(100-pct_free)*1.08 est_blocks
  2  from user_tables
  3  where table_name = 'T';

EST_BLOCKS
----------
21438.9141


So our estimate is that after a reorg is that the table will be around 21438 blocks. Now we can see how close that was:

SQL> alter table t move;

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, avg_row_len,  blocks
  2  from user_tables
  3  where table_name = 'T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS 
---------- ----------- ---------- 
   1108760         132      21532            0


Not too bad.

You don't have to be super accurate here, because you'd probably only consider a space reclaim is the potential benefits were massive anyway.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database