IPython/Jupyter SQL Magic Functions for PySpark

Topic: this post is about a simple implementation with examples of IPython custom magic functions for running SQL in Apache Spark using PyS

Oracle Midlands : Event #18

Don’t forget Oracle Midlands Event #18 next week!


If you live near, please show your support for the event and come along. It’s free thanks to the sponsorship by RedStackTech.



Oracle Midlands : Event #18 was first posted on November 17, 2016 at 4:06 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the (more...)

“That STILL Ain’t Right”

"If you want sense, you'll have to make it yourself."
- Norton Juster, The Phantom Tollbooth

In a recent post to an Oracle forum a query was presented and a tuning request was made. It appears that the query was taking 20 hours to complete. Through further interrogation and responses it was discovered that the dates were being stored in a VARCHAR2 column and implicit date conversion was being used. To show how much (more...)

Introduction to Intel Pin

This blogpost is an introduction to Intel’s Pin dynamic instrumentation framework. Pin and the pintools were brought to my attention by Mahmoud Hatem in his blogpost Tracing Memory access of an oracle process: Intel PinTools. The Pin framework provides an API that abstracts instruction-set specifics (on the CPU layer). Because this is a dynamic binary instrumentation tool, it requires no recompiling of source code. This means we can use it with programs like the Oracle (more...)

User ASADMIN does not have the privilege of deploying/undeploying composites

I am trying to deploy the SOAP service in ebs 12.2.6 with integrated SOA gateway. I can able to deploy the wsdl file but I can’t able to view wsdl files. The below error appeared when I try to open view wsdl file

“Failed to Load a composite for composite identified by default”

So I realized  the error seems to be part of SOA server. I looked the SOA server log from EM (more...)

Playing with SUBSTR and INSTR

hawk:(SYS@hawk):PRIMARY> r
  1  SELECT handle,
  2  SUBSTR(handle, INSTR(handle,'/',-1)+1)                extract_last_field,
  3  SUBSTR(handle, 1, (INSTR(handle,'/',-1,1)-1))         remove_last_field,
  4  SUBSTR(handle, 1, (INSTR(handle,'/',1,2))-1)          extract_first_field,
  5  SUBSTR(handle, INSTR(handle,'/',1,2), length(handle)) remove_first_field
  6* FROM v$backup_piece_details



hawk:(SYS@hawk):PRIMARY> r
  1  SELECT name,
  2  SUBSTR(name, INSTR(name,'/',-1)+1)              extract_last_field,
  3  SUBSTR(name, 1, (INSTR(name,'/',-1,1)-1))       remove_last_field,
  4  SUBSTR(name, 1, (INSTR(name,'/',1,2))-1)        extract_first_field,
  5  SUBSTR(name, INSTR(name,'/',1,2),  (more...)

Improving performance of top query

I’m on call this week. Here are the steps that I took to speed up a query today.

First I got an AWR report and found the top query. Also, someone from support told me to look at November 11 before the latest release and I found a similar top query.

I got a plan for both the new and old top queries – they both use the same plan which makes me think that the (more...)

Building Classrooms in the Cloud

Jumpbox Lab Server

Let’s face it: education without interaction is about as effective as shouting origami instructions at a lumberjack who is cutting down trees. Sure, your informative lessons will come in handy when the product of their work finally becomes paper, but it will be long forgotten and ultimately worthless by then. The only way a student is going to learn is if they can put (more...)

Executing Queries from the Application

Databases are designed to hold data and retrieve it, so they are optimized to run queries. Over the years I’ve seen quite a few cases where the developers did things on the application side that could be easily done on the database side, and almost every time moving it to the database improved the performance. … Continue reading Executing Queries from the Application

Creating a RAC cluster using Ansible (part 1)

In my previous blog post i explained how Vagrant can be used to create a 2-node VM environment for deploying a RAC cluster on it. In this post i want to dive into how to actually do the RAC install using ansible so you can easily create a RAC test environment on eg. your laptop.

I have created a git repo with the files that i am using for this blog post, this git repo (more...)

Creating Oracle Big Data Lite VM on Proxmox

The Oracle Big Data Lite VM available on Oracle technet, provides a pre built environment for learning about a number of key Oracle products, including Oracle 12c database, Big Data Discovery and Data integrator as well as Cloudera Distribution – Apache Hadoop (CDH 5.8.0).

The download ultimately delivers an OVA “appliance” file for use with Oracle VirtualBox, but there isn’t anything to stop you running this as a VM on proxmox 4, (more...)

Performing a large correlated update

We had a question on AskTom recently asking for the best way to update a new column on a very large table.  Four in-place update options are at our disposal:

  • Serial update SQL
  • Parallel DML update SQL
  • PL/SQL batching (as long as sensible restart-after-error logic is readily available)

all of which will achieve the required outcome but they have a significant redo and undo cost associated with them.  Also, depending on the (more...)

Cloud Control 13c Release 2 (13cR2) : It’s Alive!

o-enterprisemgr-13c-clr-2769481I delayed our upgrade from Cloud Control 13cR1 to 13cR2 until I finished all my travelling. It’s not really fair to make a major change then swan off round the world. 🙂

Yesterday was the day. I got in and started the upgrade, following the process I documented here and it worked. 🙂


I guess I shouldn’t be surprised. I had tested it all at home anyway.

We now have Cloud Control 13cR2 monitoring all out Dev and (more...)

Redo a blogpost

This blogpost is about the Oracle redo log structures and redo efficiency in modern Oracle databases. Actually, a lot of subtle things changed surrounding redo (starting from Oracle 10 actually) which have gone fairly unnoticed. One thing the changes have gone unnoticed for is the Oracle documentation, the description of redo in it is an accurate description for Oracle 9, not how it is working in Oracle 10 or today in Oracle 12.1.0. (more...)

Oracle ACE Program : Some more opinions

I’ve written about the Oracle ACE Program a number of times over the years, including the following.

I’ve also recorded a video on the subject.

Some things have come up in conversation a few times recently that lead me to believe there are some misunderstandings about what the label “ACE” means and (more...)

OGG Activity Logging Tracing (Doc ID 1204284.1)

I just came across MOS Doc for tracing OGG processes.

Just thought I would compare the old versus new.

You can find comparison and my preference here

Optimizer Compatibility, Short Pants and Hot Sauce

OK, so I’m all over the map, (technology wise) right now.  One day I’m working with data masking on Oracle, the next it’s SQL Server or MySQL, and the next its DB2.  After almost six months of this, the chaos of feeling like a fast food drive thru with 20 lanes open at all times is starting to make sense and my brain is starting to find efficient (more...)

Bulgarian Oracle User Group (BGOUG) 2016 : It’s a Wrap!

bgougSo that marks the end of my 6th BGOUG event. I’m already planning to return next year. 🙂

As part of this event I put out the following blog posts.


Bulgarian Oracle User Group (BGOUG) 2016 : Pravets to Birmingham

bgougA group of us were being picked up by a minibus at 09:50 for the trip back to the airport. Timo Raitalaakso and Gianni Ceresa were on the same flight as me for the first leg. We said our goodbyes to everyone in the hotel lobby, then it was off to Sofia airport.

The airport was very quiet when we arrived. We checked in and dropped off our bags, then walked straight through security. It (more...)

Bulgarian Oracle User Group (BGOUG) 2016 : Day 2

bgougI planned to go to breakfast for a few minutes and then hit the gym. I got caught up in conversation during breakfast and several hours later the gym was not an option.

I headed off to the Christian Berg‘s session called “Back2Basics – 50 Shades of #Fail: OBI Worst Practices in Real Life”. I know next to nothing about OBIEE so this was me checking out something new to me. it quickly became (more...)