January 2016

Video: SQL Server Databases on Microsoft Azure

I mentioned in a previous post, the whole look and feel of Microsoft Azure has been rejigged. As a result, I had to do a run through of the SQL Server DBaaS stuff to update the screen shots in and old article on the subject.

Azure : SQL Server Databases on Azure

Since I was doing that, I figured I might as well do a video for my YouTube channel.

Cheers

Tim…

VirtualBox 5.0.14

VirtualBox 5.0.14 has been born.

Downloads and changelog are in the usual places.

I’ve not done the installation on Linux yet, but it installed and seems to work fine on Windows 7 and Mac OS X (El Crapitan).

Cheers

Tim…

Converting LONG to CLOB

Some folks still are stuck with LONG columns, and are keen to move to LOB.  Since version 9, we’ve had a nice facility to do that – just with a simple alter command.

You can now simply issue “alter table (longcol CLOB)” to perform the conversion. This is a neat tool, but be aware of the space implications before attempting a conversion.

Here is a simple example to demonstrate:

PL/SQL arrays–the index datatype

You get some interesting (but perhaps not unexpected) results when playing with the speed of array functions in PL/SQL.  This is a series of tests comparing “BY PLS_INTEGER” arrays with “BY VARCHAR2” arrays.  In all the cases, their speed is pretty much blindingly fast, but the comparison between the two seems to be dependent on the type of array function being performed. 

Anyway … on to the tests

Certification exams

This is an update of a blog item I made nearly 15 years ago … but I think it still holds true

Is doing the OCP exams worthwhile ?
Yes, but not for the reasons you may be thinking. The OCP exams are a relatively cheap way of identifying possible weaknesses in your knowledge base on Oracle. For example, when I did the OCP, all of the database sites I had worked on did not use MTS and thus the exams revealed an area that could be “swotted up” on.

What does an OCP mean ?

Jenkins Plugin for Delphix

In my last blog I talked about trying out Jenkins. In this blog post I want to talk about the new Jenkins plugin for Delphix.

Delphix plugin

Delphix plugin is easy to add. Just navigate to “Manage Jenkins” in the top left

Screen Shot 2016-01-15 at 11.34.17 AM

Then click on “Manage Plugins”

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  /

ENAME
------------------------------
*KING
**BLAKE
***ALLEN      <==| 
***JAMES      <==|
***MARTIN     <==| ordered within the "BLAKE" branch
***TURNER     <==|
***WARD       <==|
**CLARK
***MILLER
**JONES
***FORD
****SMITH
***SCOTT
****ADAMS

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  /

ENAME
-----------------------

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;

ALTER TABLE SPLIT PARTITION p0 INTO 
  (PARTITION p01 VALUES LESS THAN (25),
   PARTITION p02 VALUES LESS THAN (50), 
   PARTITION p03 VALUES LESS THAN (75),
   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.

Cheers

Tim…