[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Oracle shrink table and indexes

Posted on 2015-02-24
5
Medium Priority
?
1,371 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 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 14

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

591 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