Using dynamic variables for shell scripting

So you may ask, why go through all the trouble?

Have you ever had to move scripts from one directories to another to find out the script is now broken?

This is due to the hard coding of the location for SQL script in the shell script.

When possible, I would like to separate shell / SQL scripts so they can be run individually and easier for debugging.

Don’t you hate it when copy/paste SQL (more...)

VirtualBox 5.0.6

VirtualBox 5.0.6 has been released.

It’s a maintenance release. You can find the downloads and changelog in the usual places.

Happy upgrading!



VirtualBox 5.0.6 was first posted on October 3, 2015 at 2:31 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Using sed to format controlfile trace

Do you find it cumbersome having to format controlfile trace?

For the most part, it’s a manual and tedious process.

Using sed, it may be possible to automate task.

RESUSE overwrites existing controlfile trace.

alter database backup controlfile to trace as ‘/tmp/cf.sql’ REUSE RESETLOGS;

ARROW:(SYS@hawklas):PRIMARY> alter database backup controlfile to trace as '/tmp/cf.sql' RESETLOGS;
alter database backup controlfile to trace as '/tmp/cf.sql' RESETLOGS
ERROR at line 1:
ORA-01277: file '/tmp/cf.sql'  (more...)

Online Partition Move fails with ORA-00932 –

Oracle 12c introduced feature to move table partitions and sub-partitions as online operations.So, this blog is related to this feature and the issue I faced.

The database was upgraded from to and this issue is seen in upgraded databases only.

Lets start!!!

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    partition sale_jan values less than (to_date('01-02-2015','dd-mm-yyyy')),
    partition  (more...)

Pythian at Oracle Open World 2015


Are you excited for this year’s Oracle Open World 2015? Pythian will once again be attending and participating in a number of marquee presentations and events.

This event is an amazing opportunity to hear from the finest minds in the Oracle space from Pythian and across the indusrty. We will be sending our Oracle experts to deliver some compelling presentations, packed with best practices, expert tips and advice that you won’t want to miss (See (more...)

Log Buffer #443: A Carnival of the Vanities for DBAs

This Log Buffer Edition finds and publishes blog posts from Oracle, SQL Server and MySQL.


  • SCRIPT execution errors when creating a DBaaS instance with local and cloud backups.
  • Designing Surrogate vs Natural Keys with Oracle SQL Developer.
  • EBS General Ledger – Accounting Hub Reporting Cloud Service.
  • Oracle Database Standard Edition 2 is available.
  • Disable “Build After Save at JDeveloper” 12c.

SQL Server:

  • Learn where to get the latest installation and learning resources for the (more...)

The Data Warrior Goes to Oracle Open World 2015 (#OOW15)

Yes boy and girls, it is that time of year again. Time for the BIG show in San Francisco – OOW15 I will as usual, attend, but only for a few days this year (been away from home too much). My Talk I will be presenting my session Worst Practices in Data Warehouse Design (UGF9985) […]

Oracle OpenWorld Preview event – 8 oktober

Natuurlijk organiseren we ook dit jaar weer een Oracle OpenWorld Preview event. AMIS vaardigt 10 collega’s af naar de 2015 editie van Oracle OpenWorld. Een aantal van hen geeft daar ook een presentatie. Die presentaties krijg je tijdens dit preview event alvast te horen, evenals een aantal presentaties van andere sprekers.

Ook kun je luisteren naar sessies over onderwerpen die hoog op de OOW15 agenda staan.


Je kunt de volgende sprekers verwachten:

Lucas Jellema (more...)


Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.

  lv_input  VARCHAR2(100 (more...)


Here’s a live on from OTN – here are a couple of extracts from the problem statement:

We’re experiencing an issue where it seems that the query plan changes from day to day for a particular procedure that runs once a night.
It’s resulting in a performance variance of 10 second completion time vs 20 minutes (nothing in between).
It started occurring about 2 months ago and now it’s becoming more prevalent where the bad (more...)

Oracle Database Developer Choice Awards : Spread the Love!

You’ve probably already heard about the Oracle Database Developer Choice Awards, Steven Feuerstein wrote about them here, but this is just a quick reminder to get your votes in.

This year there are five categories to vote for:

I am always a little nervous about “popularity contests”, but I would suggest you look through the finalists for each category and vote for someone if (more...)

How the log writer and foreground processes work together on commit.

(warning: this is a rather detailed technical post on the internal working of the Oracle database’s commit interactions between the committing foreground processes and the log writer)

After the Trivadis Performance days I was chatting to Jonathan Lewis. I presented my Profiling the log writer and database writer presentation, in which I state the foreground (user/server) process looks at the commit SCN in order to determine if its logbuffer contents are written to disk by (more...)

Oracle Midlands Presentation

Ever wonder why your SQL execution plan has changed? Wondering why your boss is shouting something about “traders can’t trade” or “shoppers can’t spend” or “well that’s the Airport shut!” ?

When you write SQL, Oracle runs it through the Optimiser to determine the fastest way to access the data. This access path sometimes changes without anyone noticing, sometimes with sub-optimal results. I’m giving a presentation at an OracleMidlands even (more...)

Master Notes

MoS has a number of “Master Note” documents which pop up from time to time while I’m checking for known problems or solutions; they get extended from time to time (and, conversely, link to some articles which are clearly no longer relevant to current versions). This is just a tidied up list of a few of the master notes that I’ve jotted down over time. The Data Dictionary overview appeared in my daily “Hot Topics” (more...)

Oracle 12c RAC on Oracle Linux 6 and 7 using NFS

linux-tuxFollowing on from the last post, I’ve brought my NFS RAC stuff up to date also.

I noticed I had not done a RAC install using NFS on Oracle Linux 6, so I threw that in for good measure too. :)

Just as a little (more...)

PDB Saved state –

Prior to version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from, this can be done with PDB save state feature

SQL> show pdbs

---------- ----------------- ---------- ----------

Lets create a new PDB


latch: cache buffers chains and rollback

I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event

load average: 258.52, 244.27, 226.15

select username,sql_id,event,count(*)  (more...)

Identifying database link usage

As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.

The first option was to look and see if auditing can be used. The manual states “You can audit statements that (more...)

Result Cache 2

Following on from my earlier posting of problems with temporary table and the PL/SQL result cache (a combination which the manuals warn you against) here’s another problem – again, to a large degree, self-inflicted.

Imagine you have a complex report involving a large number of financial transactions with a need to include calculations about current exchange rates. Unfortunately the rules about calculating the appropriate exchange rate for any transaction are complex and you find you (more...)

Migrate to the Oracle Database Cloud Service

On the 6th of October the Oracle Cloud Day 2015 takes place in The Netherlands. iAdvise is proud to be a bronze sponsor on what promises to be a day filled with interesting sessions.

One of the main topics will be the Oracle Database Cloud service. In this blog post, which was published in the OGh Visie earlier this year, you can read what the Oracle Database Cloud Service is, which configuration possibilities there (more...)