First Possibility: Adding the Attribute in one alter step

— mark table as uncompressed (only mark, here no uncompressing!)
ALTER TABLE <table> nocompress;
/
–uncompress table in the same tablespace
ALTER TABLE l<table> move;
/

ALTER TABLE <table> nocompress;
/

— Add Attribute with defaultvalue
ALTER TABLE <table> ADD ( start_dt DATE DEFAULT TO_DATE(‚31.12.9999′,’dd.mm.yyyy‘) NOT NULL );
/

— compress table
ALTER TABLE load_dim_business_parties compress;
/

Second Possibility: Adding the Attribute without not null / default value an do an separate update
Here is the possibility to do the update in parallel.

BEGIN
EXECUTE IMMEDIATE ‚drop table compress_add_attribute‘;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

CREATE TABLE compress_add_attribute(id number) compress;

BEGIN
FOR ii in 1 .. 10000
loop
insert into compress_add_attribute (id) values(ii);
end loop;
commit;
END;

——————————————————————————————-
——————————————————————————————-

— restartability
BEGIN
EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute ADD ( begin_dt DATE, end_dt DATE)‘;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

COMMENT ON COLUMN compress_add_attribute.begin_dt IS ‚Beginn History.‘;
/
COMMENT ON COLUMN compress_add_attribute.end_dt IS ‚End History.‘;
/

DECLARE

l_row_cnt_1      NUMBER;
l_row_cnt_2      NUMBER;
l_tablespace   VARCHAR2(30);

BEGIN

pkg_utl_log.log ( ‚Start update compress_add_attribute: ‚
|| TO_CHAR ( SYSDATE
, ‚dd.mm.yyyy hh24:mi:ss‘ ) );

EXECUTE IMMEDIATE ‚ALTER SESSION FORCE PARALLEL DML PARALLEL 8‘;

EXECUTE IMMEDIATE ‚ALTER SESSION FORCE PARALLEL DDL PARALLEL 8‘;

EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute NOCOMPRESS‘;

— Select the tablespace with the most free space
SELECT tablespace_name
INTO l_tablespace
FROM (SELECT tablespace_name
FROM ts_usage
WHERE tablespace_name LIKE ‚TS_WORK‘ –place here some %-pattern
ORDER BY free_gb DESC
)
WHERE ROWNUM = 1;

EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute MOVE TABLESPACE ‚
|| l_tablespace;

UPDATE compress_add_attribute
SET begin_dt = to_date(‚19000101′,’yyyymmdd‘);

l_row_cnt_1   := sql%ROWCOUNT;

COMMIT;

UPDATE compress_add_attribute
SET end_dt   = to_date(‚99991231′,’yyyymmdd‘);

l_row_cnt_2   := sql%ROWCOUNT;

COMMIT;

pkg_utl_log.log(‚compress_add_attribute: updated begin_dt/end_dt ‚||l_row_cnt_1|| ‚/’||l_row_cnt_2|| ‚ rows).‘);

EXECUTE IMMEDIATE   ‚ALTER TABLE compress_add_attribute COMPRESS‘;

— Select the tablespace with the most free space.
SELECT tablespace_name
INTO l_tablespace
FROM (   SELECT tablespace_name
FROM ts_usage
WHERE tablespace_name LIKE ‚TS_WORK‘ –place here some %-pattern
ORDER BY free_gb DESC
)
WHERE ROWNUM = 1;

EXECUTE IMMEDIATE   ‚ALTER TABLE compress_add_attribute MOVE TABLESPACE ‚
|| l_tablespace;

EXECUTE IMMEDIATE ‚ALTER TABLE compress_add_attribute
MODIFY ( begin_dt NOT NULL, end_dt NOT NULL )‘;

pkg_utl_log.log ( ‚Finished update compress_add_attribute: ‚
|| TO_CHAR ( SYSDATE
, ‚dd.mm.yyyy hh24:mi:ss‘ ) );
EXCEPTION

WHEN OTHERS THEN
pkg_utl_log.log ( ‚IN EXCEPTION‘ );
pkg_utl_log.log ( SQLERRM
, pkg_utl_log.gc_fatal
, SQLCODE );
RAISE;
END;