alter table … shrink space

A customer deleted 30 million rows from a table and now wants to shrink the table. This sent me doing some research about online shrinking of tables. Not the type of research that you get Noble prize for, but it may interest someone.

The idea behind shrinking is that if you delete half the rows in the table, you’ll end up with many partially empty blocks below the high water mark. If you are planning to grow the table again, this is not a problem, the space will get used eventually. However, if you are not planning to insert many rows to this table, you may want to reuse these blocks for other tables in the same tablespace. Also, full table scans go over all the blocks below the high water mark , so if many of these blocks are empty, you are doing more IO than you need, which leads to slower table scans.

So, we decided that shrinking the table is desirable, but how do we do it? Before 10g, shrinking was an ordeal which involved exporting the table, dropping it and importing it back. That causes a downtime and therefore was rarely done.
In 10g it is as easy as:
alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;

Behind the scenes Oracle will do a bunch of deletes and inserts that will move the existing rows into the empty spaces, thus shrinking the table. Note that this generates redo just like any other DML operation.

If you run ADDM, the segment advisor will tell you which tables can benefit from shrinking. You can read the advice from the latest run by running the following query:

select * from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
order by reclaimable_space desc

I tried shrinking a table on my test machine, just to make sure the process is clear:

create table t1 (x number);

for i in 1..1000000 loop
insert into t1 values (i);
end loop;

select (count(*)*52428800)/1024/1024 from v$log_history --> 1050 (it was 800 before the inserts)

delete from t1 where mod(x,2)=0 --deleting half the rows in the table

select (count(*)*52428800)/1024/1024 from v$log_history --> 1200

select count(*) from t1;

--auto trace shows: consistent gets 1640

alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;

select (count(*)*52428800)/1024/1024 from v$log_history --> 1300

select count(*) from t1;

--auto trace shows: consistent gets 837


14 Comments on “alter table … shrink space”

  1. dhoogfr says:


    Don’t forget that you can only use the shrink command for segments located in tablespaces with automatic segment space management.
    According to the documentation following limitations also apply:

    * You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

    * Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.

    * This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.

    * You cannot specify this clause for a compressed table.

    * You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

    Another thing you might consider is that shrinking the table (because of the row movement) can have a high impact on the clustering factor of indexes for this table.

  2. Venz says:

    Also not to forget:

    Because of moving the data to other blocks the rowid may change! So if you performing this online, be sure that no program is currently working on the table which uses rowids!

    Another thing: If you performing this against or with cascade be sure that you applied patch 5636728 before. Otherwise LOBs that may are in the table getting corrupt any you won’t be able to fix them!

  3. prodlife says:

    Thanks Freek and Venz!

    Seems like shrinking has many traps.
    The bug that can corrupt LOBs is especially important information.
    Also, I didn’t think about the indexes cluster factor – thats an important bit to check. If we do many range scans, we can end up harming performance more than helping.

  4. Rudy says:

    I always shrink indexes only. I leave table shrinking to maintenance windows.
    The LOBs bug is very sad. Good to know.

  5. adeel says:

    i’m not agree with you as you said “this is not a problem, the space will get used eventually.”plz test this let me know either you are right or me

    Create tablespace “Test” with one data file
    Initial sizes of data file 10MB
    Auto extent 100 KB MAX size 25MB
    Create a user whose default tablespace is test temporary is TEMP
    Now I created a table as
    create table t1 (x number);
    for i in 1..1000000 loop
    insert into t1 values (i);
    end loop;
    it take 13.1MB as show EM space on disk be remember my max size 25MB
    Now I delete half record like this
    delete from t1 where mod(x,2)=0

    but HWM remain at its position now I create another table t2 and try to insert the same number of record in t2 but it give error unable to extent tablespace. It mean when HWM could not allocate empty block it doesn’t used the space which is below the HWM. Now I issued these command
    alter table t1 enable row movement ;
    alter table t1 shrink space;

    After this I insert same number of record in T2. it inserted successfully.
    Oracle used never used space below the HVM. Am I right??? if No let me know

  6. prodlife says:

    I wrote “If you are planning to grow the table again, this is not a problem, the space will get used eventually.”

    When I said “grow the table”, I mean “grow the same table from which you deleted rows”. In your example, that would be adding rows to T1.

    When you create T2 and insert rows there, you create a new segment to which separate extents are allocated. The new table has its own HWM, separate from that of T1, and the new extents will need their own space to grow.

    If you’ll try adding rows to T1 you’ll see that you can insert the same number of rows without allocating more space, because there will be enough space below HWM.

  7. adeel says:

    Thanks for correction you are right. I’ve post the same question on ONT and People suggest me to carefully read about PCTFREE and PCTUSED and after study I’ve realized that where I was making mistake. Thanks fro your reply.
    But one thing more about which I’m still confused. About this problem I often Post my comments on OTN but solution found. My confused about Temporary Tablespace with Shrink command. I’m currently hired as a DBA. I’ve just completed my OCP so I’m newbie in oracle. Where I’m DBA this company worked on DW application where million of records process. My application developed in DOTNET. When application load data in database using a SQL loader with direct path it take 17 Hour to load 8million record with 91 columns. I’m also trying to find the solution why it takes so long but my current problem is temporary tablespace. Whenever we load data I get reach it maximum size 32GB which is maximum limit of 32bit on widows when loading process end all session released the space. EM show size of data file 32GB and used space 0.00MB. but when we start another loading it failed with error unable to extent temp tablespace. I’ve have to shrink temp space after each loading. My question is as in the case of tables same rule apply for temp too. in my understanding HWM still set in temp file and another loading process cannot use that space as just like T1 space. Am I right? but in documentation I’ve read that when a transaction complete it process it release temp space. But the (stupid) HWM still remains there. In there any solution?? PLZ help as you know oracle performance and tuning is the heart area of oracle and quite hard for beginner.

  8. Chen Shapira says:


    1) If you direct load data into a table, it will only insert data above HWM. Direct load cannot reuse empty space in a table.
    2) Temp tables can be created either with “on commit delete rows” or “on commit preserve rows” this will control what will happen to the space at the end of the transaction. In any case, the space should be released at the end of the session.
    3) You can check for space usage like this:
    4) Note that after you finished your transaction/session, the space will be marked as free, but it will not be released (i.e. file will remain 32G). You can read about this here:

  9. adeel says:

    as you said the space will be marked as free but it will not be released ture but plz tell me how can overcome this problem. after loading data using direct load temp tablespace reach it max size. in next loading session i ve to shrink tablespacein 11g or in 10g i have to use drop and recreate temp tablspace. is there anyway to avoid such activity. i have read documentation many artical but unable to find solution plz if you can help what i need to inspect??

  10. adeel says:

    hello miss how are you? i have a question to could you plz tell me when we run segment advisor to shrink space. is there anyway how to check how much space we reclaim sapce in this process can we check it becoz os Level file size does not change

  11. HappyOra says:

    Hi.. it’s good. But I wonder.. Cant I Use ‘alter table t1 shrink space cascade’ In Busy Time(many DML) ?

    • prodlife says:

      I never tried that, but it sounds like a bad idea… Shrink can be quite heavy operation in itself (lots of IO).

  12. Debabrata Choudhury says:


    I had a lots of confusion , sharp difference between the COALESCE and SHRINK clause to be used in Oracle 10g.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s