Top 60 Oracle Blogs

Recent comments


Transaction subtleties

This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out.

Lets set the scene with a simple procedure that commences a transaction, but then always fails

SQL> drop table test$tab purge;

Table dropped.

SQL> create table test$tab (val varchar2(1));

Table created.

SQL> create or replace  procedure foo_proc is
  2  begin
  3    dbms_output.put_line('in foo_proc');
  4    insert into test$tab values ('t');
  5    raise_application_error(-20001,'Error');
  6  end;
  7  /

Procedure created.

One of the cool things about PL/SQL is its nice handling of transaction processing, or often described with a statement along the lines of: “A PL/SQL procedure acts as a single unit of work”.

Data denormalization … another take

I read an interesting article the other day about data modelling and data denormalization.

I’ll paraphrase the topic and requirement here (apologies to the original author for any omissions or excessive brevity).

We have a fictional application with chat rooms, people subscribing to those chat rooms, and posting messages in the chat rooms.  To satisfy this we have the following data model




Exadata Express – I’m in !

Jumping into 12.2 on Exadata Express is made easy with the packaging up of the client configuration files.  Here’s what I had to do to get SQL Plus going into the database

1) Download the client credentials file from the Service Console




2) Unzip the file into a folder


Shirts of OpenWorld

OpenWorld just isn’t OpenWorld without enjoying a bit of fun with one’s attire Smile


The simple fix to date queries

We had question in the OpenWorld panel about why queries on date columns are “always slow”.  Well….they aren’t Smile but here’s a common cause of that misconception.

Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is  a date).

SQL> create table t as
  2  select *
  3  from dba_objects;

Table created.

SQL> create index t_ix on t ( created );

Index created.

The problems start when we do a query on the CREATED column and get "unexpected" results

Why Bother

This note comes to you prompted by “Noons” in a recent twitter exchange

In response to a complaint by Lukas Eder about having to educate people in 2016 that there is no (performance) difference between count(*) and count(1), Nuno  asked me to blog about my claim that this non-difference is a good educational example on at least three different counts.

Do You Even TDM, (Test Data Management)?

Along with the deep learning I’ve been allowed to do about data virtualization, I’ve learned a great deal about Test Data Management.  Since doing so, I’ve started to do some informal surveys of the DBAs I run into and ask them, “How do you get data to your testers so that they can perform tests?”  “How do you deliver code to different environments?”

Partitioning an existing index

I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s).  Their database was 11g, but I thought it would worth showing off some 12c features that would let you do this process with minimal disruption.

First we’ll create our table, with a standard primary key index

drop table t purge;

create table t as select * from dba_objects
where object_id is not null;

create unique index IX on T ( object_id );

alter table T add constraint T_PK primary key ( object_id );

Now the task is to partition the index. In 12c, you can have multiple indexes defined on the same column(s), as long as only one of those indexes is visible. So we’ll create our new partitioned index invisible in online mode.

Securefile in 12c – part 2

In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c.  Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile LOBs defined.  This blog post explores that.

Happy birthday to …. well … us :-)

Last year on September 1, the AskTom site was resurrected under Apex 5 with myself and Chris Saxon manning the fort to deliver as much value to the Oracle community as the esteemed previous custodian of the site did in the many years preceding us.

In the last year, we have

  • answered ~2,500 questions
  • taken and followed up on ~3000 reviews

It’s great fun and very rewarding working on the AskTom site.  We get to participate in the ever growing Oracle community, and it is like “free training” for Chris and I – we get to learn new things every day.

So I hope you’re getting as much value out of the site as we are.  We have plans to continue the site’s evolution to yield even more value in future.