0

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.

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.