July 2016

Delphix Express- Adding XE DSource

For those of you that downloaded and are starting to work with Delphix Express, (because you’re the cool kids… :)) You may have noticed that there is an Express Edition Oracle 11g database out there you could use as a Dsource, (source database for Delphix to clon

Video Tutorial: XPLAN_ASH Active Session History - Part 8

#333333; font-family: "verdana" , "arial" , sans-serif; font-size: 13px; line-height: 16.9px;">The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

#333333; font-family: verdana, arial, sans-serif; font-size: 13px; line-height: 16.9px;" />#333333; font-family: "verdana" , "arial" , sans-serif; font-size: 13px; line-height: 16.9px;">More parts to follow.
#333333; font-family: "verdana" , "arial" , sans-serif; font-size: 13px; line-height: 16.9px;">

Complete Refresh of Materialized Views: Atomic, Non-Atomic and Out-of-Place

Recently, and more than once, I have found myself explaining exactly how an Oracle database performs a complete refresh of a materialized view, and what are the implications.  So I thought I would set it out in a blog post.
I am only looking at the case where it is not possible to incrementally refresh using on a materialized view log because the materialized view joins tables together, groups data, or breaches one of the other restrictions (see General Restrictions on Fast Refresh).

Little enhancements are always nice

Before LATERAL and CROSS APPLY were added (exposed to us) in 12c, a common technique to do correlated joins was using the TABLE/MULTISET technique.

Upcoming Events and The Caffeine

So some unknowing fool gave me full access to a fridge of Starbucks caffeinated beverages, which should be against the law in this fine country.  Needless to say, my ability to type approximately 180wpm has offered me the opportunity to catch up and blog about what it’s like almost a month in employed with Delphix.

Tales from the field: potential reasons for PDB plug-in violations part 2

In the first part of this little series I explained how a missing (default) tablespace in a PDB can have an impact when opening it. In this instalment I’ll add another common cause for PDBs not opening in read-write mode: different patch levels between the PDB to be plugged in and the CDB.

Scenario

Assume for a moment that you upgraded Oracle binaries from 12.1.0.2.5 to 12.1.0.2.160419. In other words, you applied the most current PSU + OJVM patch at the time of writing to your database. Once complete, you would see output similar to this for a single instance Oracle 12c installation without Oracle Restart:

[oracle@server2 ~]$ opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.

I am using database CDB1 in this blog post to indicate the patched CDB:

Union All MV

In an article I wrote last week about Bloom filters disappearing as you changed a SELECT to a (conventional) INSERT/SELECT I suggested using the subquery_pruning() hint to make the optimizer fall back to an older strategy of partition pruning. My example showed this working with a range partitioned table but one of the readers reported a problem when trying to apply the strategy to a composite range/hash partitioned table and followed this up with an execution plan of a select statement with a Bloom filter where the subquery_pruning() hint didn’t introduced subquery pruning when the select was used for an insert.

OpenWorld 2016 is not far away

OpenWorld is just around the corner, and the Ask Tom team will be involved in a number of panels where you can chat to us, ask questions, debate topics and basically have a relaxed 45mins during all the frenzied activity that is OpenWorld.  So if you’ve got any questions you would like answered “face to face”, rather than via Ask Tom, either drop them as a comment here, or feel free to post them on the AskTom site and just annotate it in some way that lets us know you’d like to talk about it on the panels during OpenWorld.

See in in San Francisco !

Tips and Tricks- Delphix Express

Delphix Express offers a virtual environment to work with all the cool features like data virtualization and data masking on just a workstation or even a laptop.  The product has an immense offering, so no matter how hard Kyle, Adam and the other folks worked on this labor of love, there’s bound to be some manual configurations that are required to ensure you get the most from the product.

Comparing Columns Containing NULL Values

Prompted by a (not really that) recent discussion on the OTN forum I've decided to publish this note.Sometimes you have the task of comparing column values and handling the NULL value cases correctly makes this rather cumbersome for columns that are allowed to be NULL.The "official" SQL way of comparing two column values and to find out whether they are equal or not - under the assumption that having NULL in both columns should be treated as equal (a point that can be argued) would read in SQL something like the following for the "unequal" case:


column1 != column2 or (column1 is null and column2 is not null) or (column1 is not null and column2 is null)

and