Truncating a timestamp to the second

We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value.  At first glance, TRUNC seems fine.

SQL> select trunc(localtimestamp,'MM') to_the_month from dual;


1 row selected.

SQL> select trunc(localtimestamp,'YYYY') to_the_year from dual;


1 row selected.

SQL> select trunc(localtimestamp,'DD') to_the_day from dual;


1 row selected.

But the moment you try apply a TRUNC down to (more...)

HugePages speeds up Oracle login process on Linux

We bumped a Linux database up to a 12 gigabyte SGA and the login time went up to about 2.5 seconds. Then a Linux admin configured 12 gigabytes of HugePages to fit the SGA and login time went down to .13 seconds. Here is how I tested the login time. E.sql just has the exit command in it so this logs in as SYSDBA and immediately exits:

$ time sqlplus /  (more...)

Taking a peek at SYS_CONTEXT

There was Twitter discussion about using context variables, accessed via SYS_CONTEXT within queries. It’s an easy means of passing parameters into a view. The question that got asked was – does a sys_context variable act exactly like a bind variable. Let’s take a look at a simple demo to explore that.

We’ll create a table with 1 row with a value of ‘a’, and 100,000 rows with a value of ‘b’. We’ll index that column, (more...)

Translating SQL (a migration tool)

When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology.  But there are not many “perfect worlds” out there  in the IT landscape Smile  So sometimes we have to “make do” with the time and budget constraints that are placed upon us.  To (more...)

Quickly built new Python graph SQL execution by plan


I created a new graph in my PythonDBAGraphs to show how a plan change affected execution time. The legend in the upper left is plan hash value numbers. Normally I run the equivalent as a sqlplus script and just look for plans with higher execution times. I used it today for the SQL statement with SQL_ID c6m8w0rxsa92v. It has been running slow since 10/11/2016.

Since I just split up my Python graphs into multiple smaller scripts (more...)

Tim Gorman at AZORA meeting tomorrow in Scottsdale

Locked rows and lunch breaks ? A simple fix

Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem.  That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling back the transaction.  The metaphor commonly used was the “someone locks a row in a table and then goes out for lunch”, with them being totally oblivious to (more...)

Generating rowids

We have several posts on AskTom where it is described how to “carve up” a table into equi-sized chunks in order to (say) perform a task in parallel on that table.  Here is an example of one.

Much of this has nowadays been obsoleted by the DBMS_PARALLEL_EXECUTE package, but in either instance, one key point remains:  When you are generating rowids, there is no guarantee that the rowid you generate is either valid or (more...)

Running external programs from the scheduler

Although I normally use the job or scheduler facility to run database-centric style processes, most commonly PL/SQL programs, there is nothing to stop you from using the scheduler to gain control over tasks that might normally need to be done outside of the database.  And of course, in the world of virtualization, cloud and other such innovations, the ability to initiate and perform tasks from within the database becomes increasingly useful, since often access (more...)

Thinking about using Python scripts like SQL scripts

I’ve used Python to make graphs of Oracle database performance information. I put the scripts out on GitHub at As a result I’m keeping my Python skills a little fresher and learning about git for version control and GitHub as a forum for sharing Open Source. Really, these Python scripts were an experiment. I don’t claim that I have done any great programming or that I will.

But, as I review what I have done (more...)

October Webinar Series: Digital Transformation, Forms and Mobile

This year’s Oracle Open World was an exciting experience for AuraPlayer, with many flocking to our booth for live demos and attending Mia’s sessions with clients like Rogers Group Inc. We were sad that many were unable to attend the conference, so we are bringing Oracle Open World to you in our October webinar series!

In the first installment of this series, “Digital Transformation: From Legacy to Enterprise Mobility,” we will demonstrate step-by-step how best (more...)

Exceeding 1 million partitions

In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions.  As we saw, the partition numbers are preordained based on the low boundary and the interval size.

That also creates an interesting scenario that can catch people out – you might exceed the allowable number of partitions, with an empty table !

SQL> create  (more...)

Pending statistics and partition queries

This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I thought I would bring it to your attention.  The issue occurs when optimizing a query that accesses a single partition via pending statistics.

SQL> create table t
  2  partition by  (more...)

The non-existent partition

Things get a little interesting in the data dictionary when it comes to interval partitions.  Consider the following example:

SQL> create table t
  2  partition by range(x) interval(1)
  3  ( partition p0 values less than (1) )
  4  as
  5  select rownum x from dual connect by level <= 5;

Table created.

SQL> select count(*)
  2  from user_tab_partitions
  3  where table_name = 'T';


So our table has 6 partitions. Let’s now (more...)

Need classes directory to run ENCRYPT_PASSWORD on PeopleTools 8.53

I had worked on creating a Delphix virtual copy of our production PeopleTools 8.53 database and wanted to use ENCRYPT_PASSWORD in Datamover to change a user’s password. But I got this ugly error:

Error: Process aborted. Possibly due to JVM is not available or missing java class or empty password.

What the heck! I have used Datamover to change passwords this way for 20 years and never seen this error. Evidently in PeopleTools 8.53 (more...)

OTN Appreciation Day: Tom Kyte #ThanksOTN

To answer Tim Hall’s call to appreciate OTN, I could have written about my go-to feature, the MATCH_RECOGNIZE clause, or my go-to development tool, Oracle SQL Developer. Instead, I’d like to salute my go-to “Oracle Technology” guy for over 10 years, Tom Kyte. It was 2005. After almost 25 years in IT, I knew something about […]

OTN Appreciation Day: Easy Execution Plans

As part of the #ThanksOTN idea on Twitter, my favourite Oracle feature is the ability to easily view and analyse execution plans for queries. Time and time again I’ve needed to see how a query is running, and Oracle databases make it easy to view the execution plan. You can view it in either a text format […]

OTN Appreciation Day : Prebuilt Developer VMs

I learnt a lot from the entire Oracle Technology Network and I still do. One of the best features of OTN these days, IMHO, is the ability to download prebuilt Virtual Machines. Since I am a developer and not an administrator, I don’t like to be bothered with stuff like how much disk space do I need, how many cores should I use etc. I can just download a Virtual Box image, import it and (more...)

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');

JDBC executeBatch looks odd in AWR

A project team asked me to look at the performance of an Oracle database application that does a bunch of inserts into a table. But, when I started looking at the AWR data for the insert the data confused me.

The SQL by elapsed time section looked like this:


So, 1514 executions of an insert with 1 second of elapsed time each, almost all of which was CPU. But then I looked at the SQL text:


Hmm. (more...)