The old question about truncate and redo (“does a truncate generate redo or not”) appeared on the OTN database forum over the week-end, and then devolved into “what really happens on a truncate”, and then carried on.

The quick answer to the traditional question is essentially this: the actual truncate activity typically generates very little redo compared to a full delete of all the data because all it does is tidy up (more...)

Less calls…more performance (part 2)

In the previous post, I mentioned that for a programming environment fetching rows from the database, then the method of

  • open a ref cursor
  • issue a fetch call
  • close the ref cursor

might not be appropriate for those situations where the result set is known be a single row (eg primary key lookup).

A better option might be to call a procedure and get those outputs as parameters.

And I broke a cardinal rule… (more...)

Less calls…more performance

In various programming environments, a common metaphor is to open a cursor on the database (a REF CURSOR in Oracle parlance), return that cursor handle to the calling environment, and then that cursor is used to fetch or “fill” a data object, which may map to a grid on screen, or just to an array in memory.

And that’s totally fine – its an effective means to obtain a result set from the database to (more...)

Enhancing A Moving Art Project to Beginning Robotics with Raspberry Pi

Back on August 7th, the RMOUG’s Quarterly Education Workshop, I walked everyone through using a Pibrella board to create an art or flyer project that was powered with a python script and a motor.

Today, we’re going to take that same code and reuse it with a simple robotics kit to control power and rotation speeds/time to the Insectoid.  This kit, puchased from the Go! store in Flat Irons Crossing mall, was $16 and can (more...)

Reminder: Great free computer science and Python programming class starts Wednesday

I mentioned this class earlier in a blog post but I wanted to remind people who read this blog that the class is starting again on Wednesday.  Here is the URL for the class: link

The class is completely free and taught at a very high level of quality.

It teaches computer science concepts that apply in any programming language but also teaches Python programming.

It is valuable information in the increasingly computer oriented world (more...)

Oaktable World 2015 San Francisco, Oct 26 & 27

Agenda for Oaktable World 2014, located at Creativity Museum, is

 time Monday Oct 26 Tuesday Oct 27
9 Mark W. Farnham – Minimizing the concurrency footprint of transactions with Logical Units of Work stored in PL/SQL Kyle Hailey – Virtual Data

Kerry Osborne – SQL Translation Framework

Kellyn Pot’Vin-Gorman, AWR Warehouse Trending and Analysis with OBIEE
11 Greg Rahn – Taming JSON with SQL: From Raw to Results

Marco Gralike – Improving XML performance (more...)

How to get the list of users assigned with responsibilities as per Operating Unit

Query :

SELECT hou.NAME,fpov.profile_option_value org_id,frv.responsibility_name, d.full_name,a.USER_NAME, b.START_DATE,b.end_date
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv,apps.FND_USER a,apps.FND_USER_RESP_GROUPS_all b,apps. per_all_people_f d,apps.FND_RESPONSIBILITY_TL res
fpov.level_value = frv.responsibility_id and b.responsibility_id = res.responsibility_id
and a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=frv.RESPONSIBILITY_ID and a.employee_id=d.person_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = ‘MO: Operating Unit’
AND fpov.profile_option_id (more...)

Issue with Perl in $ORACLE_HOME during installs

I’ve been doing some Enterprise Manager installs a bit more lately. At the same time, I’ve been working on Data Integration items such as GoldenGate and ODI.  What these products have in common are that they require an Oracle Database for a repository.  Needless to say I’ve been installing a lot of databases in test and production environments.  The one thing that has been consistent is the issue I keep seeing (more...)

Improve Performance using Oracle Smart Flash Cache

The article/blog is published on my company website… Improve Performance using Oracle Smart Flash Cache

Web Pages Not Databases – Part 2: Fail2ban, Apache, IP Addresses, Linux, SELinux

August 23, 2015 (Back to the Previous Article in this Series) I started using Linux in 1999, specifically Red Hat Linux 6.0, and I recall upgrading to Red Hat Linux 6.1 after downloading the files over a 56k modem – the good old days.  I was a little more wise when I upgraded to another […]

12c Upgrade – It can be slow!!!!

This post is for some of you who are planning to upgrade to 12c.I worked on upgrading single instance database from to, so thought to share my experience.

From 12c catupgrade.sql has been deprecated and Oracle has introduced script for upgrade. With script Oracle tries to perform as much work as possible in parallel, therefore minimizing downtime for upgrade.


Monitoring RMAN Operations

Just a reference to source and my version of the script.

This is for restore since there are OUTPUTS.

Script to monitor RMAN Backup and Restore Operations (Doc ID 1487262.1)

$ sqlplus / as sysdba @mon_rman_restore.sql

SQL*Plus: Release - Production on Sun Aug 23 01:14:31 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release -  (more...)

Windows 10 Again

DiagnosticsI wrote a few months ago about having a play with Windows 10 (here).

I’m visiting family today, catching up on all the Windows desktop (and mobile phone) support that I missed while I was away.

I purposely postponed the Windows 10 update on the desktops before I went away, but now I’m back I did the first of them.

The update itself was fine, but it did take a long time. Nothing (more...)

Presenting in Perth on 9 September and Adelaide on 11 September (Stage)

For those of you lucky enough to live on the western half of Australia, I’ll be presenting at a couple of events in both Perth and Adelaide in the coming weeks. On Wednesday, 9th September 2015, I’ll be presenting on Oracle Database 12c New Features For DBAs (and Developers) at a “Let’s Talk Oracle” event […]

X-Window Fun

When ssh -X to another host, I am able to use X-Windows.

[dinh@ca01ts~]$ ssh -X dinh@
dinh@'s password:
Last login: Fri Aug 21 11:55:47 2015 from
/usr/bin/xauth: creating new authority file /home/dinh/.Xauthority
[dinh@arrow ~]$ xclock
Warning: Missing charsets in String to FontSet conversion

However, sudo to another user and X-Windows breaks.

[dinh@arrow ~]$ sudo su - oracle
[sudo] password for dinh:
[oracle@arrow ~]$  (more...)

Avoiding public embarrassment with triggers

If you create a trigger that does not compile, any part of your application that refers to the table with the invalid trigger is basically dead until that trigger becomes valid.  Even if a DML on that table is not going affect any rows, you still wont be able to issue it.  For example,

SQL> delete from T where 1=0;
delete from T where 1=0
ERROR at line 1:
ORA-04098: trigger 'TRG'  (more...)

Messed-Up App of the Day: Crux CCH-01W

Today’s Messed-Up App of the Day is the “Crux CCH-01W rear-view camera for select 2007-up Jeep Wrangler models.”

A rear-view camera is an especially good idea in the Jeep Wrangler, because it is very difficult to see behind the vehicle. The rear seat headrests, the wiper motor housing, the spare tire, and the center brake light all conspire to obstruct much of what little view the window had given you to begin with.

PL/SQL CASE Statement

This is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL. Here is a simple example, which checks whether a given number is 1 or 2. You start with the word case. Then you add one or more conditions followed by the action to take if that condition is satisfied. Each condition is preceded by the word when. You finish with the words end case:

Python Pass the Pigs

So I don’t doubt that many parents are bleeding out money for kid’s school fees, supplies, clothing and other demands this time of year.  How many of you are in their local Target, Walmart or other store and after filling the cart with the necessary, the kids eye up the toy aisle and start to ask for something?

Even teens are not immune and may be asking for games.  If you could turn (more...)

The Future of IT Staffing- People vs. Robots

Image and video hosting by TinyPic

The Increasing Complexity of the IT Tech Stack Will Drive the Need for Robotic Automation

I’ve been involved in database technologies for 20 years now. During that time, I have read numerous prognostications from various industry pundits proclaiming that the next release of so-and-so database would be so simple to administer that the product would no longer require DBAs for support.  Replace “database” with any technology, and you’ll find that the same industry mantra occurs.