Solved

Oracle shrink table and indexes

Posted on 2015-02-24
5
1,130 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 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