Solved

Oracle shrink table and indexes

Posted on 2015-02-24
5
1,093 Views
Last Modified: 2015-03-12
Hi experts,
I'd like to reclaim wasted space from table DT_TABLE with an online segment shrink of the table and its indexes, below the output from DBA_INDEXES:
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLE_TYPE                                                                                                                                                                                                                                                                                                                                                                                                                
---------------------------- --------------------------- ------------------------------ -----------                                                                                                                                                                                                                                                                                                                                                                                                               
IDX_TB24                    FUNCTION-BASED NORMAL       	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB18                    FUNCTION-BASED NORMAL       	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB10                    FUNCTION-BASED NORMAL       	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB4                     FUNCTION-BASED NORMAL       	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB9                     FUNCTION-BASED NORMAL       	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB13                    FUNCTION-BASED NORMAL       	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB15                    FUNCTION-BASED NORMAL       	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB20                    NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB21                    NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB22                    NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB23                    NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB1                     NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB7                     NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB11                    NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB2                     NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB3                     NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB5                     NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB6                     NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB8                     NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB12                    NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     
IDX_TB17                    NORMAL                      	DT_TABLE                		TABLE                                                                                                                                                                                                                                                                                                                                                                                                                     

Open in new window

I can't run this script;
ALTER TABLE DT_TABLE ENABLE ROW MOVEMENT;
ALTER TABLE DT_TABLE SHRINK SPACE COMPACT;
ALTER TABLE DT_TABLE SHRINK SPACE CASCADE;
ALTER TABLE DT_TABLE DISABLE ROW MOVEMENT;

because in the Oracle document it say:
Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.

What steps should I take to make the shrink of this table and its indexes? (This is a production environment)

Thanks in advance!
0
Comment
Question by:ralph_rea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 300 total points
ID: 40630046
Did you try with the procedures/functions in the "DBMS_REDEFINITION" package ?

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN11677 ---> this talks about using  dbms_redefinition package

http://www.orafaq.com/node/1936 --> also you can refer to method 4 which talks about dbms_redefinition usage to shrink space
0
 

Author Comment

by:ralph_rea
ID: 40630224
Hi,
I did not understand well how this DBMS_REDEFINITION package.

Can you, please, write me the steps in my case?
0
 

Author Comment

by:ralph_rea
ID: 40630237
I think the solution is:
 
drop function-based indexes
 shrink table without function-based indexes
shrink indexes NORMAL
re-create function-based indexes

Is correct?
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 40630648
"I think the solution is:..."
Correct.  Those steps will work.  However, these steps involve some application downtime (or at least possibly very slow response times) if those function-based indexes are normally used.

Using the procedures in the dbms_redefinition package can often provide a way to make structural changes to a table with very little production downtime needed.  But, there are some disadvantages and limitations to this approach.  Basically, this involves making a copy of the table and its indexes, triggers, constraints, etc. including whatever new features that you want.  This approach also creates triggers and a materialized view to keep the new copy in sync with the current table until you are ready for a very brief service interruption that basically renames the current and new table.  So, you temporarily need at least double the space of this table and its indexes to use this approach.  Try this in a test environment before you try it in production!  It has not always worked for me.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40630689
What also might be of interest (for the future) is: Edition based redefinition. I'd suggest, you take a look at this great "feature" (and no, it's not "only EE" enabled)... Regarding tables, it woul take some pre-req in your case (see editionable views), but it's definitely worth to take a look ;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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