Oct 212013
 
alter table to_make_cols_vanish_instantly 
set unused (the_cols_you_want_to_drop);

Dropping columns from large tables can be a time consuming process. Setting 
columns unused is a very handy way to "drop" columns in your miniscule 
release window. You still need to tidy up after yourself later though ;)

Further reading about this at oracle-base or in the docs. For a quick and dirty script, keep reading.

create table to_make_cols_vanish_instantly (
  filler                    integer,  
  the_cols_you_want_to_drop integer
);

alter table to_make_cols_vanish_instantly 
set unused (the_cols_you_want_to_drop);

-- it's gone!
desc to_make_cols_vanish_instantly ;

-- or is it?
select * 
from   user_tab_cols
where  table_name = 
         'TO_MAKE_COLS_VANISH_INSTANTLY';

-- we need to drop the unused columns 
-- to remove them properly
alter table to_make_cols_vanish_instantly 
drop unused columns;

select * 
from   user_tab_cols
where  table_name = 
         'TO_MAKE_COLS_VANISH_INSTANTLY';

drop table to_make_cols_vanish_instantly purge;

  One Response to “Instant Column Removal”

  1. This post, “Instant Column Removal

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>