Truncating a timestamp to the second

We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value.  At first glance, TRUNC seems fine.

SQL> select trunc(localtimestamp,'MM') to_the_month from dual;


1 row selected.

SQL> select trunc(localtimestamp,'YYYY') to_the_year from dual;


1 row selected.

SQL> select trunc(localtimestamp,'DD') to_the_day from dual;


1 row selected.

But the moment you try apply a TRUNC down to (more...)

Reasons to upgrade to Enterprise Manager 13.2

By Sudip Datta-Oracle
Before, during and after the Open World, I have received numerous queries on the release of Enterprise Manager 13.2. This is an important release for Enterprise Manager 12c customers whose Premier Support expires this month and who traditionally wait for the release 2 of any product before upgrading, and for customers who have already upgraded to 13c, but want their critical issues addressed. And there are specific capabilities that are sought for (more...)

User Group Boards, Voting, Oh Yeah and ODTUG

I think we’ve just about all heard enough about voting this election year, but I want to discuss a slightly different angle and that’s when it comes to user groups.  As most people know, I’m pretty passionate about everyone contributing time to your local and national user groups.  I also think it’s important to take pride in and be responsible for the promises you make.


I was sent (more...)

EM13c: Get List of URLs Monitored by Beacons

When you need to monitor web services, you can use EM beacons. A beacon is a target that allows the EM Agent to remotely monitor services. You can add beacons to any EM agent using “Add Non-Host Targets by Specifying Target Monitoring Properties” option in the Add Targets Manually page. After adding a beacon, you can configure it to fetch a web page and test the status of the service according to the result of (more...)

HugePages speeds up Oracle login process on Linux

We bumped a Linux database up to a 12 gigabyte SGA and the login time went up to about 2.5 seconds. Then a Linux admin configured 12 gigabytes of HugePages to fit the SGA and login time went down to .13 seconds. Here is how I tested the login time. E.sql just has the exit command in it so this logs in as SYSDBA and immediately exits:

$ time sqlplus /  (more...)

Pass Summit 2016 and Old School MSSQL DBA Skills

I’ll be attending my very first Pass Summit next week and I’m really psyched!  Its an amazing event and for those of you who are my Oracle peeps, wondering what Summit is, think Oracle Open World for the Microsoft SQL Server expert folks.


I was a strong proponent of immersing in different database and technology platforms early on.  You never know when the knowledge you gain in an (more...)

Google’s “Material design”

materialdesign_principles_metaphorAs a “born again UX/UI” enthusiast I was quite interested when some of my Google services started telling me about the new UI they were introducing. As part of the “do you want to see more” links they pushed me across to Google’s Material design pages.

I had a bit of mixed reaction to a lot of it. Some of the stuff was straight from the ministry of the bleedin’ obvious, while other stuff was more (more...)

Did you know #15 – Role Based Stuff

If you are using Data Guard and standby databases, you probably need to do stuff based on the database role. For example, have some services start on the primary database, whichever that is. You might want to update some tables upon failover/switchover and more. There are two mechanisms for that in Oracle databases: For services, … Continue reading Did you know #15 – Role Based Stuff

While upgrading to I faced error ORA-01830 / ORA-06512

The other day I was running an upgrade for a client that is using ACLs ( Access Control Lists) from to If you have been doing upgrades to 12c, you know that when running the -n 4 catupgrd.sql it entails 73 steps. So this upgrade failed in step 65 with the following error (I have trimmed the output for reading purposes) :

Serial   Phase  (more...)

Conjuctive Normal Form

I recently tweeted about a comment I’d picked up at the Trivadis performance days regarding tablescans and performance.

“If you can write your SQL in conjunctive normal form it can help the optimizer to offload more predicates”

Inevitably someone asked me if I had an example to demonstrate this – I didn’t, and still don’t really, but here’s an interesting demo based on an example from the Oracle In-Memory blog showing how the optimizer will (more...)

AWR Summary Data Extracts

A long time ago (in a galaxy far away) I wrote a series of blog posts about directly extracting useful sets of data from the AWR snapshots in the DBA_HIST tables for subsequent analysis and graphing using things like Excel. This post is to summarise where I got to on this, and provide links back to the main posts for future reference.

A guiding principle of my approach was that I wanted to avoid the (more...)

Taking a peek at SYS_CONTEXT

There was Twitter discussion about using context variables, accessed via SYS_CONTEXT within queries. It’s an easy means of passing parameters into a view. The question that got asked was – does a sys_context variable act exactly like a bind variable. Let’s take a look at a simple demo to explore that.

We’ll create a table with 1 row with a value of ‘a’, and 100,000 rows with a value of ‘b’. We’ll index that column, (more...)

Translating SQL (a migration tool)

When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology.  But there are not many “perfect worlds” out there  in the IT landscape Smile  So sometimes we have to “make do” with the time and budget constraints that are placed upon us.  To (more...)

Quickly built new Python graph SQL execution by plan


I created a new graph in my PythonDBAGraphs to show how a plan change affected execution time. The legend in the upper left is plan hash value numbers. Normally I run the equivalent as a sqlplus script and just look for plans with higher execution times. I used it today for the SQL statement with SQL_ID c6m8w0rxsa92v. It has been running slow since 10/11/2016.

Since I just split up my Python graphs into multiple smaller scripts (more...)

Problems with big SGAs (>200G) on Linux

I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:

ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256

This was strange because ipcs -a showed all shared memory segments (more...)

Tim Gorman at AZORA meeting tomorrow in Scottsdale

VirtualBox 5.1.8

VirtualBox 5.1.8 has burst through the chest of it’s host organism.

Downloads and changelog are in the usual places. It’s a maintenance release, so there are a bunch of bug fixes in there.

I’ve installed it on macOS (Sierra), Windows 7 and Oracle Linux 6 with no problems.SO far no dramas. 🙂

Happy upgrading!



VirtualBox 5.1.8 was first posted on October 19, 2016 at 2:19 pm.
©2012 "The (more...)

All Flash Arrays: Active/Active versus Active/Passive


I want you to imagine that you are about to run a race. You have your trainers on, your pre-race warm up is complete and you are at the start line. You look to your right… and see the guy next to you, the one with the bright orange trainers, is hopping up and down on one leg. He does have two legs – the other one is held up in the air – he’s just choosing to (more...)

Oracle Critical Patch Update for October 2016

The Critical Patch Update for October 2016 was released on October 18th, 2016. Oracle strongly recommends applying the patches as soon as possible. 

Visit the URL below for more updates

Locked rows and lunch breaks ? A simple fix

Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem.  That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling back the transaction.  The metaphor commonly used was the “someone locks a row in a table and then goes out for lunch”, with them being totally oblivious to (more...)