Logo Background

Alter Table Shrink Space In Oracle

  • By on August 13, 2010 | 4 Comments

    Oracle has alter table shrink space command to reclaim unused disk space for objects (tables and indexes). This command available from version 10g ease the DBA job from doing table export and import which needs downtime.

    It’s recommended to run the alter table shrink space during off-peak hour with less database activity. DBA needs to know what data segments experience HWM (high watermark) in order to reclaim free space to the database and shrink segments.

    A shrink could fail because the tablespace is not locally managed or do not have ASSM (automatic segment managed) enabled, the table has a column of long, row movement has not been enabled or it is a clustered table.

    • Segment shrink is allowed only on segments whose space is automatically managed
    • Heap-organised, index-organised and cluster tables can be shrunk
    • ROW MOVEMENT must be enabled for heap-organised tables
    • Chained rows may be repaired during a segment shrink operation
    • Triggers are not fired during a segment shrink operation
    • Tables with ROWID-based materialized view cannot be maintained
    • IOT mapping tables and overflow segment cannot be shrunk
    • Tables with function-based indexes cannot be shrunk

    Alter Table Shrink Space Syntax

    Perform the alter table shrink space command on the table that has been housekeep by the support team.

    SQL> ALTER TABLE schema.table_name enable ROW movement;
     
    SQL> ALTER TABLE schema.table_name shrink SPACE;
     
    SQL> ALTER TABLE schema.table_name disable ROW movement;

    It’s not guaranteed that all chained rows will be fixed because not all blocks may be read in a segment shrink operation.

    However there’s benefits of a shrink operation which are:

    • Full table scans will take less time (a table scan will always scan up to the HWM even if space is not used)
    • Better index access takes place because of a smaller B-Tree
    • Space is freed up for other database objects
    • Space below the HWM is released and the HWM is moved down
    Previous
    Next
    » Gather Schema Statistics In Oracle
  1. #1 andy
    August 13, 2010 8:15 pm

    Yes, the shrink command will shrink the size of the table and reduce the high water mark (HWM) of the table.

    If this is the only table that sits inside the tablespace, then the datafile(s) of the tablespace can be resize and make it smaller.

    However, if there are multiple tables that sits in the tablespace, then it might not be possible to shrink the size of the datafiles cause some tables might still be occupying the extent at the end of the datafile.

    Table shrink would however should reduce the size of the table (if you really did alot of housekeeping), you can check the size of the table from dba_segments/user_segments.

    If you are running out of storage, then it’s better to do shrink all the tables in that tablespace that you wish to reduce the size.

    Post ReplyPost Reply
  1. #2 ashok
    August 18, 2010 10:31 pm

    If you are going to reuse back the space then there’s no need to re-org.

    However, check this out. APPEND into tables with nologging – By using the APPEND hint, Oracle always grabs “fresh” data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don’t need to specify an APPEND hint.

    Post ReplyPost Reply
  1. #3 oracle
    August 25, 2010 9:24 pm

    How about using DBMS_REDEFINITION? It might produce the same result like alter table shrink space command but you don’t have to drop the function-based index to do so. As below is the sequence of action taken to re-org the table in DEV environment. You may refer but I strongly suggest to perform few round of testing before doing in PROD instance.

    1. Check if redefinition is possible

    SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘XLA’, ‘XLA_AE_HEADERS’);

    2. Create new empty interim table with the same column name as the original table

    SQL> CREATE TABLE XLA_AE_HEADERS_NEW
    (…)

    3. Start the redefinition process

    DBMS_REDEFINITION.start_redef_table (
    uname IN VARCHAR2,
    orig_table IN VARCHAR2,
    int_table IN VARCHAR2,
    col_mapping IN VARCHAR2 := NULL);

    SQL> EXEC DBMS_REDEFINITION.start_redef_table (‘XLA’, ‘XLA_AE_HEADERS’, ‘XLA_AE_HEADERS_NEW’);

    4. Apply captured changed to interim table

    DBMS_REDEFINITION.sync_interim_table (
    uname IN VARCHAR2,
    orig_table IN VARCHAR2,
    int_table IN VARCHAR2);

    SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE (‘XLA’, ‘XLA_AE_HEADERS’, ‘XLA_AE_HEADERS_NEW’);

    5. Add constraints, indexes, triggers, grants on interim table…

    SQL> CREATE INDEX, TRIGGERS etc (name must be different from original)

    6. Complete the redefinition process

    DBMS_REDEFINITION.finish_redef_table (
    uname IN VARCHAR2,
    orig_table IN VARCHAR2,
    int_table IN VARCHAR2);

    SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (‘XLA’, ‘XLA_AE_HEADERS’, ‘XLA_AE_HEADERS_NEW’);

    7. Cleanup the temporary table (confirm it is the old table before drop)

    SQL> drop table xla_ae_headers_new

    8. Rename all the indexes to original index name.

    Post ReplyPost Reply
  1. #4 rajesh
    June 19, 2012 9:17 pm

    Here’s the steps we took to complete the online table re-org.

    1. Check if redefinition is possible

    SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘XLA’, ‘XLA_AE_HEADERS’);

    2. Create new empty interim table with the same column name as the original table

    SQL> CREATE TABLE XLA_AE_HEADERS_NEW
    (…)

    3. Start the redefinition process

    SQL> EXEC DBMS_REDEFINITION.start_redef_table (‘XLA’, ‘XLA_AE_HEADERS’, ‘XLA_AE_HEADERS_NEW’);

    4. Copy table dependants from original table to interim table

    SQL> variable nerrors number
    1 begin
    2 dbms_redefinition.copy_table_dependents
    3 ( ‘XLA’, ‘XLA_AE_HEADERS’, ‘XLA_AE_HEADERS_NEW’,
    4 copy_indexes => dbms_redefinition.cons_orig_params,
    5 num_errors => :nerrors );
    6 end;
    7 /

    5. Apply captured changed to interim table

    EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE (‘XLA’, ‘XLA_AE_HEADERS’, ‘XLA_AE_HEADERS_NEW’);

    6, Finish the redefinition process

    EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (‘XLA’, ‘XLA_AE_HEADERS’, ‘XLA_AE_HEADERS_NEW’);

    7. Drop interim table

    SQL> drop table XLA_AE_HEADERS_NEW;

    8. Gather stats on the original table

    SQL> EXEC dbms_stats.gather_table_stats(‘XLA’,’XLA_AE_HEADERS’, estimate_percent => ’10’, cascade=>TRUE);

    Post ReplyPost Reply
Leave a Comment