Excessive Locking when Dropping a Table in 11g

I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys.
A quick investigation revealed the cause – the DROP TABLE operation tried to lock another table (in the quite aggressive “Share” mode) that was referenced by a (more...)

A Single Query with Many Filter Combinations – Part 3

In a recent post I suggested a way to write a single SQL query that filters a table by one or more of several columns.
Here is the query from that post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id = :job_id
    union all
    select rowid rid from employees where manager_id = :manager_id

A Single Query with Many Filter Combinations – Part 2

In the previous post I suggested a way to write a single SQL query that filters the EMPLOYEES table (as an example) by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME.

Here is the query from the previous post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id  (more...)

Adding a Datafile to Temp Tablespace

When monitoring Tablespace Usage (see my Tablespace Usage blog post for more info), there comes a point when you need to add a datafile to the temp tablespace to allow for growth and more importantly get below a monitoring threshold for example in OEM or OpsView.

Query to see Current Temp Datafiles State

To see the current state of the temp datafiles:

set pages 999
set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME,  (more...)

Friday Philosophy – Explaining How Performance Tuning Is Not Magic?

Solving performance issues is not magic. Oh, I’m not saying it is always easy and I am not saying that you do not need both a lot of knowledge and also creativity. But it is not a dark art, at least not on Oracle systems where we have a wealth of tools and instrumentation to help us. But it can feel like that, especially when you lack experience of systematically solving performance issues.

SQL statement (more...)

Tool to Assist with Basic SQL Analysis

I just came back from RMOUG Training Days conference. It was my first time in Colorado (and obviously my first RMOUG training day) and it was really great (I wrote about it in another post). During my second session (From 4 Minutes to 8 Seconds – about a real SQL tuning case I had quite … Continue reading Tool to Assist with Basic SQL Analysis

SQL with Apache Spark, easy!

Reading about cluster computing developments like Apache Spark and SQL I decided to find out.

What I was after was to see how easy is to write SQL in Spark-SQL. In this micro-post I will show you how easy is to SQL a JSON file.

For my experiment I will use my chrome_history.json file which you can download from your chrome browser using the extension www.JSON-XLS.com. To run the SQL query on (more...)

SQL Magic Squares – or Why the Optimizer does not like Magic

A long-time player at the Oracle Dev Gym tried his hand at generating Magic Squares using SQL.

When he attempted to tune his statement a bit, he was surprised that it didn't go a little faster as expected, rather it went from 2 minutes to 45 minutes? At that point he contacted me to see if I could explain the mystery...

So thank you, Hamid Talebian, for the interesting case. It was fun to play (more...)

Ergänzungen zu coalesce und NVL

Vor längerer Zeit hatte ich hier gelegentlich auf Artikel verwiesen, die sich mit dem unterschiedlichen Verhalten von NVL und coalesce beschäftigten und einerseits auf die short-circuit evaluation mit coalesce und andererseits auf deren Ausklammerung im Fall von Sequencen hinwiesen. Jetzt haben die Herren Lewis und McDonald dazu ergänzende Beobachtungen geliefert.
  • Jonathan Lewis weist darauf hin, dass coalesce beim costing schlechter abschneidet als NVL, weil es mit dem Standardwert von 1% für Gleichheit operiert, der für (more...)

How to fix queries on DBA_FREE_SPACE that are slow

I found myself in a situation where OpsView a monitoring tool, was having difficulty monitoring the tablespaces for a particular pluggable database.

Upon investigation it was found the queries against the dictionary table DBA_FREE_SPACE were taking a very long time:

SQL> set timing on
SQL> select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = 'USERS';


Elapsed: 00:00:10.98

There are 60 tablespaces in this pluggable database, which the time varied (more...)

Data driven APEX icons

We have an application written with a heavily customised Theme 25 built for 10" tablets, and we feel the Universal Theme justifies the move, in part because of the surrounding ecosystem. Check out this forum discussion on the topic.

We've been looking through the packaged applications for applied ideas, and using the Universal Theme sample application as a component reference.

I came across a requirement where we had a list of items that indicated completion (more...)

JSON_TABLE and the Top 2000

Oracle database 12c provides native JSON parsing. It is relatively easy to go from data in a JSON format to a relational representation.
In this example I will use data from the Top 2000. Every year in The Netherlands a playlist is produced by popular vote and fully broadcast between Christmas and the last day of the year, with the number one being played just before the New Year. You can see the complete list (more...)

“Collection iterator pickler fetch”: pipelined vs simple table functions

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it’s much better to create another pipelined function which will get and return its results through PIPE ROW().

A bit more details:

Assume we need to return collection “RESULT” from PL/SQL function into SQL query “select (more...)

Friday Fun SQL Lesson – union all

Our office kitchen is unavailable this Friday, so the call was put out for pub lunch.

After a couple of replies I decided to enter my reply, in full nerd mode.
select * from people_coming_to_lunch;


3 rows selected.
And of course one of the other SQL geeks (name redacted) replied to extend the data set.
select * from people_coming_to_lunch
select 'Shanequa'
from dual;
And I couldn't help myself. I (more...)

New READ Object Privilege in 12cR1

In writing a blog post about:
Creating a Read Only Database User Account in an Oracle Database

It came to my attend of the new “READ” object privilege, which is a New Feature in
Changes in Oracle Database 12c Release 1 (
READ and SELECT Object Privileges

The “SELECT” object privilege in addition to querying the table, allow the user to:
LOCK TABLE table_name (more...)

Creating a Read Only Database User Account in an Oracle Database

It can be quite common to create a “Read Only” database user account in an Oracle database.  To do this is pretty simple using the principle of least privilege:


Expected output:


User created.


Grant succeeded.


To see specific tables for a schema:

SET PAGES  (more...)

Easy(lazy) way to check which programs have properly configured FetchSize

  ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch
from v$sql s
and s.executions    >1
and s.fetches       >1
and s.module is not null
and s.command_type  = 3    -- SELECTs only
and s.program_id    = 0    -- do not account recursive queries from stored procs
and s.parsing_schema_id!=0 -- <> SYS
group by s.module
order by rows_per_fetch desc nulls last

Comparing queries…

How do you compare a rewritten query to its original version? Most of the time I just run a MINUS operation on the original and new query. Actually I execute two. Old query (A) MINUS New query (B) and vice versa (B) MINUS (A). Both should result in no rows. That way I thought I had proven that the resultsets for both queries are equal.
But there is a flaw in this assumption.

What if (more...)

KSQL: Streaming SQL for Apache Kafka

KSQL: Streaming SQL for Apache Kafka

Few weeks back, while I was enjoying my holidays in the south of Italy, I started receiving notifications about an imminent announcement by Confluent. Reading the highlights almost (...I said almost) made me willing to go immediately back to work and check all the details about it.
The announcement regarded KSQL: a streaming SQL engine for Apache Kafka!

Identifying Conditional Navigation Content References

As PeopleSoft customers upgrade to Fluid-enabled applications, it is quite common to start with Fluid disabled, and then implement Fluid behavior post go-live. It is all about change management and an organization's ability to digest change. Even though Oracle has set retirement dates for certain Classic components, with the first wave retiring December 31, 2017, all Classic functionality is still supposed to be present and available. But if you have opened a recent PeopleSoft image (more...)