Who's online

There are currently 0 users and 31 guests online.

Recent comments


RETURNING BULK COLLECT and database links

Looks like the nice PL/SQL facility for returning a set of updated rows is restricted when it comes to database links

(This tested on

SQL> declare
  2    type int_list is table of number(12) index by pls_integer;
  3    l_results int_list;
  5  begin
  6    update MY_TABLE b
  7    set b.my_col = ( select max(last_ddl_time) from user_objects@dblink where object_id = b.key_col)
  8    where b.my_col is null
  9    returning b.other_col bulk collect into l_results;
 10  end;
 11  /
ERROR at line 1:
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at line 6

When we remove the database link, things revert to what we would expect

Travelling in a Wheelchair

It’s that time of the year again, when one of the best grass roots Oracle user group conferences takes place, the Rocky Mountain Oracle User Group Training Days in Denver Colorado. I’ve been privileged enough to present at this conference several times in the past, and always have an absolute blast at it, so I try to submit a few papers each year. This year, I had three papers accepted. Two of them were designed to be deep-dive sessions, one on delivering Schema as a Service and one on delivering Database as a Service, and the other one is a presentation on Snap Clone, which is functionality within Enterprise Manager that takes advantage of Copy-on-Write technology (available either through storage technology or CloneDB in the Oracle Database) to take thin clones of Oracle databases.

GROUP BY – wrong results in

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s

SQL> create table T ( ts number not null, c char(10));
Table created.
SQL> insert into T
  2  select 100+dbms_random.value(1,50),'x'
  3  from ( select 1 from dual  connect by level < 1000 ),
  4       ( select 1 from dual  connect by level < 1000 )
  5  /
998001 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','T') ;
PL/SQL procedure successfully completed.

So I’ve got ~1million rows, with column TS containing decimal numbers ranging between 100 to 150. Now first I’m going to reduce those numbers to integers, via an inline view, and then truncate those numbers to the nearest 100 (the outer statement). Because all the numbers are between 100 and 150, rounding down to the nearest 100, should return just a single row. Lets see what happens…

Data Center of the Future – now



photo by youflavio

In a recent blog post Eric D. Brown defined an Agile Data Center as

#777777;">An Agile Data Center is one that allows organizations to efficiently and effectively add, remove and change services at the speed of the business, not the speed of technology - Eric D. Brown

In follow up post he said that a Agile Data Center could be implemented by Software Defined Data Center (SDDC) for example using machine virtualization to spin environments up and down. by Clive Bostock


#555555; padding-left: 30px;">This is a reposting of an old blog post that was on but is no longer accessible

#555555; padding-left: 30px;">More trace file analyzer tools at  #2970a6;" href="">

#555555; padding-left: 30px;">Related blog post: Oracle “Physical I/O” ? not always physical with a 10046 parser specifically for I/O and readme

Put Delphix on your laptop at Oracle Jan 28 !



Create an army of clone databases and applications in minutes


Tomorrow Jan 28 we will be installing Delphix on people’s laptops at the BIWA conference at Oracle conference center at Oracle head quarters in Redwood Shores.


5 ways to boost your career with Social Media


Photo by Kevin Dooley

If you are in the Bay Area tomorrow, Jan 27, come see myself, Yann Ropars and #141823;">Yury Velikanov talk about how to leverage social media to boost your career. We will be talking at Oracle head quarters at the NoCOUG/BIWA conference at #444444;">2:30 pm 

Why use social media as an IT technician? Because

Free Version of Delphix !

Delphix is now available for  30 day trial direct download ! ( if you would like a longer version please contact me at year trials and even indefinite trials  potential possible for partners, bloggers, Oracle Aces etc)

Just got to the

The Delphix download trial consists of 3 pre-configured virtual machines downloadable as OVA files

Inappropriate behaviour

You pick up little funny things in the day to day with Oracle.

Like this one when you try to drop a tablespace with a queue table in it:

drop tablespace MY_TSPACE including contents
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: Inappropriate utilities used to perform DDL on AQ table

You would think that since you’ve asked to drop everything, that well…everything could be dropped, but no :-)

The solution here is to use DBMS_AQADM.DROP_QUEUE_TABLE (with force=true if necessary) to clean it out, and the re-issue your command.