Top 60 Oracle Blogs

Recent comments

January 2016

Getting Your Transaction SCN – USERENV(COMMITSCN)

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it so I asked Jonathan and he pointed me straight to this post he did about it a while back. What a nice chap. However, the post is from 1999 (last CENTURY!) so I thought I should just check it out first…

A little known ORDER BY extension

Within a CONNECT BY statement, you can order siblings, that is, under a particular branch of the hierarchy, the child entries can be ordered, but you still preserve the hierarchy.

SQL> select lpad('*', level, '*' ) || ename ename
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr
  5  order SIBLINGS by ename
  6  /

***ALLEN      <==| 
***JAMES      <==|
***MARTIN     <==| ordered within the "BLAKE" branch
***TURNER     <==|
***WARD       <==|

14 rows selected.

SQL> select lpad('*', level, '*' ) || ename ename
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr
  5  order SIBLINGS by ename DESC
  6  /


Multiple partitions

In 12c, one of the nice changes to come along is that partition maintenance operations can now be done on multiple partitions, for example

ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0;

   PARTITION p04);

and so on. However, one of the things that you still cannot do is the same thing with SELECT

Video: Oracle Linux Virtual Machine (VM) on Micorosft Azure

The interface for Microsoft Azure has been re-jigged since I last did screen shots, so I did a run through of creating an Oracle Linux VM and recorded it for my channel.

I also updated the associated article.



Drop Column

I published a note on AllthingsOracle a few days ago discussing the options for dropping a column from an existing table. In a little teaser to a future article I pointed out that dropping columns DOESN’T reclaim space; or rather, probably doesn’t, and even if it did you probably won’t like the way it does it.

I will  be writing about “massive deletes” for AllthingsOracle in the near future, but I thought I’d expand on the comment about not reclaiming space straight away. The key point is this – when you drop a column you are probably dropping a small fraction of each row. (Obviously there are some extreme variants on the idea – for example, you might have decided to move a large varchar2() to a separate table with shared primary key).

DML Operations On Partitioned Tables Can Restart On Invalidation

It's probably not that well known that Oracle can actually rollback / re-start the execution of a DML statement should the cursor become invalidated. By rollback / re-start I mean that Oracle actually performs a statement level rollback (so any modification already performed by that statement until that point gets rolled back), performs another optimization phase of the statement on re-start (due to the invalidation) and begins the execution of the statement from scratch.


Oracle 12c – PL/SQL in SELECT

It is often useful to execute PL/SQL functions as part of an SQL SELECT statement (or other DML). When SQL calls PL/SQL a “context switch” occurs as execution moves between SQL execution and PL/SQL execution. In Oracle Database 12c Oracle has given us two tools to help reduce the impact of this context switching. The first is the ability to add a local function definition to the SELECT via the WITH clause, the second is to flag a function using PRAGMA UDF so that the compiler and optimizer will know it is to be used from SQL. Both options can measurably improve performance if used correctly.

To achieve maximum performance gains the PL/SQL function involved should not call other PL/SQL procedures and/or PL/SQL functions nor should PRAGMA UDF functions be called from “normal” PL/SQL (not inside SQL).

Trying out Jenkins

Jenkins seems to be the top code build, integration and QA automation tool to support continuous delivery.

Jenkins allows me to kick of jobs on a schedule or ofter certain events and Jenkins will keep track of the status of the jobs and the results.

To get Jenkins installed,  I read   and then read to try my first Jenkins example.

I installed Jenkins  on one of the VMs from the “landshark” demo environment for Delphix Express. Landshark consists of two Linux Centos VMs that provide source and target examples for Delphix Express. I used the “target” VM but could have just as easily used the “source” machine.

See Sharp Objects, Run with Sharp Objects, Crash with Sharp Objects – Obscure and Incorrect Error Messages in Commercial Products

January 15, 2016 Since October 2015 I have been fighting with an application programming interface (API) that works with an enterprise resource planning (ERP) system.  The API, with uses Microsoft .Net 4.0 or higher, is intended to simplify the process of automating the creation of various types of documents within the ERP system.  For example, the API […]

Adding Targets in Enterprise Manager 13c

The only thing that is constant is change.