?
Solved

Oracle shrink table and indexes

Posted on 2015-02-24
5
Medium Priority
?
1,233 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 1200 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 800 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

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

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.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…
Suggested Courses

764 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