A RAC DBA needs to keep at least two sets of environment variables handy: one for the Grid/ASM home, and one for the database home. Here's how I set up my .bashrc to make that easy.
Author: Natalka
Tip: skip the TO_DATE with ANSI DATE literals
Tired of all the typing required to specify a date in a query using TO_DATE?
SELECT SUM(sale_amt) AS december12_sales WHERE sale_date BETWEEN to_date('2012-12-01','YYYY-MM-DD') AND to_date('2012-01-01','YYYY-MM-DD');
Whew! Shake out your fingers, that was a pain. Fortunately, there’s an easier way. Read more
Quorum CEO
Dilbert is doing a co-CEO arc which reminded me immediately and forcefully of voting disks.
The pointy-headed CEO has hired a co-CEO, only to find himself deadlocked with the new hire. Of course, this is exactly why Oracle Clusterware needs an odd number of voting disks!
Today’s images are from dilbert.com, copyright 2014 Scott Adams, used without permission, for comment and instruction.
Recursive WITH, part III: IS_LEAF
The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifes leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH.
Read more
Recursive WITH, part II: Hierarchical queries
In my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH.
Read more
Recursion with recursive WITH
I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that I'm not making much use of the developments in Oracle SQL since 8i. In this blog post, I'll look at the recursive subquery factoring introduced in Oracle 11g.
CONNECT BY, Part II
In my last post, I used CONNECT BY to query a table holding the bones of the skeleton in the “Dem Dry Bones” song: since the skeleton in the song has a head with no parent bone (making it a root node), and each bone has only one parent bone, it’s hierarchical data.
In this post, I’m going to add an arm and some ribs to the skeleton to make the data more interesting to query.
Read more
CONNECT BY, Part I
In my last post, I briefly covered what hierarchical data is and how it’s represented in an RDBMS using the adjacency list model. In this post, we’ll look at querying hierarchical data with Oracle SQL’s CONNECT BY construct, using a really fun example.
Hierarchical Data
This is the first in a series of posts about Oracle SQL and hierarchical data.
100+ Support Documents …
I realized recently that since joining Oracle’s RAC Support team in 2007, I’ve written or co-authored over 100 technical documents for My Oracle Support (MOS, formerly known as Metalink). Read more