A development database with a small FRA was filling it up with archived logs, hanging the database on log file switch (needs archiving).
Got a message from a user of a not-much-used dev database that their SQL Developer connection was timing out. Here’s a walkthrough of how I troubleshot (troubleshooted?) the issue to find that the root cause was a kernel issue.
Here’s a quick start guide to taking and interpreting 10046 trace, with bind variables.
1. Generate the trace
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.
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
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.
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.
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.
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.
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.