ODC Appreciation Day : Live SQL

During a session on Oracle OpenWorld I had a pleasant time watching Keith Laker use LiveSQL.oracle.com extensively to walk through a tutorial on SQL pattern matching. Very powerful indeed! Both the pattern matching and the way the tutorial was built up.

So, having used it a little bit before, I thought I’d share an example of the in-database archiving functionality in 12c with my team, using Live SQL.

I first jotted down an example on my own, since I like to work through stuff on my own to explore caveats which sometimes are not obvious in the examples. I was surprised when my example didn’t work and spent a bit of time going through documentation.

I also tested an example from the fabulous library of Tim Hall‘s blog posts – and that didn’t work on Live SQL either.

However – both examples worked flawlessly in my VirtualBox database.

Going through the Help-pages of Live SQL i realized that this functionality probably isn’t supported due to session privileges. In fact – I found an example, added by Oracle, a couple of weeks ago demonstrating in-database archiving, where I found the following comment:

Note that the WHERE clause has been added for demonstration purposes in Live SQL. Outside the Live SQL environment the WHERE clause would not be needed because the ROW ARCHIVAL VISIBILITY session setting can be determined.

So – bottom lines:

  • In-database archiving works, but not on Live SQL.
  • There are already examples lying around on Live SQL if you want to save time.
  • Live SQL is a great platform to share knowledge examples! Try it out!

Selected to present on OUG Scotland 2017

I’m honoured to be selected to present twice on OUG Scotland 2017 in Glasgow in june:

Happily I get to deliver my favourite topic “Scalability, Maintainability, Correctness? Easier with #ThickDB“. In this session I’ll look at the Thick DB-paradigm and explain the major negative effects of not adhering to it it will have in a data-lifecycle perspective. We’ll take a red pill and look into The Matrix and see what really happens.

Sadly I’ll be doing that at the same time as Jonathan Lewis talks about “Reading Parallel Execution Plans”. I would really have liked to get a refresh on that topic.

In the afternoon, I’ll talk about a proof of concept (PoC) that we did in the last year: “Consolidating large critical medical databases with minimum downtime – a story from Norway“. The goal was to find a way to migrate 9 medical databases into one of about 100+ TB with a downtime of less that 4 hours for every migration. That includes replacing billions of keys in the process. This was a fun project where we could really test and evaluate different techniques of moving data from several sources into on large one. By applying better techiques and automation we reduced the data-loading downtime window from 15+ hours to 1-2.

I held both of these on OUGN’s spring seminar this year, and this time I assume that the latter session will be unobstructed by (the ferry’s) safety-announcements. 🙂

Presentations from #OUGN17 uploaded


OUGN17 is over and I’m back home with new impressions. Definitely, once again, a job well done by the organizing committee.

Preparing, presenting and co-presenting 3 sessions, all held on friday, took quite a bit of work and nerve, but it was great fun since it went fairly well!

The three presentations have been uploaded to OUGNs site:

Continue reading “Presentations from #OUGN17 uploaded”

As time goes by: When joins spill to disk

Large joins may use full scans and hash joins. If your tables are large enough, this will fill up your process working memory and start spilling to your temp-tablespace. At that time a few important effects come into play:

Continue reading “As time goes by: When joins spill to disk”

ORA-00904 when inserting over a db-link

ORA-00904 when combining function-based index and db-link

There seems to be a bug related to expression based (function based) indexes when the optimizer creates a plan for inserts over a database link. The symptom is ORA-00904: … : invalid identifier.

Continue reading “ORA-00904 when inserting over a db-link”

If Oracle Scheduler is mysteriously not running

If you, like me, have stumbeled into trouble with the Scheduler not running jobs, you might realize that there are several ways to disable the Oracle scheduler. The parameter job_queue_processes is just one of them.

The more cunning one is that there’s a parameter, SCHEDULER_DISABLED, visible in the view dba_scheduler_global_attribute.

Continue reading “If Oracle Scheduler is mysteriously not running”