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...)

Riga Dev Days 2018 : The Journey Home

Cut to the end for the conference thank you messages if you can’t be bothered to read about my travel traumas. 🙂

It was a 04:30 start to get myself together, check out of the hotel and walk across to meet Chris and Ionut to share a taxi to the airport.

Chris used his magic credit card to sign us in as guests to business lounge for what was meant to be a quick pitstop. Pretty (more...)

MySQL client slow to display the first row of a large result set

A coworker of mine asked me to look at a MySQL query that was running longer than we want it too. If they added a LIMIT 1000 clause at the end of the query it popped up the results in a couple of minutes but without the LIMIT clause it apparently hung forever without returning a row of the result set. I figured out that the query was returning 4 million rows with a bunch (more...)

Friday Philosophy – Despair of the Dyslexic Developer and Your Help Please

Like a surprisingly large number of people, I’m dyslexic. I’ve mentioned this before, describing how I found out I was dyslexic and also how I think it is sometimes used as an odd sort-of badge of distinction. I am mildly dyslexic, the letters do not try to “merge or run away” from my eye, if I hit a large word I am unfamiliar with I can visually chop it up and get through it. But (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...)

Oracle Tidbits – May 2018 #oratidbit

“Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter, and Google+ during weekdays in May 2018. Hope you find these helpful to learn something new or to remind you of its existence and use. #oratidbit #db18c When cloning a non-CDB as a PDB or relocating a PDB, use the DBMS_PDB.EXPORTRMANBACKUP procedure to export RMAN backup metadata into the PDB dictionary, enabling backups …

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...)

Long-running delete of synopses during table stats gathering


I recently encountered one long-running session and several blocked sessions, all performing the same delete operation.

--SQL_ID dsf21kcbjqfyk

One session had been running for several hours, and the other sessions were all blocked by a row lock from the first session. Unfortunately, each of these sessions was trying to gather stats on new partitions of tables as part (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 18c Autonomous Health Framework (AHF) – Part 1

Recently I had to present at eProseed AnualTech conference in Luxembourg and I was requested  to present a topic something about Oracle18c.

Obviously I don't want to talk and repeat the same about Autonomous Database, many experts already said much on this. I then decided to pick a topic which really helps DBAs, Administrators and finally to the organization. I was really fascinated about Oracle 18c autonomous health framework concepts and decided to do a (more...)

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 (

Python script to backup remote directory using sftp

This is the sftp version of the ftp script I published in an earlier post. It logs into a remote host using sftp and backs up a remote directory recursively backing up all subdirectories.

This one uses Paramiko instead of ftplib so you can do encrypted file copies.


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...)

utlrp weird behavior with INVAID objects in an Orace EBS database

In one of the recent database migration & upgrade activities, I have migrated an Oracle EBS database to a new DB host and upgraded to After migration and post upgrade, an utlrp.sql was ran to validate the 100k invalid objects in the database.

Weirdly, during the utlrp execution and when the INVALID objects count goes to 200, the INVALID objects counts started to increase again, and (more...)

Traditional Export/Import Fail With ORA-01031 in DB Vault Environment Starting Release

Oracle Database Vault was configured on RAC database v11.2.0.3 and appropriate permissions given to allow the regular duties, such as data pump export/import etc.

However, when classical/traditional import (a dump file from v9 database) was performed, the import on database failed with the following errors:


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...)

flipping performance

Recently I had a request to check "if there is any problem with the database at <specific times>".
You can imagine, there was no problem. Nothing in alert.log, no tracefiles, no locks or oddities in ASH/AWR.

I had to ask back & forth to get some more information about the issue. The information I got was:
"we use a statement SELECT * FROM table(some_function('P1', 'P2')) - and it took longer than 10 sec at (more...)