Shrinking Size of Tables in Oracle

Posted on 2011-04-21
Last Modified: 2012-05-11
Dear experts,

I recently performed a large delete in tables of an Oracle database. The database is used in production and should be always online. (7x24) Which of the following methods would you recommend me to use to shrink size without corrupting indexes and disturbing on-going operations?

1.Create Table As Select

2.Online Reorg Using DBMS_REDEFINITION

3.Alter Table Move

4.Alter Table Shrink Space

Question by:GurcanK
    LVL 23

    Assisted Solution

    hi GurcanK,
    - to Recreate, Move or to Shrink? objective: reclaim free segment.

    - you could do the re-creating table using the create table as select, then drop the old table. you also could create a new tablespace, move the table to the new tablespace and drop the old one, this can be done with nologging and parallel. both of these approach however, requires downtime and requires re-indexing. the downtime is no go because you are in production environment.

    - what about shrink? it requires no downtime, can be done online but there are restrictions on which table can be shrink. the UNDO, Temporary segments, LONG datatype-context index-LOB index tables are among those cannot be shrink. expect heavy DML transaction, therefore UNDO tablespace will grow fast. it might/might not effect to your database performance when the operation is running.

    - if you want to go with shrink, i would suggest you to look into Segment Advisor utility in the Enterprise Manager. you can use the wizard to search for shrink candidates and it offers comprehensive recommendations.
    LVL 40

    Accepted Solution

    First, it is important that you note which version of Oracle is in question here. Some options are not available with older versions.

    1 - is only an option if you can allow 1-2 seconds of downtime while renaming tables in the final step. But this option requires good testing, and you must ensure to synch up any changed rows after the tables are switched, since there could be DML going on during the initial create of the copy that won't be committed. Basically this is the manual way to accomplish what DBMS_REDEF does for you except REDEF doesn't require downtime at all, so is superior.
    3 - is NOT an option in your case, a move locks the table during the move, and indexes will need to be rebuilt.

    Your only sure options to ensure no downtime is 2 and 4
    2 - DBMS_REDEF - No downtime, but a little complex to remember syntax, unless you use Enterprise Manager or another tool. I've used it successfully in production environments.
    4 - alter table shrink space - 10gR2+ feature. Requires table to be in ASSM tablespace. No downtime, but possibly the slowest option of the two.

    I'd start by trying (4), the shrink is the simplest. If you find it has poor performance, try the REDEF and/or call Oracle. During either one of these make sure you have plenty of archive space, especially the shrink.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now