ASSM Truncate.

Here’s one that started off with a tweet from Kevin Closson, heading towards a finish that shows some interesting effects when you truncate large objects that are using ASSM. To demonstrate the problem I’ve set up a tablespace using system allocation of extents and automatic segment space management (ASSM).  It’s the ASSM that causes the problem, but it requires a mixture of circumstances to create a little surprise.

	tablespace test_8k_auto_assm
	datafile	-- OMF
	SIZE 1030M
	autoextend  (more...)

An idealized log management and analysis system — from whom?

I’ve talked with many companies recently that believe they are:

  • Focused on building a great data management and analytic stack for log management …
  • … unlike all the other companies that might be saying the same thing :)
  • … and certainly unlike expensive, poorly-scalable Splunk …
  • … and also unlike less-focused vendors of analytic RDBMS (which are also expensive) and/or Hadoop distributions.

At best, I think such competitive claims are overwrought. Still, it’s a genuinely (more...)

Oracle IO wait events: db file sequential read

(the details are investigated and specific to Oracle’s database implementation on Linux x86_64)

Exadata IO: This event is not used with Exadata storage, ‘cell single block physical read’ is used instead.
p1: file#
p2: block#
p3: blocks

Despite p3 listing the number of blocks, I haven’t seen a db file sequential read event that read more than one block ever. Of course this could change in a newer release.

One of the important things (more...)

Nobody Bunts With Two Strikes

Once upon a time, I coached a young women's fast pitch softball team.  Big adventure, as most of my coaching experience is with baseball, and I really enjoyed it.  One game, the opposing team's catcher was hitting with two outs and two strikes.  I shouted out to my team to stop covering the bunt - nobody bunts with two strikes (because a foul ball off a bunt attempt is strike three).  So my team's infield (more...)

E-Business Customers

It seems the Oracle E-Business Suite stymies newbies. It’s complex and requires some research before you try to develop customization.

Having just fielded a question about how to manage, search, create and update customers for a third party order entry system, I was surprise that they didn’t know about Oracle’s Trading Community Architecture (TCA). They likewise didn’t know there was already a documented process, like this process defined in the Oracle Trading Community Architecture User (more...)

Group By Bug

This just in from OTN Database Forum – a surprising little bug with “group by elimination” exclusive to 12c.

alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';

       /* optimizer_features_enable('')*/
       trunc (ts,'DD') ts1, sum(fieldb) fieldb
from (
        ts, max(fieldb) fieldb
  from (
  select trunc(sysdate) - 1/24 ts, 1 fieldb from dual
  union all
  select trunc(sysdate) - 2/24 ts, 2 fieldb from dual
  union all
  select trunc(sysdate) - 3/24 ts, 3 fieldb from dual
  union  (more...)

EM12c and Windows OS Failover Cluster- Keep it Simple

This is going to be a “KISS”, (Keep it Simple, Silly) post.  I was offered a second Windows cluster to test on when my original one was required for QA.  It was a gracious offer, but I also found out why a cluster should be kept simple and this cluster was anything but simple.  Many times, issues are created and we’ll never see them coming until we go through the pain and (more...)

Oracle, Parallelism and Direct Path Reads… on Flash


Guest Post

This is another guest post from my buddy Nate Fuzi, who performs the same role as me for Violin but is based in the US instead of EMEA. Because he’s an American, Nate believes that “football” is played using your hands and that the ball is actually egg-shaped. This is of course ridiculous, because as the entire rest of the world knows, this is football whereas the game Nate is thinking of is (more...)

Sometimes the cause of a TNS error is ….

A couple of months ago one of my customers had a failed data ware house report. There was a ORA-12592 (TNS) error message generated.
I turned out not to be the only TNS error. During a couple of weeks similar TNS-errors were generated. Not only the ORA-12592 error but also ORA-12514 and ORA-12571 errors.

We did some extensive sqlnet tracing but we didn’t find the cause there.

Finally the cause turned out to be a (more...)

How to make a time consistent export dump using the expdp datapump utility

In those old days when there was the exp utility we made a time consistent export dump by using the consistent=y parameter.
But today, in fact a couple of years already, we mostly use the expdp datapump utility. How should we make a time consistent export using datapump?
For that we use the flashback_time or flashback_scn parameter. In this post I show you how to set the flashback_time parameter.

The flashback_time parameter needs as input (more...)

Monitor Dell PERC when running on Oracle Virtual Server 3.x

In this article I will try to explain how to monitor your Dell Raid Controller with Cloud Control when running Oracle Virtual server 3.x (OVS3.x).

Some time ago a physical drive broke down on one of our Dell PowerEdge Servers. Not causing any problem because it was configured in a raid set using the Dell build in PowerEdge Raid Controller( hereafter PERC). However, we did not notice this breakdown until also a second (more...)

Oracle Magazine-January/February 2001

The headline articles of Oracle Magazine for January/February 2001 were on how the current set of Oracle products supported the development and deployment of mobile and internet applications.
Ora mag 2001 jan feb

Other articles included:

  • Oracle and HP form an alliance to develop and delivery CRM solutions aimed at helping companies keep up with customers in a world where rapid change is common place.
  • In Tom Kyte's regular article he look at and gives examples of using function based (more...)

SLOB Patch 2014.09.01 – AWR Post-Processing Script ( Fix.

The SLOB kit comes with a little script that extracts interesting information from the awr.txt file produced at the end of a SLOB test. This is just a quick blog entry to point folks to a patched version of that works properly with all Oracle Database 11g releases as well as Oracle Database 12c.

Oracle changed AWR format in the and 12c releases so the old (more...)

Oracle and Data Warehouses

If you follow Blogs and Tweets from the Oracle community you won’t have missed hearing about the recent release of the first patch-set for Oracle 12c. With this release there are some significant pieces of new functionality that will be of interest to Data Warehouse DBAs and architects. The headline feature that most Oracle followers will know of is the new in-memory option. In my opinion this is a game-changer for how we design reporting (more...)

SQL> Select * From Alert_XML_Errors;

Once you are able to show the xml version of the alert log as data in database table Alert_XML, it would be nice to checkout the errors with accompanying timestamps from within view Alert_XML_Errors. Like this, with the help of 2 types and a pipelined function.

su - oracle
. oraenv [ orcl ]
[oracle@localhost ~]$ sqlplus harry/*****
SQL> desc alert_xml
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TEXT                                               VARCHAR2(400 CHAR)


Webinar Followup (Aug. 27): In Search of Plan Stability – Part 1

Thanks to everyone who attended my August 27th webinar entitled In Search of Plan Stability - Part 1. You can download the presentation materials from these links:

Presentation PDF

I'll update this post to provide a link to the recording shortly.

Come back in November for Part 2. Hope to see you then!


DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity in 12c

12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I'm going to use it to see how temporal validity queries are actually executed. All tests are done using Enterprise Edition.

First I'll create a table for (more...)

SQL> Select * From Alert_XML;

By mapping an external table to some text file, you can view the file contents as if it were
data in a database table. External tables are available since Oracle 9i Database, and from Oracle
11gR2 Database on, it is even possible to do some inline preprocessing on the file.

The following example of this feature picks up on standard output of shell script “”.
It isn’t referencing any file, but take notice of (more...)

My Speaking Schedule for Oracle Open World 2014

A quick post to let you know about the two presentations that I’ll be doing at Oracle Open World 2014.

Session ID:         UGF4482
Session Title:     “Getting Started with SQL Pattern Matching in Oracle Database 12c
Venue / Room:  Moscone South – 301
Date and Time:  9/28/14, 13:30 – 14:15

Session ID:          CON4493
Session Title:      “Regular Expressions (more...)

In-memory Consistency

A comment on one of my early blogs about the 12c in-memory database option asked how Oracle would deal with read-consistency. I came up with a couple of comments outlining the sort of thing I would look for in a solution, and this note is an outline on how I started to tackle the question – with a couple of the subsequent observations. The data is (nearly) the same as the data I generated for my previous article on the (more...)