This discussion is archived
3 Replies Latest reply: Nov 6, 2012 9:26 AM by LaserSoft RSS

Can I compress existing data

719190 Newbie
Currently Being Moderated
Can I compress data that is already in a table/partition that is not already compressed, INLINE, and not have to unload/reload?

Thanks,

Rich
  • 1. Re: Can I compress existing data
    mrmessin Oracle ACE
    Currently Being Moderated
    You will have to reload the data in some fashion. You can compress an individual partition at a time through the move of a partition or table as well
  • 2. Re: Can I compress existing data
    916290 Newbie
    Currently Being Moderated
    yes use following command to compress existing data in partition...
    alter table TEST_TAB move partition TEST_TAB_2011 compress for oltp;

    Following only modifies new data.
    alter table TEST_TAB modify partition TEST_TAB_2011 compress for oltp;
  • 3. Re: Can I compress existing data
    LaserSoft Journeyer
    Currently Being Moderated
    Hi

    To compress an existing table, the following 3 approaches can be used:

    Method 1:

    Option 1 :

    SQL> select TABLE_NAME,COMPRESSION from USER_TABLES where table_name='TEST';
    SQL> ALTER TABLE T1 move COMPRESS;
    SQL> select TABLE_NAME,COMPRESSION from USER_TABLES where table_name='TEST';

    Option 2 :

    SQL> select table_name, partition_name, compression from user_tab_partitions where table_name='SALES';
    SQL> ALTER TABLE sales MOVE PARTITION southwest TABLESPACE TS COMPRESS;
    SQL> select table_name, partition_name, compression from user_tab_partitions where table_name='SALES';


    NOTE: All constraints, triggers or other dependent objects stay the same but the indexes are marked as unusable. To mark the indexes as usable :

    Rebuild the indexes

    OR

    Specify UPDATE INDEXES as part of the ALTER TABLE statement

    Method 2 :

    CREATE TABLE AS SELECT

    This method allows for creation of a new compressed table without the indexes or constraints.
    Constraints,Indexes if required can be created on the newly created table.


    Method 3 :

    3, DBMS_REDEFINITION:

    ALTER TABLE and MOVE statements takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes.If this is not acceptable, the Oracle Database online redefinition utility (the DBMS_REDEFINITION PL/SQL package) can be used:

    Thanks
    LaserSoft

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points