Skip to Main Content
  • Questions

  • what is the difference between shrink ,move and impdp

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, ezio.

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

Last updated: July 20, 2023 - 2:38 pm UTC

Version: 10g 11g

Viewed 50K+ 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 Chris 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.

Rating

  (17 ratings)

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

Comments

a little more

ezio, March 17, 2016 - 12:52 am UTC

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
March 17, 2016 - 2:24 am UTC

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

Cheers,
Connor

Shrink vs Shrink Compact

S, April 13, 2016 - 4:22 pm UTC

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
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

Stefano G., December 01, 2017 - 3:23 pm UTC

Cleary and easy to understand.
Thanks Tom!

Still relevant advice for 12c?

Scott Wesley, February 12, 2018 - 8:23 am UTC

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
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

Scott Wesley, February 16, 2018 - 1:31 am UTC

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
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

Khalid Rahim, April 27, 2019 - 7:14 pm UTC

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
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

A reader, April 29, 2019 - 7:46 pm UTC

What you have just explained is good enough.

Connor McDonald
April 30, 2019 - 2:27 am UTC

glad we could help

Shrink table / Rollback?

John Fisher, August 18, 2019 - 6:07 pm UTC

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
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!

John Fisher, August 20, 2019 - 4:08 pm UTC

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?

Leandro Martins de Lima, August 21, 2019 - 1:42 pm UTC

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
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?

JVdB, September 11, 2020 - 1:03 pm UTC

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
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.

Details on "checkpointing" during SHRINK

Mike Tefft, February 03, 2021 - 1:22 pm UTC

In one of your responses you said:
> but batches of rows are 'checkpointed' so you won't "lose" the progress you have made so far.

Can you provide any reference to this behavior? Your comment is very encouraging but if this is documented at all it would be even better. (We can't really investigate this ourselves.) Is there any way to measure progress on an incomplete SHRINK COMPACT operation?

I need to perform a shrink on an enormous SYS.AUD$. If I can have confidence that doing repeated interrupted SHRINK COMPACT operations is actually making progress, it would be very helpful.
Connor McDonald
February 04, 2021 - 3:30 am UTC

Not documented unfortunately...but easy to demonstrate


SQL>
SQL> create table t tablespace largets as
  2  select rownum x, rpad(rownum,1000,'x') y
  3  from
  4  ( select 1 from dual connect by level <= 1000 ),
  5  ( select 1 from dual connect by level <= 2000 );

Table created.

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

666666 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> select c, count(*)
  2  from (
  3  select dbms_rowid.ROWID_BLOCK_NUMBER(rowid), count(*) c
  4  from t
  5  group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
  6  )
  7  group by c
  8  order by 2 desc;

         C   COUNT(*)
---------- ----------
         5     190476
         4      95238
         2          1

3 rows selected.


So you can see that most of my blocks have 4 or 5 rows in them, which makes sense because I would have started with 7 or 8 per block (1000 bytes per row) and then I deleted one third of them. Now in another session I ran

alter table t enable row movement;
alter table t shrink space compact;

and waited 5 seconds before killing the session. Let see how it looks now:

SQL> select c, count(*)
  2  from (
  3  select dbms_rowid.ROWID_BLOCK_NUMBER(rowid), count(*) c
  4  from t
  5  group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
  6  )
  7  group by c
  8  order by 2 desc;

         C   COUNT(*)
---------- ----------
         5     148429
         4      74215
         7      42047

3 rows selected.

SQL>


You can see that part of the job is done. I fire up another session and run it again this time to completion

SQL> alter table t shrink space compact;

Table altered.

SQL> select c, count(*)
  2  from (
  3  select dbms_rowid.ROWID_BLOCK_NUMBER(rowid), count(*) c
  4  from t
  5  group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
  6  )
  7  group by c
  8  order by 2 desc;

         C   COUNT(*)
---------- ----------
         7     190475
         4          1
         5          1

3 rows selected.


and we're done

Row movement side-effects

Frank, March 03, 2021 - 9:45 am UTC

Being still on 11.2 I consider the best way to reorganize a 1+ TB database. I'd like to avoid Data Pump and do things online.

The Space Advisor tells me to enable row movement and shrink tables and indexes which will at least free up space inside the objects, but how can I know if enabling row movement will cause problems with some queries?

I may also wait until an upcoming upgrade to 12c to finalize using "move online". Just considering the options for now.
Chris Saxon
March 03, 2021 - 5:54 pm UTC

It's unlikely to cause your queries any issues.

If you're querying rowids directly, it's possible you could hit problems if you run shrink or other row moving operations at the same time. Hopefully you're not doing that though ;)

shrink and chained rows

Greg Mistler, September 29, 2022 - 9:33 am UTC

Hi Chris, thanks for all those explanations,

I try to figure out if the shrink resolves chained rows?

Thanks for your help.
Connor McDonald
September 30, 2022 - 4:27 am UTC

Need to be careful here with terminology with "chained" versus "migrated"

See this video for details



So shrinking will help migrated but not chained

shrink table

Youhengnchhieng, July 18, 2023 - 1:53 am UTC

does oracle database can shrink table space without drop the function-based-index?
Connor McDonald
July 18, 2023 - 3:59 am UTC

No, you would need to drop the index and then recreate it afterwards.

SQL> create table t as select * from emp;

Table created.

SQL> create index tix on t ( upper(ename));

Index created.

SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object

SQL> alter index tix unusable;

Index altered.

SQL> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object


SQL> drop index tix;

Index dropped.

SQL> alter table t shrink space;

Table altered.


Reclaim The Unused space

Youhengnchhieng, July 18, 2023 - 8:50 am UTC

If I want to reclaim the unused space from table by using move if it possible without dropping the function-based-index?
Chris Saxon
July 18, 2023 - 12:33 pm UTC

Yes:

create table t (
  c1 int
);
--Table T created.

insert into t values ( 1 );
--1 row inserted.

create index i on t ( c1 + 1 );
--Index I created.

alter table t move online;
--Table T altered.

Shrink Table with Function-based-index

ChhiengYouheng, July 20, 2023 - 2:01 am UTC

If it possible I want to shrink table with function-based-index without drop it. can we make index to unusable after that shrink and rebuild the index. pls help assist me can we do this way?
Chris Saxon
July 20, 2023 - 2:38 pm UTC

Moving the table will reclaim space - you can do this with an FBI on it. If you do this ONLINE, the index will still be usable afterwards. If you omit this it will be UNUSUABLE.
more

Connor and Chris don't just spend all day on AskTOM. 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