Mar 102014
 
alter table from_11g add 
  column_with_a varchar2(100) default 
    'is instant provided you set it' not null;

Prior to 11g the above statement could take a very long time if the table was “big”. I was reminded of this by a recent discussion on Oracle-L. Tom Kyte also discusses this in more detail on asktom.

For a quick and dirty demo, keep reading Continue reading »

Feb 282014
 
alter  /* unnormalized */ table 
    is_still_unnormalized_after add ( 
  surrogate_key_id integer primary key,
  to_it            varchar2(1)
);

Normalization is to do with the dependencies between the (business) attributes. As surrogate keys are additional system generated identifiers, they do nothing to change the dependencies between the original attributes!

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.
Continue reading »

Jun 102013
 
alter table i_know_my_data_is_rubbish_so
enable novalidate 
constraint so_at_least_new_stuff_is_good;

Creating constraints as novalidated can be a great way to start bringing order to chaos 
if you've inherited a database with no constraints. It can also be useful when business
constraint have changed and you want to leave existing data violating the new constraint.

If you’d like an example of this in practice, just use this script.