Startup Procedure in Oracle

We are going to have look at the most basic but most important part of Database. It is the first step of Database. We can call it initial stage of Database. Let’s have look at this image. It indicates some hidden simple and most meaningful message. Ladders are to meant to climb only it shows we have to go step by step towards our goal we can’t skip any single step to reach the top.

In (more...)

Progress status of index rebuild in Oracle


The game never ends after the creation of anything. It starts when it comes to the phase of maintenance. Today we are going to have look on the query which is used to lookProgress status of index rebuild/creation or long operation”. 



Let’s have look on the query.

select sid, target, to_char(start_time,'hh24:mi') start_time, elapsed_seconds/60 elapsed, round(time_remaining/60,2) "min_remaining",
message from v$session_longops where time_remaining > 0;

v$session_longops will show you the number of blocks scans out of the total block. Once (more...)

Change User Password in Oracle

In the world of technology, we use “USERNAME and PASSWORD “ multiple time in a day. As per nature, rule “CHANGE is a must.  Oracle Database also provides the option to change user’s password. The only superuser has a privilege to change the password.

Let’s have look at the steps which we use to change the password.

The user can able to change the password by login

#sqlplus username_1@service_name_1

SQL> password
Old password:
New  (more...)

Riga Dev Days 2018

rigaLast week I spent some time in Riga, Latvia. It was unusually warm, much to the consternation of the locals who wanted it to return to more normal sub-20C instead of the current 27C. Riga is a long way North.

It is also beautiful, friendly, and hosts a great conference! RigaDevDays is a Development conference, talking about Architecture, programming paradigms and also databases (which is usually where I fit in). img_4003-e1528018968617.jpgI was speaking about Oracle Optimization. (more...)

Index Bouncy Scan 4

There’s always another hurdle to overcome. After I’d finished writing up the “index bouncy scan” as an efficient probing mechanism to find the combinations of the first two columns (both declared not null) of a very large index a follow-up question appeared almost immediately: “what if it’s a partitioned index”.

The problem with “typical” partitioned indexes is that the smallest value of the leading column might appear in any of the partitions, and the combination (more...)

Why You Should Periodically Review Your Backups


So like every good Oracle DBA, you’ve created a few databases, decided that if they were worth creating and using they might also be worth backing up, and created some (more...)

OHUG Solutions Central – Come See The Experts

Don't try to describe the ocean if you've never seen it
Don't ever forget that you just may wind up being wrong

                                     -  From Mañana by Jimmy Buffet

Having been in the enterprise software business for longer than I really care to admit, I've witnessed an awful lot of smoke blown up an awful lot of skirts. (more...)

SQL Goodies In Oracle 18c….

Oracle database 18c has come up with some really useful features and the new features guide is the best place to get started-as always. In this post, which is the 1st post about 18c, I shall mention two very simple yet very helpful features introduced for SQL statements. So without further adiue, let’s get to it.

Showing SQLID in the SQL*Plus

If you have ever tried to do anything with a query that’s already been (more...)

Oracle Code London – Periscope Interview

Whilst in London Wednesday to present Microservices in a Monolith World at the Oracle Code London,  I also participated in an interview streamed via Periscope.  The interview can be seen at 


Min/Max upgrade

Here’s a nice little optimizer enhancement that appeared in 12.2 to make min/max range scans (and full scans) available in more circumstances. Rather than talk through it, here’s a little demonstration:

rem     Script:         122_minmax.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2018
rem     Purpose:
rem     Last tested
rem           Good path
rem           Bad path

create table pt1 (

Rittman Mead at Kscope 2018

Rittman Mead at Kscope 2018

Kscope 2018 is just a week away! Magnificent location (Walt Disney World Swan and Dolphin Resort) for one of the best tech conferences of the year! The agenda is impressive (look here) spanning over ten different tracks from the traditional EPM, BI Analytics and Data Visualization, to the newly added Blockchain! Plenty of great content and networking opportunities!

I'll be representing Rittman Mead with two talks: one about Visualizing Streams (Wednesday (more...)

Oracle database wait event ‘db file async I/O submit’ timing bug

This blogpost is a look into a bug in the wait interface that has been reported by me to Oracle a few times. I verified all versions from Oracle 11.2 version up to on Linux x86_64, in all these versions this bug is present. The bug is that the wait event ‘db file async I/O submit’ does not time anything when using ASM, only when using a filesystem, where (more...)

Index Bouncy Scan 3

This is a follow-up to a problem I had with yesterday’s example of using recursive CTEs to “bounce” along a multi-column index to pick out the unique set of combinations of the first two columns. Part of the resulting query used a pair of aggregate scalar subqueries in a select list – and Andrew Sayer improved on my query by introducing a “cross apply” (which I simply hadn’t thought of) which the optimizer transformed into (more...)


One of my maxims for Oracle performance is: “Don’t try to be too clever.” Apart from the obvious reason that no-one else may be able to understand how to adjust your code if the requirements change at a future date, there’s always the possibility that an Oracle upgrade will mean some clever trick you implemented will stop working.

While searching for information about a possible Oracle bug recently I noticed the following fix control (more...)

Index Bouncy Scan 2

I wrote a note some time last year about taking advantage of the “index range scan (min/max)” operation in a PL/SQL loop to find the small number distinct values in a large single column index efficiently (for example an index that was not very efficient but existed to avoid the “foreign key locking” problem. The resulting comments included pointers to other articles that showed pure SQL solutions to the same problem using recursive CTEs (“with” (more...)

UKOUG Call For Papers: Tech18, Apps18 and JDE18

The call for papers for Tech18, Apps18 and JDE18 is now open until Monday 4th June at 09:00. You have until then to submit an abstract for whatever you would like to talk about!


What would you like to talk about? You’re telling me you have nothing? I don’t believe you. Everybody has a story to tell. Your work has value an your experiences and wisdom will help others avoid your mistakes and follow your (more...)

Filtering LOBs

A two-part question about the FILTER operation appeared on the Oracle-L list server yesterday. The first part was a fairly common question – one that’s often prompted by the way the optimizer used to behave in older versions of Oracle. Paraphrased, it was: “Why is the total cost of the query so high compared to the sum of its parts?”

Here’s the query, and the execution plan.

    PHT. (more...)

Oracle SOA Suite 12c: How to deploy a BPEL onto a weblogic server

After you have created your BPEL inside Jdeveloper, you have to add an application server inside jdev:

 And here we go:

Then go to the applications view:

And after the deployment check via Application Server view, if the BPEL version was deployed:

Running Istio on Oracle Kubernetes Engine–the managed Kubernetes Cloud Service

imageIn a recent post, I introduced the managed Oracle Cloud Service for Kubernetes, the Oracle Kubernetes Engine (OKE): A logical next step when working with Kubernetes in somewhat challenging situations, for example with microservice style architectures and deployments, is the use of Istio – to configure, monitor and manage the so called service mesh. Istio – – is brand new – not even Beta yet, although a first production release (more...)

First steps with Oracle Kubernetes Engine–the managed Kubernetes Cloud Service

imageOracle recently (May 2018) launched its Managed Kubernetes Cloud Service (OKE – Oracle Kubernetes Engine) – see for example this announcement: Yesterday I got my self a new free cloud trial on the Oracle Public Cloud ( Subsequently, I created a Kubernetes cluster and deployed my first pod on that cluster. In this article, I will describe the steps that I went through: