Close

Missing peaks in ASH results

The ASH charts in OEM are great utilities for getting a quick summary of your system’s activity. However, these results can be misleading because of how the data is represented on screen. First, ASH is data is collected by sampling so it’s not a complete picture of everything that runs. Another thing to consider is…

Finding the name of an Oracle database

Oracle offers several methods for finding the name of a database. More significantly, 12c introduces new functionality which may change the expected value from some of the old methods due to the multi-tenant feature. Here are 11 methods for finding the name of a database. SELECT ‘ora_database_name’ method, ora_database_name VALUE FROM DUAL UNION ALL SELECT…

Splitting a clob into rows

I’ve used this tool for a wide variety of other parsing projects. One of the interesting tuning techniques I used was to pull the clob apart into 32K varchar2 chunks. It is possible to split the clob directly using the DBMS_LOB package or through the overloaded SQL functions; but clobs are expensive objects. Varchar2 variables…

Update to LDAP package

I’m in the process of updating my OID package from last year to version 2.0. The new version will include a significant change in API but will also include more functionality, in particular, support for TNS aliases. I’m also changing the way exceptions are raised as well as improving the efficiency of searches. Along the…

Managing TNS lookups in OpenLDAP via PL/SQL

I have an Oracle Express Edition (XE) database running on almost every pc in my home. It’s not so numerous that I can’t manage them by manipulation of TNSNAMES.ORA; but I always thought it would be nice to manage them centrally via LDAP service name lookup. Unfortunately Oracle Internet Directory (OID) licensing is included with…

Analogies to help explain views

A not-uncommon request I hear is to explain when the contents of a view are updated. Sometimes this will be to explain the difference between a “view” and a “materialized view.” I like analogies as a tool to help describe what I’m trying to say. I wear glasses. Querying through a view is like looking…

Oracle 12c finally released!

I’m disappointed with the container licensing. Seems odd to publish with a “consume more resources than needed” option turned on. Of course, people will want to buy the containers to take full advantage of their hardware. So I guess I can understand the sales model but from a branding perspective publishing with the brakes locked…

KScope12 Day 1

First day was unintentionally APEX heavy for me. Not that that’s a bad thing, just sort of surprising to me in hindsight. Saw some really neat apps and ideas with HTML5 and dynamic actions. Also saw some new tools I’ll be checking out to make my own development more productive like aptana studio and apexlib.…

Nifty math trick with hierarchical query

Using the algorithm discovered by David Bailey, Peter Borwein, and Simon Plouffe, you can easily generate values of pi with a simple recursive query… SQL> SELECT d, to_char(bbp, rpad(‘0.’,55, ‘9’)) bbp,   2    TO_CHAR(   3       SUM(bbp) OVER (ORDER BY d),   4       ‘9.’ || RPAD(‘9’, d – 1, ‘9’)   5    ) pi  …

11gR2 on OEL6? yes and no

According to recent press release http://www.oracle.com/us/corporate/press/1563775 Yes- you can install 11gR2 on Oracle’s latest release of Linux. However, I was not successful in my attempts. Upon further research it seems the certification of db and os is only for 11.2.0.3 which is only available through download from Oracle support. That’s fine for work where we…

Using java to extend dbms_crypto

I don’t normally write a lot of java stored procedures.  They simply aren’t necessary most of the time; but today somebody asked me a question that just seemed a perfect fit.  They wanted to know how to generate a SHA-256 hash for a given string.  For most hashing tasks I’d recommend Oracle’s built-in package DBMS_CRYPTO;…

Oracle Database Gateway for ODBC

I’ve known about Heterogeneous Services and Gateways for a while but only recently got around to playing with them.  Like others, I chose the ODBC Gateway because the database license includes the ODBC usage, unlike the rest of the Gateways that are each licensed separately.  First, create an ODBC DSN for whatever database and platform…

Let’s get started!

Welcome to my blog about Oracle database development. I’m also interested in math and frequently write pl/sql and sql snippets to solve various math problems. For example, here’s an article I wrote about solving a combinatorics problem found in dart games like 301,501,701, etc. Fun with Oracle SQL – Solving Checkouts in a Game of…