Does Oracle Streams replicate unused columns

oracleoracle-streamsreplication

Since you can now mark columns as unused with

alter table <table> set unused column <column>

are columns, set as unused, replicated by Streams? Or is it configurable by rules or restrictions?

It would be an efficient way to delete unused columns from the dictionary by just using streams and switching the database at some point to get free from all the unused columns data.

Best Answer

That seems a large setup effort just to reclaim space from an unused column. If you really want to recover that space (and presumably not take an outage to achieve it) you could look at DBMS_REDEFINITION

SQL> create table t ( x int primary key , y int, fatcol char(1000) );

Table created.

SQL> insert into t
  2  select rownum, rownum, rownum
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL>
SQL> select bytes from user_segments where segment_name = 'T';

     BYTES
----------
  12582912

1 row selected.

SQL>
SQL> alter table t set unused column fatcol;

Table altered.

SQL>
SQL> CREATE TABLE small_t as
  2  SELECT *
  3  FROM   t
  4  where 1=0;

Table created.

SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table(user, 'T', 'SMALL_T');

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER TABLE SMALL_T ADD (CONSTRAINT SMALL_T_PK PRIMARY KEY (x));

Table altered.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(user, 'T', 'SMALL_T');

PL/SQL procedure successfully completed.

SQL>
SQL> select bytes from user_segments where segment_name = 'T';

     BYTES
----------
    196608

1 row selected.