HomeArticlesOthersLogin
NewsCustom SearchContact
Aging table - drop column of partitioned table with compressed old partitions
    by Ludek Bob Jankovsky, 11-Nov-2012 (ORACLE STUFFS)
Using fast aging data (fact tables, logs) you will have to consider some kind of aging strategies like partitioning by time slices, maintaining old partitions compressing them, moving them to slower storage or dropping them at the end. One of factor considered during decision COMPRESS - NO COMPRESS is potential problem of dropping column from table. That is impossible while table or some partitions are compressed. Uncompress all the partitions of huge aged table can make space difficulties. Following solution avoid the problem and offer robust way how to drop column from aged table with compressed partitions.


Following steps are necessary to make it without extended requirements for disk space:
  • For each partition: create one empty single 'copy' table with the same structure without constraints and indexes
  • For each partition: exchange partition with the 'copy' table. For that you have to disable all constraints of the partitioned table to avoid conflicts.
  • Alter empty shell now of the partitioned table to make it NOCOMPRESS.
  • Drop column from the empty shell of the partitioned table.
  • Alter empty shell now of the partitioned table to make it COMPRESS.
  • For each 'copy' table: Uncompress - drop column from the copy - compress again - exchange back - drop 'copy' - rebuild local indexes.
  • Rebuild global indexes.
  • Enable disabled constraints.

There are following issues of the solution:
  • Data in the table are not accessible during the action (if you need data accessible all the time, you have to make modified copy of the table instead of the mentioned solution, what supports it despite of bigger space and time consumption).
  • There are several DDL operations so in the case of accidental interruption of the process there should be an evidence about current state of partition exchanges and names of copies of data.

Sample solution:

DECLARE
  -- parameter sheet ----------------------------------------------------------
  p_table_owner      VARCHAR2(30) := USER;
  p_table_name       VARCHAR2(30) := 'BBB';--name of processed partitioned table
  p_drop_column_name VARCHAR2(30) := 'A3'; --name of column to be dropped
  p_prefix           VARCHAR2(30) :='AAA$'; --prefix of all the service objects
  -----------------------------------------------------------------------------
  v_operation_type   VARCHAR2(12);
  v_operation_status VARCHAR2(100);
  NO_OBJECT_FOUND    EXCEPTION;
  PRAGMA EXCEPTION_INIT (NO_OBJECT_FOUND, -942);
  NO_IDENTIFIER_FOUND EXCEPTION;
  PRAGMA EXCEPTION_INIT (NO_IDENTIFIER_FOUND, -904);
  v_count INTEGER;
  CURSOR c_CN (p_owner VARCHAR2,p_table_name VARCHAR2) IS
    SELECT CONSTRAINT_NAME,  CONSTRAINT_TYPE
    FROM ALL_CONSTRAINTS
    WHERE owner = p_owner AND table_name = p_table_name AND Status = 'ENABLED';
  TYPE t_CN IS TABLE OF c_CN%rowtype INDEX BY binary_integer;
  v_CN t_CN;
  CURSOR c_TP(p_owner VARCHAR2,p_table_name VARCHAR2) IS
    SELECT PARTITION_NAME,
      CAST(p_prefix||PARTITION_NAME AS VARCHAR2(100)) AS COPY_NAME,
      CAST('NONE' AS VARCHAR2(30))  AS STATUS
    FROM ALL_TAB_PARTITIONS
    WHERE TABLE_OWNER=p_table_owner AND TABLE_NAME   =p_table_name;
  CURSOR c_TSP(p_owner VARCHAR2,p_table_name VARCHAR2) IS
    SELECT SUBPARTITION_NAME AS PARTIRION_NAME,
      CAST(p_prefix||PARTITION_NAME AS VARCHAR2(100)) AS COPY_NAME,
      CAST('NONE' AS VARCHAR2(30))  AS STATUS
    FROM ALL_TAB_SUBPARTITIONS
    WHERE TABLE_OWNER=p_table_owner AND TABLE_NAME   =p_table_name;
  TYPE t_TP IS TABLE OF c_TP%rowtype INDEX BY binary_integer;
  v_TP t_TP;
  TYPE t_names IS  TABLE OF CHAR INDEX BY VARCHAR2(30);
  v_names t_names;
  --
  PROCEDURE RepairPartIndexes(
    p_table_owner    VARCHAR2,
    p_table_name     VARCHAR2,
    p_partition_name VARCHAR2)
  IS
  BEGIN
    FOR r2 IN (
      SELECT ai.OWNER, ai.INDEX_NAME, aip.PARTITION_NAME
      FROM ALL_INDEXES ai
      JOIN ALL_IND_PARTITIONS aip ON aip.INDEX_NAME = ai.INDEX_NAME AND aip.INDEX_OWNER = ai.OWNER
      WHERE ai.TABLE_NAME =p_table_name AND ai.TABLE_OWNER = p_table_owner AND ai.PARTITIONED = 'YES'
        AND aip.PARTITION_NAME=p_partition_name AND aip.STATUS ='UNUSABLE'
    ) LOOP
       EXECUTE IMMEDIATE 'ALTER INDEX '||r2.owner||'.'||r2.INDEX_NAME||' rebuild partition '||r2.partition_name||' PARALLEL';
    END LOOP;--r2
  END RepairPartIndexes;
  --
  PROCEDURE RepairSubPartIndexes(
    p_table_owner    VARCHAR2,
    p_table_name     VARCHAR2,
    p_partition_name VARCHAR2)
  IS
  BEGIN
    FOR r2 IN (
      SELECT ai.OWNER, ai.INDEX_NAME, aip.SUBPARTITION_NAME
      FROM ALL_INDEXES ai
      JOIN ALL_IND_SUBPARTITIONS aip ON aip.INDEX_NAME =ai.INDEX_NAME AND aip.INDEX_OWNER = ai.OWNER
      WHERE ai.TABLE_NAME = p_table_name  AND ai.TABLE_OWNER = p_table_owner AND ai.PARTITIONED ='YES'
        AND aip.SUBPARTITION_NAME = p_partition_name AND aip.STATUS = 'UNUSABLE'
    ) LOOP
       EXECUTE IMMEDIATE 'ALTER INDEX '||r2.owner||'.'||r2.INDEX_NAME||' REBUILD SUBPARTITION '||r2.subpartition_name||' PARALLEL';
    END LOOP;--r2
  END RepairSubPartIndexes;
  --
  PROCEDURE RepairIndexes(
    p_table_owner VARCHAR2,
    p_table_name  VARCHAR2)
  IS
  BEGIN
    FOR r2 IN(
      SELECT ai.OWNER, ai.INDEX_NAME
      FROM ALL_INDEXES ai
      WHERE TABLE_NAME = p_table_name AND table_owner = p_table_owner AND status ='UNUSABLE' AND PARTITIONED ='NO'
    ) LOOP
      EXECUTE IMMEDIATE 'ALTER INDEX '||r2.owner||'.'||r2.INDEX_NAME||' REBUILD PARALLEL';
    END LOOP;--r2
  END RepairIndexes;
  --
  FUNCTION RepairCopyName(
    p_name VARCHAR2)
  RETURN VARCHAR2 IS
    v_name_orig VARCHAR2(30):=SUBSTR(p_name,1,30);
    v_name      VARCHAR2(30):=v_name_orig;
    v_cnt       NUMBER      :=1;
    v_int       INTEGER     :=0;
  BEGIN
    WHILE (v_cnt>0) LOOP
      IF NOT v_names.exists(v_name) THEN
        SELECT COUNT(1) INTO v_cnt FROM USER_OBJECTS WHERE OBJECT_NAME=v_name;
        IF v_cnt =0 THEN
          v_names(v_name):='Y';
        END IF;
      END IF;
      IF v_cnt >0 THEN
        v_int :=v_int                  +1;
        v_name:=SUBSTR(v_name_orig,1,29-LENGTH(TO_CHAR(v_int)))||'$'||TO_CHAR(v_int);
      END IF;
    END LOOP;
    RETURN v_name;
  END RepairCopyName;
  --
BEGIN
  ------------------------------------------------------------
  --TABLE TYPE
  --are there subpartitions?
  BEGIN
    SELECT CASE WHEN SUBPARTITIONING_TYPE !='NONE' THEN 'SUBPARTITION' 
                WHEN PARTITIONING_TYPE !='NONE' THEN 'PARTITION' END 
     INTO v_operation_type 
     FROM ALL_PART_TABLES WHERE TABLE_NAME=p_table_name AND OWNER=p_table_owner;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      raise_application_error(-20000, 'Table '||p_table_owner||'.'||p_table_name||' is not partitioned or you are short of some important privileges');
  END;
  ------------------------------------------------------------
  --LOGGING AND FAILOVER START
  --table status NONE -> START -> METADATA -> CHANGED -> DONE
  BEGIN 
     EXECUTE IMMEDIATE 'select STATUS from '||p_prefix||'$$STATUS$$ where R_TYPE=''TABLE''' INTO v_operation_status; 
  EXCEPTION
     WHEN NO_DATA_FOUND THEN
       v_operation_status := 'START'; 
     WHEN NO_OBJECT_FOUND THEN 
       v_operation_status := 'NONE'; 
  END;
  IF  v_operation_status = 'DONE' THEN 
     RETURN;
  End if;
  IF  v_operation_status = 'START' THEN --rollback to NONE
      EXECUTE IMMEDIATE 'drop table '||p_prefix||'$$STATUS$$ purge'; 
      v_operation_status := 'NONE'; 
  END IF;
  IF  v_operation_status = 'NONE' THEN --create initial setting
      EXECUTE IMMEDIATE 'create table '||p_prefix||'$$STATUS$$ (R_NAME Varchar2(100),R_TYPE Varchar2(100),STATUS Varchar2(100),VAL1 Varchar2(100), primary key (R_NAME, R_TYPE))'; 
      EXECUTE IMMEDIATE 'insert into '||p_prefix||'$$STATUS$$(R_NAME, R_TYPE, STATUS) values ('''||p_table_name||''',''TABLE'',''START'')';
      v_operation_status := 'START'; 
  END IF;
  ------------------------------------------------------------
  COMMIT; --Checkpoint DDL before, Proces in state START or further 
  ------------------------------------------------------------
  --CONTSRAINTS STATUS STORE, it works either in state of START or METADATA .. they are either initialized or appended 
  OPEN c_CN(p_table_owner,p_table_name);
    fetch c_CN BULK COLLECT INTO v_CN;
  CLOSE c_CN;
  IF v_CN.FIRST IS NOT NULL THEN
    FOR i IN v_CN.FIRST .. v_CN.LAST LOOP
      BEGIN 
        EXECUTE IMMEDIATE 'insert into '||p_prefix||'$$STATUS$$(R_NAME, R_TYPE, STATUS, VAL1) values ('''||v_CN(i).CONSTRAINT_NAME||''',''CONSTRAINT'',''N/A'','''||v_CN(i).CONSTRAINT_TYPE||''')';
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
      END;
    END LOOP;  
  END IF;
  ------------------------------------------------------------
  --PARTITION STATUS STORE, it works either in state of START or METADATA .. they are either initialized or appended 
  IF v_operation_type='PARTITION' THEN
    OPEN c_TP(p_table_owner,p_table_name);
    FETCH c_TP bulk collect INTO v_TP;
    CLOSE c_TP;
  ELSE
    OPEN c_TSP(p_table_owner,p_table_name);
    FETCH c_TSP bulk collect INTO v_TP;
    CLOSE c_TSP;
  END IF;
  v_names.delete;
  --correct copy names to be short enough and unique  
  FOR i IN v_TP.FIRST .. v_TP.LAST LOOP
    v_TP(i).copy_name:=repairCopyName(v_TP(i).copy_name);
    BEGIN 
       EXECUTE IMMEDIATE 'insert into '||p_prefix||'$$STATUS$$(R_NAME, R_TYPE, STATUS,VAL1) values ('''||v_TP(i).partition_name||''','''|| v_operation_type||''',''START'','''|| v_TP(i).copy_name||''')';
    EXCEPTION
       when DUP_VAL_ON_INDEX then null;
    END;   
  END LOOP;      
  IF v_operation_status IN ('NONE','START') THEN 
     v_operation_status := 'METADATA';
     EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_operation_status||''' where R_TYPE = ''TABLE''';
  End if;   
  ------------------------------------------------------------
  COMMIT; --Checkpoint, Proces in state METADATA, CHANGED, DDL will follow
  -- In state of METADATA, CHANGED phase all is driven based on particular objects
  ------------------------------------------------------------
  --DISABLE STILL ENABLED CONSTRAINTS       
  IF v_CN.FIRST IS NOT NULL THEN
    FOR i IN v_CN.first .. v_CN.last LOOP
      EXECUTE immediate 'Alter table '||p_table_owner||'.'||p_table_name||' disable constraint '||v_CN(i).CONSTRAINT_NAME||
      CASE
      WHEN v_CN(i).CONSTRAINT_TYPE IN ('U','P') THEN
        ' KEEP INDEX'
      END;
    END LOOP;
  END IF;
  ------------------------------------------------------------
  --REFILL PARTITIONS WITH STORED METADATA
  v_TP.DELETE;
  EXECUTE IMMEDIATE 'Select R_NAME,VAL1,STATUS from '||p_prefix||'$$STATUS$$ where R_TYPE = '''||v_operation_type||'''' BULK COLLECT INTO v_TP;
  ------------------------------------------------------------
  --FRAGMENTIZE ORIGINAL TABLE
  FOR i IN v_TP.FIRST .. v_TP.LAST LOOP
     IF v_TP(i).status in ('START','PENDING') THEN
        BEGIN
           EXECUTE IMMEDIATE 'select count(1) from '||v_TP(i).copy_name||' where rownum=1' INTO v_count;
        EXCEPTION
           WHEN NO_OBJECT_FOUND THEN 
           EXECUTE IMMEDIATE 'Create table '||v_TP(i).copy_name||' as select * from '||p_table_owner||'.'||p_table_name||' where 1=0';
           v_count := 0;
        END;
        IF v_count=0 THEN 
          v_TP(i).status := 'PENDING';
          EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_TP(i).status||''' where R_NAME = '''||v_TP(i).partition_name||''' and R_TYPE = '''||v_operation_type||'''';
          EXECUTE IMMEDIATE 'Alter table '||p_table_owner||'.'||p_table_name||' exchange '||v_operation_type||' '||v_TP(i).partition_name||' with table '||v_TP(i).copy_name||' EXCLUDING INDEXES';
          v_TP(i).status := 'OUT';
          EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_TP(i).status||''' where R_NAME = '''||v_TP(i).partition_name||''' and R_TYPE = '''||v_operation_type||'''';
          COMMIT;
        ELSE -- the status START was false or status PENDING just before crash
          v_TP(i).status := 'OUT';
          EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_TP(i).status||''' where R_NAME = '''||v_TP(i).partition_name||''' and R_TYPE = '''||v_operation_type||'''';
          COMMIT;
        END IF;
     END IF;
  END LOOP;
  ------------------------------------------------------------
  --EMPTY SHELL CHANGE
  EXECUTE IMMEDIATE 'select STATUS from '||p_prefix||'$$STATUS$$ where R_TYPE=''TABLE''' INTO v_operation_status; 
  if v_operation_status = 'METADATA' then 
    EXECUTE IMMEDIATE 'Alter table '||p_table_owner||'.'||p_table_name||' nocompress';
    BEGIN
      EXECUTE IMMEDIATE 'Alter table '||p_table_owner||'.'||p_table_name||' Drop column '||p_drop_column_name;
    EXCEPTION
      WHEN NO_IDENTIFIER_FOUND THEN --the operation has been done earlier
        NULL;
    END;
    EXECUTE IMMEDIATE 'Alter table '||p_table_owner||'.'||p_table_name||' compress';
     v_operation_status := 'CHANGED';
     EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_operation_status||''' where R_TYPE = ''TABLE''';
    ------------------------------------------------------------
    COMMIT; --Checkpoint, Proces in state  CHANGED, DDL will follow
    ------------------------------------------------------------
  END IF;
  ------------------------------------------------------------
  --FRAGMENT CHANGES
  FOR i IN v_TP.first .. v_TP.last LOOP
     IF v_TP(i).status in ('OUT','RETURNING') THEN
        EXECUTE IMMEDIATE 'select count(1) from  '||p_table_owner||'.'||p_table_name||' '||v_operation_type||'('||v_TP(i).partition_name||') where rownum=1' INTO v_count;
        IF v_count=0 THEN 
          v_TP(i).status := 'RETURNING';
          EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_TP(i).status||''' where R_NAME = '''||v_TP(i).partition_name||''' and R_TYPE = '''||v_operation_type||'''';
          EXECUTE IMMEDIATE 'Alter table '||v_TP(i).copy_name||' move nocompress PARALLEL';
          BEGIN
            EXECUTE IMMEDIATE 'Alter table '||v_TP(i).copy_name||' drop column '||p_drop_column_name;
          EXCEPTION
            WHEN NO_IDENTIFIER_FOUND THEN --the operation has been done earlier
            NULL;
          END;
          EXECUTE IMMEDIATE 'Alter table '||v_TP(i).copy_name||' move compress PARALLEL';
          EXECUTE IMMEDIATE 'Alter table '||p_table_owner||'.'||p_table_name||' exchange '||v_operation_type||' '||v_TP(i).partition_name||' with table '||v_TP(i).copy_name||' EXCLUDING INDEXES  WITHOUT VALIDATION';
          v_TP(i).status := 'HOME';
          EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_TP(i).status||''' where R_NAME = '''||v_TP(i).partition_name||''' and R_TYPE = '''||v_operation_type||'''';
          COMMIT;
        ELSE -- the status OUT was false or status RETURNING just before crash
          v_TP(i).status := 'HOME';
          EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_TP(i).status||''' where R_NAME = '''||v_TP(i).partition_name||''' and R_TYPE = '''||v_operation_type||'''';
          COMMIT;
        END IF;
     END IF;
     IF v_TP(i).status = 'HOME' THEN
        BEGIN
          EXECUTE IMMEDIATE 'Drop table '||v_TP(i).copy_name||' purge';
        EXCEPTION
           WHEN NO_OBJECT_FOUND THEN  NULL;
        END ;
        IF v_operation_type='PARTITION' THEN
          RepairPartIndexes(p_table_owner,p_table_name,v_TP(i).partition_name);
        ELSE
          RepairSubPartIndexes(p_table_owner,p_table_name,v_TP(i).partition_name);
        END IF;
        v_TP(i).status := 'DONE';
        EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_TP(i).status||''' where R_NAME = '''||v_TP(i).partition_name||''' and R_TYPE = '''||v_operation_type||'''';
     END IF;
  end LOOP;             
  RepairIndexes(p_table_owner,p_table_name);  
  ------------------------------------------------------------
  --REFILL CONSTRAINTS WITH STORED METADATA
  v_CN.DELETE;
  EXECUTE IMMEDIATE 'Select R_NAME,VAL1 from '||p_prefix||'$$STATUS$$ where R_TYPE = ''CONSTRAINT''' BULK COLLECT INTO v_CN;
  ------------------------------------------------------------
  --ENABLE CONSTRAINTS
  IF v_CN.FIRST IS NOT NULL THEN
    FOR i IN v_CN.FIRST .. v_CN.LAST LOOP
      EXECUTE IMMEDIATE 'Alter table '||p_table_owner||'.'||p_table_name||' enable constraint '||v_CN(i).CONSTRAINT_NAME;
    END LOOP;
  END IF;
  v_operation_status := 'DONE';
  EXECUTE IMMEDIATE 'update '||p_prefix||'$$STATUS$$ set STATUS = '''||v_operation_status||''' where R_TYPE = ''TABLE''';
  ------------------------------------------------------------
  COMMIT; --Checkpoint, Proces in state  DONE
  ------------------------------------------------------------
END;
/

[Download]

Testing data for the solution

DROP TABLE BBB PURGE;
Create table BBB 
( A1 number
 ,A2 number  not null
 ,A3 number
 ,A4 number 
 ,constraint PK_AAA primary key (A1, A2, A4) using index local
) compress
Partition by range(A1) 
   --subpartition by range(A2) SUBPARTITION TEMPLATE  (SUBPARTITION S10 VALUES LESS THAN (10), SUBPARTITION S1000 VALUES LESS THAN (1000))
(
  partition P100 values less than (100)
 ,partition P200 values less than (200)
 ,partition P300 values less than (300)
 ,partition P400 values less than (400)
 ,partition P500 values less than (500)
 ,partition P600 values less than (600)
 )      
;
Insert into BBB values(1,1,1,1) ;
Insert into BBB values(100,10,2,1) ;
Insert into BBB values(200,20,4,1) ;
Insert into BBB values(110,3,14,2) ;
Insert into BBB values(120,11,25,14) ;
Insert into BBB values(215,30,47,81) ;
Insert into BBB values(415,310,487,781) ;
COMMIT;

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky