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;
/
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;