Top 60 Oracle Blogs

Recent comments

February 2016

Oracle DBA, IT Manager, or Something Else

February 14, 2016 In September 2014 I posted an article on this blog where I mentioned that I am the IT Manager and an Oracle Database administrator at a company that specializes in welding and machining very large metal parts – in the article I attempted a bit of humor while trying to explain a few items.  […]

Oracle 12c – PL/SQL “White List” via ACCESSIBLE BY

Oracle 12c now provides a “white list” capability for procedure/function execution by means of a new PL/SQL ACCESSIBLE BY clause used to control access. ACCESSIBLE by specifies which objects are able to reference a PL/SQL object directly (sometimes called a “white list”). The ACCESSIBLE BY clause may be added to packages, procedures, functions, and types allowing an additional layer of security. Even if by some accident an intruder obtains permissions to use stored PL/SQL; the ACCESSIBLE BY rules will not allow any access that violates the specified rules.

Example Coding

Here’s an Example of ACCESSIBLE BY in a simple function:

UltraEdit v16 for Mac

Followers of the blog will know I’ve been a long time user of the editor UltraEdit.


I got introduced to UltraEdit about 15 years ago. At the time everything else around sucked. I paid my money and was hooked pretty much instantly. At that point it was a Windows-only product. When I later ditched Windows in favour of Linux at home, I went through a succession of crappy editors and was never really happy. I eventually started running UltraEdit on Linux using Wine and it “mostly” worked.

Friday Philosophy – Content, Copying, Copyright &Theft

There have been a couple of things this week that have made me think about the stuff that some of us write and what other people do with it.

Public / private cursors

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.

However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component.

Cursors can also be defined in the same way as the following example shows:

   CURSOR c_recent_hires RETURN emp%ROWTYPE;
END emp_stuff;

   CURSOR c_recent_hires RETURN emp%ROWTYPE IS
      SELECT * FROM emp 
      WHERE hiredate > sysdate-30;
END emp_stuff;

New Round of Let’s Talk Database Events

I’ll be presenting a new round of “Let’s Talk Database” events around Australia and NZ next month. These are free events but have often “sold out” in the past so booking early is recommended to avoid disappointment. All events run between 9:00am – 12:30pm and are followed by a networking lunch. Currently, the confirmed events are: Sydney – Tuesday, […]

Implementing Index Compression (and other Physical Storage Options) via Application Designer

There are some performance improvements that require physical storage options to be set on tables or indexes.
One particular technique that I will take as an example for this article is index compression.  A good example in PeopleSoft is the tree node table, PSTREENODE.  It drives many security and hierarchical queries.  It is not updated very frequently, only as new trees are brought on.  Many of the indexes are good candidates for compression.
This compression works by storing repeated column values only one per index leaf block.  Each distinct set of values in the columns up to the prefix length are stored in a symbol table.  The choice of prefix length can significantly effect the compression.  Oracle can calculate the optimal prefix length using

Quiz Night

I was setting up a few tests on a copy of recently when I made a mistake creating the table – I forgot to put in a couple of CAST() calls in the select list, so I just patched things up with a couple of “modify column” commands. Since I was planning to smash the table in all sorts of ways and it had taken me several minutes to create the data set (10 million rows) I decided to create a clean copy of the data so that I could just drop the original table and copy back the clean version – and after I’d done this I noticed something a little odd.

Here’s the code (cut down to just 10,000 rows), with a little output:

RMOUG 2016 – Controlling Execution Plans Workshop

Thanks to everyone that attended my session. Here’s a quick link to a zip file with the scripts I used in my Controlling Execution Plans Workshop presentation.

Controlling Execution Plans Workshop Zip File

Video: Amazon Web Services (AWS) : Relational Database Services (RDS) for SQL Server

Here’s another video on my YouTube channel. This one is a quick run through of RDS for SQL Server, a DBaaS offering from Amazon Web Services.

The video was based on this article.

The cameo for this video is Garth Harbach, a former colleague of mine. :)