Results 1 to 6 of 6

Thread: Oracle Shrink

  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Oracle Shrink

    Hi experts,
    I'd like to reclaim wasted space from table DT_TABLE with an online segment shrink of the table and indexes, below the output from DBA_INDEXES:

    Code:
    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
    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!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'd like to reclaim wasted space from table DT_TABLE
    why is there "wasted space" within this table now?

    post SQL & results that show how much wasted space now exists; both is absolute number & on percentage basis.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by anacedent View Post
    >I'd like to reclaim wasted space from table DT_TABLE
    why is there "wasted space" within this table now?

    post SQL & results that show how much wasted space now exists; both is absolute number & on percentage basis.
    I'd like just to Know How Can I shrink the table with function-based indexes.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by raf View Post
    I'd like just to Know How Can I shrink the table with function-based indexes.
    I'd like to know the answers to my questions.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What makes you think you have excessive wasted space. I have been a developer/DBA since Oracle 6 and the number of tables that truly needed to be shrunk I could count on one hand,
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How big is the table, and how long is the maintenance window? You could always take the sledgehammer approach by dropping the function based indexes, shrinking the table, and rebuilding them. It takes time, of course, but that is just going to be part of the cost/benefit analysis.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •