Shrinking Size of Tables in Oracle

Posted on 2011-04-21
Medium Priority
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

OP_Zaharin earned 800 total points
ID: 35446262
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

mrjoltcola earned 1200 total points
ID: 35448007
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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

749 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