Select Page

Removing Advanced Compression from Large Tables with DBMS_REDEFINITION

Steve Thompson | | March 28, 2019

Problem:

One of Datavail’s customers had found there were some tables using advanced compression features on tables, but did not have a license. We had previously tried to perform an ‘Alter Table Move…’ operation, but two tables were so large they couldn’t be moved during the outage window provided by the business. We needed to find a way to remove advanced compression to get the customer’s licensing current, but with minimal impact to the business.

Solution:

Oracle has provided a package name dbms_redefinition, that can be used to perform operations on a table while keeping it online.

 

Here are the steps to follow:

  1. Verified that table could be redefined:
    can_redef_table
    (uname=’<table_owner>’
    , orig_table=>’<table_name>’
    , options_flag=>’cons_use_pk or cons_use_rowid’); <= optional if table has PK con_use_rowid if no PK on table.
  2.  

  3. Validated space is available prior to beginning compression removal.
    Assumption – that table would grow to double in size as an estimate.
  4.  

  5. Created separate undo tablespace for process, to prevent impacting other database transaction undo usage.
  6.  

  7. Create interim table that mirrors original, but without compression.
  8.  

  9. Next part is to run the compression itself.

     

    ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
    ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;
    ALTER SESSION ENABLE RESUMABLE TIMEOUT 86400;
    ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS3;

     

    begin
    dbms_redefinition.start_redef_table
    ( uname=>'<table_owner>',
    orig_table=>'<table>',
    int_table=>'<interim_table>', <= uncompressed table
    options_flag=>dbms_redefinition.cons_use_rowid  <= table doesn’t have a primary key defined.
    );
    end;
    /

     

    During this run, you can validate the original table doesn’t have a lock on it allowing the application and users to access it as normal.

     

    select b.owner, b.object_name, a.locked_mode
    from v$locked_object a, all_objects b
    where a.object_id = b.object_id
    /

     

    OWNER
    ------------------------------
    SYS
    <TABLE_OWNER>
    OBJECT_NAME
    ------------------------------
    SNAP$
    <INTERIM_TABLE>
    LOCKED_MODE
    -----------
    3
    6
  10.  

  11. After table redefinition has completed, now is the time to sync up all the table updates that happened during redefinition process.

     

    EXEC DBMS_REDEFINITION.sync_interim_table('OWNER', 'ORIGINAL', 'INTERIM_TABLE');

  12.  

  13. Copy table dependents to new table. This should copy all constraints, indexes and triggers using the default parameters.

     

    BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
    (uname=>'<table_owner>',
    orig_table=>'<table>',
    int_table=>'<interim_table>');
    END;
    /

  14.  

  15. Finally, tables can be switched the interim table will now be the name of the original table and vice versa. To be safe, there should be no applications accessing the table for this last step.  It only takes a few seconds to perform.

     

    EXEC DBMS_REDEFINITION.finish_redef_table('<table_owner>', '<orig_table>', '<interim_table>');

Conclusion:

Using dbms_redefinition allows the DBA to make structural changes to a table that is completely transparent to the user community. In this example the purpose was to remove advanced compression from a table, but this is appropriate for changing a non-partitioned table to partitioned, changing column order or similar types of operations.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Tips for Upgrading From SQL 2008 to 2012 or 2014

It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.

Andy McDermid | April 8, 2015

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS