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;

  COUNT(*)
----------
         2

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

rollback;

select count(*) from user_objects;

  COUNT(*)
----------
         3

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.

Jan 312014
 

An SQL magic trick – the table disappeared, just by running a select statement! Can you figure out how it’s done?

select * 
from   the_disappearing_table_trick
/

NOW_YOU_SEE_ME
--------------
Now you don't

--....

select * 
from   the_disappearing_table_trick
/

ORA-00942: table or view does not exist

If you think you know, put your answers in the comments!

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...