NUMBER data type…what harm can it do ?

There’s a somewhat sour discussion going on based attached to the video at

Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to data types with arbitrary precision, which was stumbled upon by a friend at work.

Let’s start with a simple comparison between two dates. In this case, I’ve just (more...)

Avoiding the COMMIT bomb!

I’m an Oracle dinosaur, so I like using SQL Plus.  Its simple, fast, comes with every version and installation and platform, and I’m very familiar with it.  (And who knows, it might still be at the forefront of the Oracle development teams! )

But there is one important thing I always take care of when I’m using SQL Plus, and it’s easiest to explain with an example.

You start (more...)

(OT) an idea for Easter (and well…every day)

I don’t travel as frequently as some people do, but with OpenWorld and various Oracle conferences each year, I definitely see my fair share of the inside of a plane and a hotel room.  To pass the time on flights, I try to read, but when you’re reading on a work-based trip, it’s a case of reading a page or two here and there, rather than a true extended session of relaxed reading.

The (more...)

Edition based redefinition – an apology

In April 2008 (wow, does time fly!) I used the following picture in my "11g features for Developers" presentation at the Australian Oracle User Group conference.


I think the picture is from the movie "Indiana Jones and the Last Crusade"… where they sought the Holy Grail.

I used the picture because I said that Edition Based Redefinition (EBR) was the Holy Grail of 24/7 Oracle based applications.  (Like most Oracle presentations at the (more...)

In-memory – can you REALLY drop those indexes ?

Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries.  The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is the capacity to consume data from storage.

What perhaps a more interesting area of investigation is the claim that In-memory will also be of benefit in OLTP systems, where (to (more...)

WHEN OTHERS … sometimes just not enough

We currently are working on a bug with Oracle Support with dbms_stats in

That may be the subject of a latter post, but in a nutshell, here’s the issue

SQL> exec dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA');
BEGIN dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA'); END;
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete

Now obviously that’s not meant to be happening, and it pertains to incremental stats on a database (more...)

v$object_usage catches me out every time :-)


Every …. single …. time….

Why does index monitoring make me scratch my head and charge off to google so many times…Well, I’m over it, so time to put it on my blog (even though its already on many other places) so I do not get caught out anymore :-)

It always starts like this:

  • Turn on index monitoring on some indexes
  • Wait…
  • Come back later, and get ready to check on my results
  • (more...)

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. (more...)

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.

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 (more...)