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.

  9 Responses to “SQL Quiz: How do inserts create database objects?”

  1. create synonym user_objects for a_table
    /

  2. maybe an effect of interval partitioning: a new partition is created for the new value – but not removed during the rollback.

  3. Deferred segment creation.

 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>