Not Exists

Another question on a seemingly simple “not exists” query has appeared on OTN just a few days after my last post about the construct. There are two little differences between the actual form of the two queries that make it worth repeating the analysis.

The first query was of the form:


select from big_table
where  not exists (select exact_matching_row from small table);

while the new query is of the form:


select from big_table alias1
where  (more...)

ANSI expansion

Here’s a quirky little bug that appeared on the OTN database forum in the last 24 hours which (in 12c, at least) produces an issue which I can best demonstrate with the following cut-n-paste:


SQL> desc purple
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 G_COLUMN_001                        NOT NULL NUMBER(9)
 P_COLUMN_002                                 VARCHAR2(2)

SQL> select p.*
  2  from GREEN g
  3    join RED r on g.G_COLUMN_001 = r.G_COLUMN_001
  4    join PURPLE p on g.G_COLUMN_001 = p. (more...)

ASH

There was a little conversation on Oracle-L about ASH (active session history) recently which I thought worth highlighting – partly because it raised a detail that I had got wrong until Tim Gorman corrected me a few years ago.

Once every second the dynamic performance view v$active_session_history copies information about active sessions from v$session. (There are a couple of exceptions to the this rule – for example if a session has called dbms_lock.sleep() it will appear (more...)

select * from table where rownum=1

I never thought I would have to optimize so simple query as

select col1, col2, col4, col7 from table where rownum=1

(even though I read recently “SELECT * FROM TABLE” Runs Out Of TEMP Space)
But a few days ago frequent executions of this query caused big problems on the one of our databases(11.2.0.3) because of adaptive serial direct path reads.

I don’t know why, but I felt intuitively that full (more...)

New Version Of XPLAN_ASH Utility – In-Memory Support

A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.

As usual the latest version can be downloaded here.

This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 11.2.0.1).

As an extra however, (more...)

ORA-19599 block corruption when filesystemio_options=SETALL on ext4 file system using Linux

Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.

This was virtual machine running oldish 10.2.0.5.0 Oracle database.

I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)

New Version Of XPLAN_ASH Utility

A new version 4.2 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

There were no too significant changes in this release, mainly some new sections related to I/O figures were added.

One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings (more...)

Data Pump and AQ_TM_PROCESSES

First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]

Datapump Bug: Scheduler Jobs Not Imported in 11.2

Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP. It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window. Here’s an excerpt of a Data Pump Logfile: ORA-39083: Object type PROCOBJ failed to create with error: ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job […]

PeopleTools 8.54 Feature: Application Engine Trace File Enhancements

In this blog, we have been reviewing the new features of PeopleTools 8.54. Today is the turn of Application Engine, particularly on its troubleshooting. This release of PeopleTools include several enhancements on Application Engine tracing, which are outlined below:


  • The .AET trace file can now include the PeopleCode trace. This removes the need of checking the .AET file for the the non-PeopleCode steps and the .TRC file for the PeopleCode steps. Surely, .TRC files (more...)

Being Right and Proving It

One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.

In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)

Debugging Faulty Assumptions

I wanted to install Oracle JDevelper 12.1.3 – a version that I had been eagerly awaiting. Since my primary machine is a MacBook, I wanted to install it on OS X 10.9.3.

I downloaded the generic installer and found that the install didn’t run. Since OS X had disappeared from the documentation, I assumed that I would have to fiddle around until I found a combination of JDK and OS (more...)

New Version Of XPLAN_ASH Utility

A new version 4.1 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)

Database Shutdown With crsctl

I noticed something for the first time tonight when I was playing around in the Enkitec lab – something that I have been doing wrong for a while.  When working in the lab, I often rely on the crsctl command to shut down the entire cluster stack for me.  It’s really easy to use “crsctl stop cluster -all” followed by “dcli -l root -g ~/dbs_group /u01/app/11.2.0.4/grid/bin/crsctl stop crs” to get everything down (more...)

SCAN VIP Troubleshooting

We had a client that was running into a strange issue on their Exadata where new connections coming in through the SCAN were failing.  After doing some troubleshooting, it was discovered that it was related to one of the SCAN listeners not properly accepting requests from new sessions.  The VIP and listener were running, and everything looked normal.

We had the following SCAN setup:

SCAN VIP # VIP IP
1 172.25.2.70
2 (more...)

Wrong Result Bug using group-by placement optimization

Last week I’ve mentioned on Twitter that we ran into wrong result bug. We found workaround quickly but I’ve decided to spend some time to reproduce error and write blog post to warn you about this optimizer behavior.
Special thanks to my colleague who spotted odd results which led us to this finding.

My test (virtual) environment is:
OS: Oracle Enterprise Linux 5.8
DB: Oracle EE 11.1.0.7.12

In test I (more...)

November/December Highlights

In the Oracle technical universe, it seems that the end of the calendar year is always eventful. First there’s OpenWorld: obviously significant for official announcements and insight into Oracle’s strategy. It’s also the week when many top engineers around the world meet up in San Francisco to catch up over (more...)

When error say nothing about real issue

We want restore backup from source. So we took backup from source and copied it to /dba/share/MYDB on target. My collouge sent me email saying  restore failed with following error


rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 18 20:47:38 2013

Copyright (more...)

Oracle BI Publisher Desktop Microsoft Visual Basic System Error &H8000FFFF -2147418113 Catastrophic failure

Not too sure what I changed on my laptop, possibly just installation of another product or perhaps Microsoft or similar updates, but when I went to edit a BI Publisher RTF template I got the following error message on clicking ANY button in the BI Publisher plugin for Microsoft Word.

 (more...)

ORA-01722 (invalid number) over and over again

This is a translation of my German article which was requested through “Google Translate” many times. It seems that throughout the years this error and its mostly simple causes haven’t lost their significance. Here’s a sample scenario: A table’s VARCHAR2 column shall be searched for numeric entries; We use a query with a WHERE clause […]