Shrinking Size of Tables in Oracle

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

Who is Participating?
mrjoltcolaConnect With a Mentor Commented:
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.
OP_ZaharinConnect With a Mentor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.