Jun 032014

I insert a row into a table, roll it back, yet somehow I’ve created a new object in my database!

select count(*) from user_objects;


insert into a_table
values ('that create', 1, 'new object');


select count(*) from user_objects;


There’s no triggers or other “trickery” involved – the code above is a copy-paste in a database where I have the only session.

How did this happen? Put your answers in the comments!

If you’d like to get more posts on using and designing Oracle databases, enter your details the form below to receive future updates.

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 »

Mar 222013
with the_sql_monitor as (
  select a_great_way_to_see,
  from   your_painfully_slow_queries
  where  tuning_pack_license = 'Y'
  and    the_boss = 'screaming for the result'
select * 
from   the_sql_monitor
where  real_time_execution_plans = 
           'taking !$@# forever to run';
This really is an outstanding feature to help track the progress of that 4 hour SQL query,
showing which part of the execution plan is chewing up all the time so you know where to direct your tuning efforts

For a great description on using the SQL monitor, have a read of this oracle base article.

Jan 302013
create table use_interval_partitioning (
  and_new_partitions            number,
  will_be_created_automatically varchar2(1)
                                default 'Y',
  for_each_new                  date,
  so_your_system_wont_break     varchar2(1)
                                default 'Y',
  if_maintenance_is_forgotten   varchar2(100)
                                default 'Yay!'
) partition by range (for_each_new)
  interval (numtodsinterval(1,'DAY')) (
    partition first values less than (
      date '2013-01-30')
A very handy feature, I just wish there was a way to specify a regexp for new partition names; 
I don't like systems cluttered up with SYS_Pxxx partitions...
Jan 022013
create trigger nuclear_fission 
for update on  uranium compound trigger

  cursor remaining_material is
    select rowid, u.*
    from   uranium u
    where  atomic_mass = 235
    and    rownum <= 
      (select count(*) from neutrons);
  fissile_matter remaining_material%rowtype;
  after each row is 
    if :new.atomic_mass = 236 then 
      insert into krypton (atomic_mass) 
      values (92);
      insert into barium  (atomic_mass) 
      values (141);
      insert into neutrons (atomic_mass) 
      values (1);
      insert into neutrons (atomic_mass) 
      values (1);
      insert into neutrons (atomic_mass) 
      values (1);
    end if;
  end after each row;

  after statement is
    open remaining_material;
      fetch remaining_material 
      into  fissile_matter; 
      exit when remaining_material%notfound;
      update uranium
      set    atomic_mass = atomic_mass + 
        (select atomic_mass 
         from   neutrons 
         where  rownum = 1)
      where  atomic_mass = 235
      and    rowid = fissile_matter.rowid;
      delete neutrons;
    end loop;
    close remaining_material;
    delete uranium 
    where  atomic_mass = 236;
  end after statement;
/It may not generate your database any extra power, 
but at least you won't have to deal with leftover radioactive material

To start your own nuclear reaction, use this.