Solved

Oracle shrink table and indexes

Posted on 2015-02-24
5
897 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
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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 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
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now