Skip navigation.

Feed aggregator

LittleArduinoProjects#185 Building the Böhm Stirling-Technik HB13 Small Bonsai

Paul Gallagher - Sat, 2016-02-13 08:57
What has this to do with electronics? Well, nothing (yet), but there's ample scope later;-)

A Stirling engine is a closed-cycle regenerative heat engine with a permanently gaseous working fluid. They are named after Robert Stirling who invented the first practical example in 1816.

Böhm is a small(?) specialist manufacturer from Germany that's taken up a side-line in producing Stirling engine kits. When I first discovered them, I couldn't resist. So here's my build of the Small Bonsai (HB-13).

As always, all my notes and schematics are in the Little Arduino Projects repo on GitHub.

LittleArduinoProjects#184 Adjustable Pulse Generator

Paul Gallagher - Sat, 2016-02-13 08:43
Yet another variation on the basic 555 timer astable oscillator to allow a wide range of frequency and duty cycle adjustments. It's similar the circuit used in kits like this.

As you can see from the schematic, it's the combination of pot and capacitor selector that produce the wide range of oscillator control:

But the challenge with the classic 555 astable circuit is trying to hold frequency or duty cycle constant while adjusting the other. This circuit doesn't solve that problem, but for something different, I plotted the functions with WolframAlpha. If you correlate the two graphs below, you can see the severe penalty you pay in terms of duty cycle when attempting to push for maximum frequency. It definitely is a case of trying to find the best compromise for your application.

As always, all notes, schematics and code are in the Little Arduino Projects repo on GitHub, including live links to WolframAlpha to reproduce these plots.

Frequency for all values of R1, R2 (C=1µF)

Duty Cycle for all values of R1, R2 (C=1µF)

UltraEdit v16 for Mac

Tim Hall - Sat, 2016-02-13 07:59

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.

A few years ago I switched from Linux desktops to Mac and went through another succession of crappy editors that everyone told me were amazing. One day I woke up to find UltraEdit had released a Mac and Linux version of their editor. It was lacking features compared to the Windows version, but it worked. I immediately switched and have been there ever since.

I have a multi-platform unlimited upgrades license, which cost me something like $200 a few years ago. I use UltraEdit for Mac on my desktop at home. I use the Windows version when I’m at work. Very occasionally I fire up the Linux version on one of my servers, but that tends not to happen much these days as I just connect to that stuff from my Mac.

Today I’ve upgraded to UltraEdit for Mac v16. It still doesn’t have 100% of the features of the Windows version, but it’s getting close. Happy days!

I know there are a lot of good editors out there (Sublime Text, NotePad++, Atom etc.), but I don’t get on too well with any of them. I’m sure a lot of if comes down to familiarity, but I find myself saying, “If only they could do …” all the time. Aaaahhhh, to be old and set in your ways. :) Anyway, if you like any of those, all power to you. If you are struggling against them, you might want to give UltraEdit a try!



PS. This is not a sponsored ad. I’m just a fan! :)

UltraEdit v16 for Mac was first posted on February 13, 2016 at 2:59 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 site is guilty of copyright infringement.

Integrating Google Maps, IFTTT, and OpenWeatherMap APIs into the Oracle UX Rapid Development Kit

Usable Apps - Sat, 2016-02-13 04:31

The Oracle Applications Cloud User Experience Rapid Development Kit (RDK) enables Oracle partners and developers to learn faster, design simpler, and build better. The RDK contains a set of free resources for the design and build phases and includes sample Oracle ADF applications that illustrate how to develop sleek, modern SaaS integrations and custom PaaS applications that have the same look and feel as the Oracle Applications Cloud.

You can find more resources for the RDK at, including the code download link. If you prefer to hear about the RDK, tune in to our Oracle HCM Talk Radio podcast to hear how you can tap into the Oracle Applications Cloud User Experience.

In this blog, I explain how to enhance the Oracle ADF application samples in the RDK with integration to third-party services, such as Google Maps, IFTTT (If This Then That), and OpenWeatherMap, using simple HTTP and JavaScript.

What You Need
  • Oracle JDeveloper ( or 12c) available as a free download from OTN.
  • Oracle UX Rapid Development Kit available as a free download from OTN and GitHub.
Where To Start

Download the RDK zip file, and then unzip it to your Oracle JDeveloper working folder. You should see a folder structure resembling the following.

RDK Code Folder Structure

AppsCloudUIKit folder structure

Open the application (AppsCloudUIKit.jws) in Oracle JDeveloper to show the projects that make up the RDK.

RDK Project Structure in JDeveloper

Oracle JDeveloper view of projects in the RDK

At this point, you can run the page Welcome.jspx in the DemoMaster project and interact with the out-of-the-box runnable sample application.

Integrating Google Maps

Google Maps come integrated in the Contacts Map page fragment. You can find this page under the DemoCRM project, which corresponds to the Oracle Sales Cloud, with the code organized under logical business objects - contacts, opportunities, and so on.

Location of Contacts Map page in JDeveloper

DemoCRM project: Page fragment ContactsMap.jsff 

When you run the RDK, dismiss the one-time welcome banner, and then click the Map my Contacts icon to navigate to the Contacts Map page. The finished page has a searchable list of contacts on the left with the map rendered on the right. When you click a contact record, the map renders a pin to mark the geographical location of the contact. Clicking the same contact again removes the pin. You can click more than one contact to see location pins together.

Screen shot of running Contacts Map page

Map My Contacts page

Let us see how to build this.

The Google Maps API is a JavaScript Library. We can add JavaScript to an Oracle ADF Faces page by either adding the JavaScript code to the page source or by adding a reference to an external JavaScript library file. In this case, we will use both techniques. We first add a reference to the Google Maps JavaScript Library using the <af:resource> tag.

<af:resource type=”javascript” source=””/>

We then add JavaScript code blocks directly into the page source using the same <af:resource> tag. The following code block initializes the map object on the page when the page loads.

   if (window.addEventListener) {
      /* Modern browsers */
      window.addEventListener("load", onLoad, false)
   } else if (window.attachEvent) {
      /* IE */
      window.detachEvent("onload", onLoad)
      window.attachEvent("onload", onLoad)
   } else {
      window.onload = onLoad

  function onLoad() {

The doMap function anchors the map container object to a seeded element in the page with the ID mapdiv. It sets a default value for latitude, longitude, and zoom level to the map object in the container for initial rendering. This code block also prepares an array to hold multiple marker references in case of multiple contacts selection.

<af:resource type="javascript">
   var map;
   var geocoder = new google.maps.Geocoder();
   markers = [];

  function doMap() {
      var mapelem = document.getElementById('mapdiv');
      var newLatLng = new google.maps.LatLng(37.75, - 122.42);
      var myOptions = {
         center : newLatLng, zoom : 8
      map = new google.maps.Map(mapelem, myOptions);

When the user clicks a contact, a client listener triggers the geocodeAndMark() function in the following code block. This function obtains the contact address information, generates a unique marker ID, and then calls the geocodeAddress() function.

<af:resource type="javascript">
   function geocodeAndMark(evt) {
      var comp = evt.getSource();
      var combo = comp.getShortDesc();
      var comboArr = combo.split( "+" );
     var id = comboArr[0];
      var addr = comboArr[1];
      geocodeAddress(id, addr, geocoder, map);

The geocodeAddress function uses the Google Maps geocoder to geocode the address, save the marker in the marker array, and add a pin to the map.

<af:resource type="javascript">
   function geocodeAddress(id, address, geocoder, resultsMap) {
      if (markers[id] == undefined) {
         markers[id] = new google.maps.Marker(null, null);
      if (markers[id].getMap() == null) {
            {‘address' : address},
            function (results, status) {
               if (status === google.maps.GeocoderStatus.OK) {
                  var marker = new google.maps.Marker({
                     map : resultsMap, position : results[0].geometry.location
                  markers[id] = marker;
               } else {
                  alert('Unable to find address. Geocoding returned ' + status);
      } else {

Because this processing is done on the client side, the page does not require full or partial refresh to redraw the map to toggle markers. That is all there is to it!

Integrating IFTTT (If This Then That)

IFTTT is a free web-based service that allows you to conditionally trigger events by writing statements in the format—If Condition Then Action—referred to as Recipes. A large number of Channels are available to specify the Condition and Action part of the Recipe. It is a fun, yet powerful, integration or automation service for all things internet-connected. I will assume that you have an account and that you know how to build IFTTT Recipes.

We will use the IFTTT Maker Channel to trigger a Recipe based on a user event in the RDK. The Maker Channel lets you connect a Web Request as the Condition or Action of a Recipe, and this will be the basis for connecting the RDK to IFTTT. You will need to set up your Recipe with the Maker Channel as trigger and give a specific name to the Trigger Event. You will need to add this in the RDK code, so make it meaningful.

Trigger event screenshot

Trigger event name example

You will also need to note the secret "key" string generated by IFTTT for your Maker Channel instance. (Because it is a secret key, I have hidden mine.)

IFTTT Channel screen shot

Maker Channel page

In the RDK, we will trigger the Recipe every time the Sales Opportunity amount is changed. This can be done quite easily by adding a few lines of code to the managed bean associated with the Sales Opportunity page. Open in the DemoCRM project under package oracle.apps.uikit.crm.opportunities.bean, and then add the event name you created and your secret key.

private static final String IFTTT_MAKER_EVENT = “RDK_Opportunity_Amount_Changed”;
private static final String IFTTT_SECRET_KEY = “################”;

We now invoke the following method when the user saves the changes they make on a page. The method continues processing only if the amount attribute has changed. It effectively sends a HTTP POST to IFTTT that triggers the Recipe associated with the Maker Channel instance identified by event name and secret key. At the time of writing, you can send up to three parameters to the event.

//Alert opportunity amount change
private void _alertOpportunityAmountChanged(String name, String oldAmount, String newAmount){
   if (!oldAmount.equals(newAmount)){
      try {
         CloseableHttpClient httpclient = HttpClients.createDefault();
         try {
            String HttpPostString = "" + IFTTT_MAKER_EVENT + "/with/key/" + IFTTT_SECRET_KEY;
            HttpPost httppost = new HttpPost(HttpPostString);
            String inputStr = "{\"value1\" : \"" + name + "\", \"value2\" : \"" + oldAmount + "\", \"value3\" : \"" + newAmount + "\" }";
            StringEntity input = new StringEntity(inputStr);
            input.setContentType( "application/json" );
            String responseBody = httpclient.execute(httppost, responseHandler);
         } catch (ClientProtocolException e) {
         } catch (IOException e) {
         } finally {
      } catch (IOException e) {
   }//amount has changed

// Create a custom response handler
ResponseHandler responseHandler = new ResponseHandler() {
   public String handleResponse(final HttpResponse response) throws ClientProtocolException, IOException {
      int status = response.getStatusLine().getStatusCode();
      if (status >= 200 && status < 300) {
         HttpEntity entity = response.getEntity();
         return entity != null ? EntityUtils.toString(entity) : null;
      } else {
         throw new ClientProtocolException("Unexpected response status: " + status);

As part of the Recipe action, you now have a world of exciting things you can do every time the amount attribute of a Sales Opportunity is updated in the RDK—blink your lights, send an email, write to a cloud document, tweet. Now go play!

Integrating OpenWeatherMap

OpenWeatherMap is an online service that provides free API access to weather data, including current weather, forecasts, and historical data. To use this service, you will need to register for a free account and obtain your unique APP-ID. I will assume that you have already done this. We will now use this service to display an icon for the current weather at the location for Sales Contacts.

Contacts List View screenshot

Contacts page

The technique involves a simple HTTP request to a specific URL with necessary parameters passed as part of the URL. While the services can be invoked for several cities at a time, I will keep it simple by fetching the current weather for one city. You can use the following URL patterns for your HTTP request.{city name}{city name},{ISO 3166 country code}

The following piece of code can be used to invoke the OpenWeatherMap service and extract the weather icon reference from the returned JSON payload.

String location = <city>;
String wicon = “”;
if (location != null && location.length() > 0){
   location = location.replaceAll(" ", "+" );
   try {
      CloseableHttpClient httpclient = HttpClients.createDefault();
      JSONParser parser = new JSONParser();
      try {
         HttpGet httpget = new HttpGet( "" + location + "&APPID=" + APPID + "&mode=json&units=metric" );
         //Create custom response handler
         ResponseHandler responseHandler = new ResponseHandler() {
            public String handleResponse(final HttpResponse response) throws ClientProtocolException, IOException {
               int status = response.getStatusLine().getStatusCode();
               if (status >= 200 && status < 300) {
                  HttpEntity entity = response.getEntity();
                  return entity != null ? EntityUtils.toString(entity) : null;
               } else {
                  throw new ClientProtocolException("Unexpected response status: " + status);
         String responseBody = httpclient.execute(httpget, responseHandler);
         JSONObject root = (JSONObject)parser.parse(responseBody);
         JSONArray weather = (JSONArray)root.get( "weather" );
         JSONObject wobj = (JSONObject)weather.get(0);
         wicon = "" + wobj.get( "icon" ) + ".png";
      } catch(ClientProtocolException e) {
         //Handle as appropriate
      } catch(IOException e) {
         //Handle as appropriate
      } catch(ParseException e) {
         //Handle as appropriate
      } finally {
  } catch(IOException e) {
      //Handle as appropriate
}//check location is available

You can display the weather icon in the Sales Contacts list view as shown in the Contacts page above, or anywhere else in the RDK that suits your requirement.


I have shown at a very elementary level, how to call a Web Service (or REST) API by constructing a URL string by using HTTP, receive a JSON payload in response, and parse this to extract the relevant parts. I have also shown how such processing can be easily incorporated into an ADF application by using Java or JavaScript. This gives you a flavor of how to rapidly enhance Oracle Applications Cloud with standalone PaaS applications or PaaS4SaaS integrations, enabling you to offer more UX value to your customers.

Do feel free to share your thoughts in comments, and stay tuned for more. Enjoy!


When JET Comes To Rescue - Live Data Charts in ADF

Andrejus Baranovski - Fri, 2016-02-12 09:57
I have described previously, how you can run JET in ADF UI - Improved JET Rendering in ADF. This was technical steps explanation. Today I will describe a case, when such rendering can be important and useful. The case is related to live data delivered through WebSocket and rendered in chart. WebSocket client must be implemented in JavaScript, but we can't access ADF DVT component values in JavaScript and update them, without making a call to the server and reloading binding value. Which would require client/server roundtrip.

In this post I'm using JET/WebSocket integration solution described previously - Oracle JET and WebSocket Integration for Live Data. Technical steps for JET rendering in ADF are the same as in the post above. Download sample application (it contains WebSocket and ADF (with JET code) projects) -

Pie chart is rendered by JET in ADF UI:

You should check demo video, where two sessions are running in parallel and each second new data is coming from WebSocket. See how synchronous JET rendered chart is changing in both sessions:

JET HTML code is included into ADF fragment. It renders DVT pie chart component, with value initialized from JavaScript variable:

Main ADF page must include a reference to WebSocket JavaScript file, this is where WebSocket client is implemented:

ADF fragment with JET code is included into main ADF page:

Whats the difference with ADF DVT chart? It gets data in different way, from bindings - this means data must be prepared on the server. ADF DVT chart value property must point to Collection Module from bindings. This is not bad, it is just different approach. It works perfect to display server side data, but makes it complex to render live data received in JavaScript:

WebSocket client contains onMessage method, where update is received and pushed to JET context:

JET handles update and changes chart data directly on the client:

To simulate continues updates, I have implemented ADF BC method to update DB data multiple times. DB change listener picks up these changes and makes a call through WebSocket to deliver update event. JET re-draws chart UI, based on the update:

Currently Oracle doesn't support JET in ADF. While JET can be rendered in ADF, you will not get official support through Oracle Support channels. 
You may run into issue of session timeout handling. ADF page sends a "ping" to the server whenever there is interaction on the page. JET does not do anything of that kind, so if you are sitting on an ADF page, and only interacting with the JET portion of that page, there is nothing telling the server that you are still active. This could result in ADF session timeout, while user is still working with JET component. In practice this would rarely happen, especially if ADF timeout is set to be long, but you should be aware of that.

Folien zum Webinar REST mit ORDS

Dietmar Aust - Fri, 2016-02-12 07:57
Oracle hat zusammen mit der DOAG das Webinar "Modern Application Development" organisiert, darin habe ich auch ein Webinar (auf deutsch) zum Thema "Oracle als REST-Service mit ORDS – So geht‘s" beigesteuert.

Die Folien dazu findet Ihr hier.

Viele Grüße,

Step-by-Step Guide to January 2016 PSU Patch Apply on 12c Grid and RDBMS Homes in Linux

Pythian Group - Fri, 2016-02-12 07:52

Following step by step action plan is for single instance database stored on ASM in on Linux (OEL 6 64 bit in this case.)

StepDescriptionETA1Update the OPATCH utility:


For Database home:


$ unzip -d /u01/app/oracle/product/12.1.0/db_1

$ /u01/app/oracle/product/12.1.0/db_1/OPatch/opatch version


For Grid home:


$ unzip -d /u01/app/oracle/

$ /u01/app/oracle/ version15 min2Create ocm.rsp file:


Note: Press Enter/Return key and don’t provide any input and say Yes.


$ export ORACLE_HOME=/u01/app/oracle/

$ $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /stage/ocm.rsp5 min3Validation of Oracle Inventory


Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched. Run the following command as respective Oracle home owner to check the consistency.


For database home:


$ /u01/app/oracle/product/12.1.0/db_1/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/12.1.0/db_1


For Grid home:


$ /u01/app/oracle/ lsinventory -detail -oh /u01/app/oracle/


If this command succeeds, it lists the Oracle components that are installed in the home. Save the output so you have the status prior to the patch apply.5 min4Stage the Patch:


$ mkdir /stage/PSUpatch

$ cp /stage/ /stage/PSUpatch


Check that the directory is empty.

$ cd /stage/PSUpatch

$ ls


Unzip the patch as grid home owner.


$ unzip p22191349_121020_<platform>.zip5 min5One-off Patch Conflict Detection and Resolution:


Run it with root user:


/u01/app/oracle/ apply /stage/PSUpatch/22191349 -analyze -ocmrf /stage/ocm.rsp


It will ask to rollback identical patches like this:


Analyzing patch(es) on “/u01/app/oracle/” …

Patch “/stage/PSUpatch/22191349/21436941” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.

Patch “/stage/PSUpatch/22191349/21948341” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.

Patch “/stage/PSUpatch/22191349/21948344” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.

Patch “/stage/PSUpatch/22191349/21948354” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.


So first rollback above 4 patches by going to their directory and issuing with grid owner from grid home:


opatch rollback -id 21948354 -local -oh /u01/app/oracle/ (Repeat for all 4 patches)


Note: In some cases, weirdly, I had to shutdown the has services with root user before patch rollback by using:


/u01/app/oracle/ stop has -f


After this again run:


/u01/app/oracle/ apply /stage/PSUpatch/22191349 -analyze -ocmrf /stage/ocm.rsp


If analyze command fail then use this with root user:


$ORA_GI_HOME/crs/install/ –postpatch


It will start the has services too.


Then again run the analyze command as given above:


It will show something like:


Analyzing patch(es) on “/u01/app/oracle/” …

Patch “/stage/PSUpatch/22191349/21436941” successfully analyzed on “/u01/app/oracle/” for apply.

Patch “/stage/PSUpatch/22191349/21948341” successfully analyzed on “/u01/app/oracle/” for apply.

Patch “/stage/PSUpatch/22191349/21948344” successfully analyzed on “/u01/app/oracle/” for apply.

Patch “/stage/PSUpatch/22191349/21948354” successfully analyzed on “/u01/app/oracle/” for apply.


Now you are good to apply the patch. Proceed to next step.




 10 min6Apply the Patch: (Note: This should apply patch in both GI and RDBMS Home but its unreliable in that sense so after this completes, we need to check opatch lsinventory to make sure that it also applied patches in RDBMS Home)


As root user, execute the following command:


# /u01/app/oracle/ apply /stage/PSUpatch/22191349 -ocmrf /stage/ocm.rsp


In case if it doesn’t apply in RDBMS Home, then run:


/u01/app/oracle/product/12.1.0/db_1/OPatch/opatchauto apply /stage/PSUpatch/22191349 -oh /u01/app/oracle/product/12.1.0/db_1 -ocmrf /stage/ocm.rsp


Make sure the above applies both OCW and PSU patches. You can verify that from opatch lsinventory. If only OCW patch is present in output and no PSU (which is likely the case), then issue following from Oracle home with oracle database owner after shutting down database:


/u01/app/oracle/product/12.1.0/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/12.1.0/db_1 -local /stage/PSUpatch/22191349/2194835460 min7Loading Modified SQL Files into the Database:


% sqlplus /nolog

SQL> Connect / as sysdba

SQL> startup

SQL> quit

% cd $ORACLE_HOME/OPatch

% ./datapatch -verbose60 min8Check for the list of patches applied to the database.


SQL> select action_time, patch_id, patch_uid, version, status, bundle_series, description from dba_registry_sqlpatch;5 min

Categories: DBA Blogs

New Round of Let’s Talk Database Events

Richard Foote - Thu, 2016-02-11 18:45
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, […]
Categories: DBA Blogs

Log Buffer #460: A Carnival of the Vanities for DBAs

Pythian Group - Thu, 2016-02-11 14:34

This Log Buffer Edition covers blog posts from Oracle, SQL Server and MySQL for this week.


APEX shuttle item with one direction

Wondering about which tasks and work products are essential for your project?

Using Spark(Scala) and Oracle Big Data Lite VM for Barcode & QR Detection

Cloning Oracle Home on fully patched 11.31 HP-UX hangs

An UNDO in a PDB in Oracle 12c?

SQL Server:

SQL Azure Performance Benchmarking

Monitoring In-Memory OLTP: What’s Important?

Find and Remove Duplicate Records SQL Server

A Database to Diagram For

Getting started with R scripts and R visuals in Power BI Desktop


MySQL Support People – Percona Support

How to Install Redmine 3 with Nginx on Ubuntu 15.10

The magical abandoned .ibd files after crash recovery with MySQL 5.7.10

How To Speed Up MySQL Restart (hint: just like before, but this time for real)

OmniSQL – Massively Parallel Query Execution

Categories: DBA Blogs

Oracle RAC on Azure

Pythian Group - Thu, 2016-02-11 14:31

Microsoft Azure provides an acceptable and affordable platform for a training environment. I am an Oracle DBA, and use it to test functionality, new technologies and features of different Oracle products. Azure supplies a template for Oracle linux and it can be used to run a single database, but when we try to create an Oracle RAC, we hit two major issues.

In the first, the Azure virtual network doesn’t support multicast and, as result, cannot be used for interconnect. The second issue is shared storage. Azure provides shared file storage, and you can access it using SMB-2 protocol, but it isn’t exactly what we need for RAC. How we can solve or workaround those problems? I will share my experience and show how I can setup a RAC on Azure.

For a two node RAC we first need to create at least two virtual machines for the cluster nodes. I’ve chosen Oracle Linux 6.4 from Azure Marketplace. I decided to create the machines with 2 network interfaces where one will be used for public, and another will be used for private interconnect. Here is my blog post how to create a VM with 2 network interfaces. It may not be necessary since you can fork a virtual interface out of your only public network, but I decided to go this way and create cluster nodes with two interfaces.

Here is output for the network from the first node:

[root@oradb5 network-scripts]# ifconfig
eth0 Link encap:Ethernet HWaddr 00:0D:3A:11:A3:71
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:a371/64 Scope:Link
RX packets:776 errors:0 dropped:0 overruns:0 frame:0
TX packets:789 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:96068 (93.8 KiB) TX bytes:127715 (124.7 KiB)

eth1 Link encap:Ethernet HWaddr 00:0D:3A:11:AC:92
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:ac92/64 Scope:Link
RX packets:2 errors:0 dropped:0 overruns:0 frame:0
TX packets:9 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:722 (722.0 b) TX bytes:1166 (1.1 KiB)

We need to install oracle-rdbms-server-12cR1-preinstall-1.0-14.el6.x86_64 rpm. It will install all required packages and set up kernel and limits for oracle user on our boxes :

yum install oracle-rdbms-server-12cR1-preinstall-1.0-14.el6.x86_64

The next step is to enable multicast support on the network for interconnect. You can read how to enable the multicast support in my other blog. As result you are getting a network interface edge0 which can be used now for our private network. Here is output of the ifconfig after crating virtual interface with support of multicast:

[root@oradb5 ~]# ifconfig
edge0 Link encap:Ethernet HWaddr 9E:1A:D8:0B:94:EF
inet addr: Bcast: Mask:
inet6 addr: fe80::9c1a:d8ff:fe0b:94ef/64 Scope:Link
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:3 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:500
RX bytes:0 (0.0 b) TX bytes:238 (238.0 b)

eth0 Link encap:Ethernet HWaddr 00:0D:3A:11:A3:71
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:a371/64 Scope:Link
RX packets:118729 errors:0 dropped:0 overruns:0 frame:0
TX packets:62523 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:143705142 (137.0 MiB) TX bytes:20407664 (19.4 MiB)

eth1 Link encap:Ethernet HWaddr 00:0D:3A:11:AC:92
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:ac92/64 Scope:Link
RX packets:9 errors:0 dropped:0 overruns:0 frame:0
TX packets:271 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:1274 (1.2 KiB) TX bytes:43367 (42.3 KiB)

I’ve used multicast tool from Oracle support document

Grid Infrastructure Startup During Patching, Install or Upgrade May Fail Due to Multicasting Requirement (Doc ID 1212703.1)

The check was successful:

[oracle@oradb5 mcasttest]$ ./ -n oradb5,oradb6 -i edge0
########### Setup for node oradb5 ##########
Checking node access 'oradb5'
Checking node login 'oradb5'
Checking/Creating Directory /tmp/mcasttest for binary on node 'oradb5'
Distributing mcast2 binary to node 'oradb5'
########### Setup for node oradb6 ##########
Checking node access 'oradb6'
Checking node login 'oradb6'
Checking/Creating Directory /tmp/mcasttest for binary on node 'oradb6'
Distributing mcast2 binary to node 'oradb6'
########### testing Multicast on all nodes ##########

Test for Multicast address

Nov 24 16:22:12 | Multicast Succeeded for edge0 using address

Test for Multicast address

Nov 24 16:22:13 | Multicast Succeeded for edge0 using address
[oracle@oradb5 mcasttest]$

So, we have solved the first obstacle and need to get shared storage for our RAC. We have at least a couple of options here, and I believe somebody can advise us on others. We can use NFS based shared storage, or we can use iscsi for that. You may choose something from the Azure Marketplace like SoftNAS or Stonefly or you may decide to create your own solution. In my case I just faired another Oracle Linux VM, added couple of storage disks to it using portal, and then set up NFS server on that machine. Here is the high level description for that:
We create a linux based VM on Azure using Oracle Linux 6.4 template from Marketplace. The size will be dictated by your requirements. I called the machine oradata.
I’ve added a 20 Gb disk to the oradata machine through the Azure portal, and created a partition and filesystem on it:

[root@oradata ~]# fdisk -l
[root@oradata ~]# fdisk /dev/sdc
[root@oradata ~]# mkfs.ext4 /dev/sdc1
[root@oradata ~]# mkdir /share
[root@oradata ~]# mkdir /share/oradata1
[root@oradata ~]# e2label /dev/sdc1 sharedoradata1
[root@oradata ~]# vi /etc/fstab
[root@oradata ~]# mount -a
[root@oradata ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 7.4G 1.4G 5.7G 19% /
tmpfs 1.7G 0 1.7G 0% /dev/shm
/dev/sda1 485M 50M 410M 11% /boot
/dev/sda2 2.0G 67M 1.9G 4% /tmp
/dev/sdc1 20G 4.2G 15G 23% /share/oradata1
/dev/sdb1 60G 180M 56G 1% /mnt/resource
[root@oradata ~]#

Installed necessary utilities using yum:
[root@oradata ~]# yum install nfs-utils
Configured NFS server on the box:

[root@oradata ~]# chkconfig service nfs on
[root@oradata ~]# vi /etc/exports
[root@oradata ~]# cat /etc/exports
[root@oradata ~]# service nfs restart
[root@oradata ~]# showmount -e
Export list for oradata:

Configure or stop firewall(You may need to do it on your cluster nodes as well) :

[root@oradata ~]# service iptables stop
iptables: Flushing firewall rules: [ OK ] iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Unloading modules: [ OK ] [root@oradata ~]# chkconfig iptables off
[root@oradata ~]#

On your cluster nodes you need add the mountpoint for your shared storage to /etc/fstab and mount it.

[root@oradb5 ~]# vi /etc/fstab
[root@oradb5 ~]# cat /etc/fstab | grep nfs
oradata:/share/oradata1 /u02/oradata nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,actimeo=0,vers=3,timeo=600 0 0
[root@oradb5 ~]# mount -a
[root@oradb5 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 7.4G 2.5G 4.6G 36% /
tmpfs 3.5G 0 3.5G 0% /dev/shm
/dev/sda1 485M 69M 391M 15% /boot
/dev/sda2 2.0G 86M 1.8G 5% /tmp
/dev/sdc1 60G 12G 45G 21% /u01/app
/dev/sdb1 281G 191M 267G 1% /mnt/resource
20G 4.2G 15G 23% /u02/oradata
[root@oradb5 ~]# mount | grep /u02/oradata | grep -v grep
oradata:/share/oradata1 on /u02/oradata type nfs (rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,actimeo=0,vers=3,timeo=600,addr=
[root@oradb5 ~]#

Now we have the required storage for OCR and Voting disks, network for public and interconnect, and can install our cluster.
We need to correct /etc/hosts file on both nodes (you may choose to use Azure DNS service instead).

[oracle@oradb5 ~]$ cat /etc/hosts localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 oradb5 oradb6 oradb5-vip oradb6-vip oradb-clst-scan oradb5-priv oradb6-priv oradata
[oracle@oradb5 ~]$

You can see I setup the public,VIP and SCAN in the hosts file. Of course it is not acceptable for any production implementation or if you want to have more than one scan. As I’ve already mentioned above you can use DNS for proper installation.
We copy required software to one of the nodes, unpack it and create a response file for installation like:

[oracle@oradb5 ~]$ cat grid.rsp

The file can be used for silent installation. You may choose instead to use runInstaller in GUI mode.
To run installation in silent mode you just need to go to your unpacked software and run:

[oracle@oradb5 grid]$ ./runInstaller -silent -responseFile /home/oracle/grid.rsp -ignoreSysPrereqs -ignorePrereq
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB. Actual 1350 MB Passed
Checking swap space: 0 MB available, 150 MB required. Failed <<<>> Ignoring required pre-requisite failures. Continuing…

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-02-01_09-41-01AM. Please wait …

You’ve of course noticed that I’ve run the installation ignoring requirements. As a matte of fact, I ran it without ignoring, checked the failing checks, made necessary adjustments for those checks, and then I decided they were important and left other as they were. As example my /etc/resolve.conf file was different due to settings on dhcp server and so on. I advise to apply common sense and your knowledge to decide what checks are important for you and what can be ignored.
Your installation will be completed and all you need to run is a couple of scripts to finish the installation.

As a root user, execute the following script(s):
1. /u01/app/12.1.0/grid/

Execute /u01/app/12.1.0/grid/ on the following nodes:
[oradb5, oradb6]

Run the script on the local node first. After successful completion, you can start the script in parallel on all other nodes.

Successfully Setup Software.
As install user, execute the following script to complete the configuration.
1. /u01/app/12.1.0/grid/cfgtoollogs/configToolAllCommands RESPONSE_FILE=

1. This script must be run on the same host from where installer was run.
2. This script needs a small password properties file for configuration assistants that require passwords (refer to install guide documentation).

We run the on each node one by one as user root and execute configToolAllCommands script as user oracle on the node we have run our installation. The response file would be required if we specified password for ASM,ASM monitoring or for DBCA. Here is an example of the file contents:


Change permission for the file to 600 before running the script:

[oracle@oradb5 grid]$ vi /home/oracle/
[oracle@oradb5 grid]$ chmod 600 /home/oracle/

We don’t have any ASM in our installation or BMS console but I will leave the file nevertheless just for reference.
Here is an output what we ran on our system :

[root@oradb5 ~]# /u01/app/12.1.0/grid/
Check /u01/app/12.1.0/grid/install/root_oradb5_2016-02-01_10-21-07.log for the output of root script

[root@oradb6 ~]# /u01/app/12.1.0/grid/
Check /u01/app/12.1.0/grid/install/root_oradb6_2016-02-01_10-38-50.log for the output of root script

[oracle@oradb5 grid]$ /u01/app/12.1.0/grid/cfgtoollogs/configToolAllCommands RESPONSE_FILE=/home/oracle/
Setting the invPtrLoc to /u01/app/12.1.0/grid/oraInst.loc

perform – mode is starting for action: configure

Keep in mind the configToolAllCommands should also create the management database in your cluster. If somehow it was failed you can try to recreate it using dbca in silent mode like :

/u01/app/12.1.0/grid/bin/dbca -silent -createDatabase -createAsContainerDatabase true -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType FS -datafileDestination /u02/oradata/ocr/oradb-clst/mgmtdb -datafileJarLocation /u01/app/12.1.0/grid/assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -skipUserTemplateCheck -oui_internal

The RAC is created and now it can be used for application high availability or for databases tests. You may install a database software on the RAC either using GUI installer or silent mode, but don’t forget to specify cluster nodes during installation. I would also like to mention that I would not recommend installing it as production system, but it is quite suitable for tests or to experiment if you want to verify or troubleshot some RAC specific features.

Categories: DBA Blogs

Speed, Not Surprises, Is the Secret to SaaS Simplified UIs

Usable Apps - Thu, 2016-02-11 12:31

Julian Orr (@orr_ux) of the Oracle Applications User Experience (OAUX) team explains how the OAUX Rapid Development Kit enables the fast and furious pace of PaaS4SaaS development and lets partners and customers confidently deliver stunning simplified UIs in the cloud.

For SaaS and PaaS developers, code is always “on.” They need to work like lightning. That’s because the cloud has changed SaaS customer expectations; if they don’t like their user experience (UX) or don’t get it in time, they’ll go elsewhere.

“PaaS has been a great catalyst for change,” agrees Basheer Khan (@bkhan) Principal at Oracle Partner Knex Technology. “It’s transformed how we develop applications.” Basheer knows that cloud customers will not wait months for application integration and hearing that “but-all-I-wanted-was” lament after a hastily delivered solution wins business only for his competitors.

 Joe Rovirosa, Financial Controller at Pacific Health Foundation Enterprises and Basheer Khan of Knex Technology sketch PaaS4SaaS UX ideas together.

Together in the Oracle Cloud: Joe Rovirosa, Financial Controller at Pacific Health Foundation Enterprises and Basheer Khan of Knex Technology sketch PaaS4SaaS UX ideas together.

SaaS and PaaS Accelerated Development for Real

The Oracle Applications User Experience (OAUX) team recently worked with Knex Technology to deliver a simplified UI SaaS solution for their customer Public Health Foundation Enterprises (PHFE). Using OAUX’s Simplified UI PaaS4SaaS Rapid Development Kit (RDK) an Oracle ERP SaaS solution was designed, developed, and deployed to the Oracle Cloud during an OAUX-facilitated hands-on workshop for partner and customer.

 Part of the free Rapid Development Kit so that SaaS and PaaS developers don’t have to research the best usability practices.

Simplified UI user experience design patterns: Part of the free Rapid Development Kit so that SaaS and PaaS developers don’t have to research the best usability practices.

The free RDK contains Oracle ADF simplified UI page templates and components, an Oracle Alta UI look and feel, and the UX design patterns that enable partners to rapidly assess a business opportunity, develop it quickly using designs from the Oracle Applications Cloud, and then deploy a SaaS integration or PaaS custom app to the Oracle Cloud. OAUX enhances the RDK with online training and one-on-one workshops for partners and their customers.

Basheer explained the essence of the RDK for partners:

“The RDK’s best design practices are embedded into the development tools that make the end product, so developers don’t have to start from scratch figuring out a user experience. Those reusable software components of the kit make it efficient for partners to offer customers low cost solutions.”

Applying simplified UI design patterns during the software development process is a real PaaS4SaaS developer productivity win. Patterns are reusable solutions for developers (how a landing page or visualization should work, for example) that accelerate the development of a user experience that’s proven in advance. Developers can concentrate instead on enterprise-critical technical areas, such as performance and security.

Partners and Customers Together in the Oracle Cloud

Central to the success of PHFE’s solution was their direct involvement in the design process. Representatives from PHFE’s financial and human resource departments acted as typical PHFE users and helped to sketch out (or “wireframe”) an agreed user experience solution. From Leonardo Da Vinci to Walt Disney, this sketching of ideas before committing to an expensive production process allows for easy exploration of options, faster iterations, use of industry standard designs, and eliminates unpleasant surprises at the end. That’s a perfect accelerator for a partner growing an Oracle Cloud business with that killer UX differentiator.

 Stakeholder agreed wireframes and patterns accelerated developer cloud productivity for an Oracle Cloud ERP solution.

From sketched idea to cloud deployment: Stakeholder agreed wireframes and patterns accelerated developer cloud productivity for an Oracle ERP Cloud solution. The latest version of the RDK resources contains a wireframe template that allows you to design simplified UI wireframes using Microsoft PowerPoint.

Basheer encourages this collaborative approach:

“When the customer is involved, solutions are delivered 5 to 10 times quicker to market. The UX tools and guidance make sure the customer requirements are not lost in translation. Customers get to understand what user experience is about and to take early ownership of “their” design that they take away.”

OAUX and Oracle Applications Development enables and guides Knex Technology to deliver a solution for PHFE at a workshop in Oracle HQ

Oracle Applications User Experience Group Vice President Jeremy Ashley (back, left) and PaaS4SaaS UX designer Julian Orr (back, right) let the design learning transfer as Basheer Khan (front, right) agrees an API-connected solution with Oracle Applications developer Suresh Punathilath.

PHFE’s Financial Controller Joe Rovirosa’s own experience of the OAUX enablement also echoes the power of customer involvement:

“We realized that we needed all stakeholders to be present in order to compliment key skill sets. Having Oracle, Knex, and PHFE in one room allowed business requirements and application capabilities to be immediately vetted, resulting in a very efficient process and a superior solution. Such interaction works best for any implementation.”

From UX Mystery to Cloud Magic

This partner-customer enablement based on the PaaS4SaaS RDK is now an OAUX-proven approach that has seen other Oracle partners such as Hitachi Consulting, Certus Solutions, and eProseed win awards and kudos from North America to Europe to Asia. And that means more Cloud business for the partner network.

Partners and customers availing of the PaaS4SaaS enablement have their designs reviewed by OAUX Group Vice President, Jeremy Ashley (@jrwashley) who underlined Oracle’s commitment to growing the Oracle partner ecosystem with a must-have UX:

“Partners can have confidence in the whole RDK as a standalone, proven, consistent cloud developer productivity solution that in turn will give their customers real confidence in an SaaS user experience their users want.”

Developer speed, no nasty surprises, and a great simplified SaaS UI are all easily realized with the latest version of the PaaS4SaaS RDK. It’s now being downloaded for free by Oracle partners and developers who want to lead and win with the development of SaaS solutions using PaaS.

OAUX plans further enhancements to the RDK itself, exploring other frameworks, wearable technology integration, mobile application development, and more developer experience ideas all based on Oracle Cloud solutions and reflecting the simplicity, mobility, and extensibility UX strategy.

Information on how to get the RDK and on OAUX partner enablement events is available on the Oracle Usable Apps website.

More Information

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

David Kurtz - Thu, 2016-02-11 12:07
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.  Oracle can calculate the optimal prefix length using
I have written script to make it slightly easier, calc_opt_comp.sql.  This is the output on my demo database, but I get similar results on production systems.
Compression Weighted
Prefix Current Average
Table Name Index Name Length FREQ PARTS Blocks Saving %
------------------ ------------------ ----------- ---- ----- ---------- --------
PSBPSTREENODE 3 1 0 264 30.0
PSCPSTREENODE 1 1 0 120 7.0
PSDPSTREENODE 4 1 0 256 61.0
PSFPSTREENODE 2 1 0 256 67.0
PSGPSTREENODE 3 1 0 400 49.0
PS_PSTREENODE 4 1 0 256 44.0 
However, I want to make sure that should the table need to rebuilt in the future, PeopleTools will generate the DDL with the appropriate settings.  The same principle would also apply to any other physical storage option.  I would always recommend that the compression prefix lengths be incorporated into the PeopleTools DDL override in Application Designer (figure 1).  While you could extend the DDL model and add another override for compression, I just append it to the PCTFREE setting.
Index DDL OverridesFigure 1. Index DDL OverideHowever, there is catch.  PeopleTools has never examined DDL overrides when determining whether there is a difference between the PeopleSoft and database data dictionaries, even though that comparison must be platform specific.  DDL overrides and DDL models are just strings held in the PeopleTools tables.  They can be extended (or even removed) by customers.  I assume this is the reason; it was not felt possible to reliably check them,
So, if the build settings (figure 2) are 'recreate index only if modified', which is the default, Application Designer will not generate a DDL script, nor execute any DDL.
Build SettingsFigure 2. Build SettingsThe workaround has always been to set the index creation option in the build settings to 'recreate index if it already exists'.  However, we then discover the override doesn't appear in the DDL.  As Application Designer has not detected a difference between PeopleTools and the database, it has instead used the Oracle DBMS_METADATA package to generate the storage clause from from the index that exists in the database.  Hence the DDL contains additional keywords not in the PeopleSoft DDL model.
I have only checked this behaviour on PeopleTools 8.54, but use of DBMS_METADATA was introduced in PeopleTools 8.51, so this problem has probably existed since then.
SELECT dbms_metadata.get_ddl('INDEX','PS_PSTREENODE')
FROM dual

However, if I drop the index and then regenerate the DDL script in Application Designer,
DROP INDEX ps_pstreenode
PeopleTools generates the create index with the compression specified in the PeopleTools table.


Rather than go through the business of dropping the index so you can then generate the correct script to then recreate the index, I would suggest just implementing the change manually by rebuilding the indexes.

ConclusionThis makes the business of implementing physical attributes through Application Designer much more complicated.  I would still recommend recording the settings in Application Designer, if only because it provides documentation, but then it may be easier to implement the changes manually.©David Kurtz

Upgrading Oracle Apps (EBS) to 12.2 ? ORA-29283: Invalid File Operation

Online Apps DBA - Thu, 2016-02-11 10:28
This entry is part 7 of 7 in the series Oracle EBS 12.2 Upgrade

This post covers issue while running American English Upgrade patch driver reported in our Oracle EBS Upgrade R12.2 training (next batch starts on 20th Feb and only limited seats are available . We limit number trainees to 15 and where we cover Architecture, Overview of R12.2 & Major features in Upgrading to R12.2, Different upgrade paths available to R12.2, Best practices for R12.2 Upgrade, How to minimize down time for R12.2 Upgrade, Difficulties/Issues while upgrading to R12.2)

One of the trainee from our previous batch, encountered  issue “ORA-29283: Invalid File Operation” while running American English Upgrade patch driver.


1. Running the merged driver on the administration server node on Release 12.2 APPL_TOP using the following commands:

cd $ORACLE_BASE/fs1/EBSapps/appl/au/12.0.0/patch/115/driver

adpatch options=nocopyportion,nogenerateportion

Note: Merged driver must be applied using adpatch options=nocopyportion,nogenerateportion on RUN File System.

2. But Patch got failed with below error messages.


As it was showing issue on worker1 so we looked into log file of worker 1 located at

$ORACLE_BASE/fs1/EBSapps/appl/admin/PRD122/log and it was showing below issue



Issue with the write permissions in $APPLPTMP/UTL_FILE_DIR. wmsgrlspecn.sql creates a file under $APPLPTMP/ UTL_FILE_DIR with the name GenRulepkgs.log.

The error will occur if this file exists under a different user (If you have multiple environments owned by different O.S. user on server then you could run into issues like this).


To resolve this issue follow the steps as given below:

1. Ensure that the user running the patch or the script has write permissions to $APPLPTMP/UTL_FILE_DIR location. To find the value for utl_file_dir

sqlplus apps/<apps password>

SELECT name, value FROM v$parameter WHERE name LIKE ‘utl_file_dir';

2. If $APPLPTMP/GenRulepkgs.log exists under a different user, then change permissions to 755 on the file so that current user can write to it.

chmod 755 $APPLPTMP/GenRulepkgs.log

3. Restart adpatch again as

cd $ORACLE_BASE/fs1/EBSapps/appl/au/12.0.0/patch/115/driver

adpatch options=nocopyportion,nogenerateportion

And this time adpatch completed successfully.


  • Script Wmsgrlspecn.sql Fails With ORA-29283: Invalid File Operation (Doc ID 314293.1)

If you want to learn more about Oracle EBS Upgrade to R12.2  then click the button below and register for our  Oracle Upgrade 12.2  (next batch starts on 20th February, 2016 )

Note: We are so confident on our workshops that we provide 100% Money back guarantee, in unlikely case of you being not happy after first sessions, just drop us a mail before second session and We’ll refund FULL money.

Oracle E-Business Suite Upgrade to R12.2 Training

Live Instructor led Online sessions with Hands-on Lab Exercises, Dedicated Machines to Practice and Recorded sessions of the Training

Click here to learn more with limited time discounts

Stay Tuned for more Information on Oracle Apps 12.2 Upgrade!!

The post Upgrading Oracle Apps (EBS) to 12.2 ? ORA-29283: Invalid File Operation appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Quiz Night

Jonathan Lewis - Thu, 2016-02-11 09:28

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:

--      Script:  12c_vc32767_b.sql

create table t1 (
        id      not null,
        rownum                                          id,
                add_months(sysdate, -120) +
                        (rownum)* 3652 / 1e7
        )                                               date_open,
                        add_months(sysdate, -120) +
                                (rownum) * 3652 / 1e7,
                        12 * trunc(dbms_random.value(1,6))
        )                                               date_closed,
        dbms_random.string('U',1)                       deal_type,
        dbms_random.string('U',4)                       client_ref,
        lpad(rownum,10)                                 vc_small,
        rpad('x',100,'x')                               vc_padding
connect by
        level <= 10000
alter table t1 modify deal_type varchar2(1);
alter table t1 modify client_ref varchar2(4);

create table t2 nologging as select * from t1;

                method_opt=>'for all columns size 1'
                method_opt=>'for all columns size 1'

select table_name, num_rows, blocks, avg_row_len from user_tables;

-------------------- ---------- ---------- -----------
T1                        10000        304         139
T2                        10000        218         139

There’s no hidden code – this is the code I ran, and the tables both went into the same tablespace- yet one table has used about 30% more blocks than the other on a simple “create as select”, even though the row lengths and row counts are the same!

When I first did this test it just so happened that the only place in the database I had to make the t2 copy was in a tablespace using freelist management, and I’d created the original table in a tablespace using ASSM, so I wasted a little time examining the ASSM space management (bitmap) blocks before I realised what had happened.

Suggestions about what has happened, and how to confirm your hypothesis, invited in the comments.

Update (following morning)

The explanation is in the comments: I had enabled extended (32,767 byte) varchar2().  My calls to dbms_random.string() had returned character values, and the return type defaults to the maximum character length, which meant they were declared as varchar2(32767) and those are implemented as (unhackable) CLOBs in

When I modified the table to change the varchar2(32767) to shorter lengths Oracle let me get away with it because none of the actual stored values at that time was longer than my target declaration – but it didn’t physically change the stored column values to “short” varchar2(), it left them as CLOBs, and a “short” CLOB includes a LOB Locator which starts out at about 30 bytes.

When I created t2 from t1 Oracle first copied the (short) column definitions, and then copied the data, reading and converting the CLOBs to normal varchar2() storage, and that explains why t2 needed fewer blocks.  The avg_row_len for the two tables matched because the code to gather stats simply applies the sys_op_opnsize() function to the varchar2() defined in t1, and doesn’t give you any indication about the LOB locator.

(If you use the extremely deprecated analyze command to gather stats on the two tables you’ll find that the avg_row_len of table t1 then allows for the LOB locator size.)


I should have used:  cast(dbms_random.string(‘U’,4) as varchar2(4)) and the column definition would have been a proper varchar2(4) from the start.  (As noted in a comment from Ivica Arsov below, substr(…, 1, 4) would also have worked.  Of course I should have used cast() for the numerics as well so that I could declare them as things like number(8,0) rather than the slightly dangerous “number”.

Note:  I’ve put “workaround” in quotes because it’s not really a workaround to a problem – it’s just an example of doing it right.


Here’s a symbolic dump of a single character (‘T’) being stored as a varchar2(32767):

  Length:        84(31)
  Version:        1
  Byte Length:    1
  Flags[ 0x01 0x0c 0x00 0x80 ]:
    Type: BLOB
    Storage: SecureFile
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: ReadWrite
  SecureFile Header:
    Length:   11
    Old Flag: 0x48 [ DataInRow SecureFile ]
    Flag 0:   0x90 [ INODE Valid ]
      Lengths Array: INODE:5
        00 00 01 01 54

Note the 84(31) at line 3: this is a reminder of how big a LOB locator could get for an out of line LOB, compared to the current size of the LOB locator. Rows that hold out of line LOBs can actually be much longer than avg_row_len tells you.

LittleArduinoProjects#100 Retrogaming on an Arduino/OLED "console"

Paul Gallagher - Thu, 2016-02-11 08:43
(blogarhythm ~ invaders must die - The Prodigy)
Tiny 128x64 monochrome OLED screens are cheap and easy to come by, and quite popular for adding visual display to a microcontroller project.

My first experiments in driving them with raw SPI commands had me feeling distinctly old school, as the last time remember programming a bitmap screen display was probably about 30 years ago!

So while in a retro mood, what better than to attempt an arcade classic? At first I wasn't sure it was going to be possible to make a playable game due to the limited Arduino memory and relative slow screen communication protocol.

But after a few tweaks of the low-level SPI implementation, I was surprised myself at how well it can run. Even had enough clock cycles left to throw in a sound track and effects.

Here's a quick video on YouTube of the latest version. ArdWinVaders! .. in full lo-rez monochrome glory, packed into 14kb and speeding along at 8MHz.

As always, all notes, schematics and code are in the Little Arduino Projects repo on GitHub.

Full source and schematics are in the LittleArduinoProjects collection on Github.

LittleArduinoProjects#173 Mini 64-LED Cube

Paul Gallagher - Thu, 2016-02-11 08:42
LED cubes were a "thing" a few years back maybe ... but I've never built one. Time to fix that...

Here's my "mini" 4x4x4 cube - 3cm per side with 3mm clear blue LEDs. Pretty compact, and delivers nice effects. The clear blue LEDs work really well - very bright, even when driven with minimal current.

It's encased in a Ferrero Rocher cube box. During the build, that raised some challenges - most of the effort in building the project concerned squeezing all the electronics into the space in the lid (which becomes the base of the cube). Not quite as small as HariFun's "World's Tiniest RGB LED Cube" but about as small as you can get without resorting to SMD LEDs!

As always, all notes, schematics and code are in the Little Arduino Projects repo on GitHub. Here's a quick demo:

LittleArduinoProjects#174 USB LED Notifiers

Paul Gallagher - Thu, 2016-02-11 08:42
So four of these USB Webmail Notifier devices turned up in a dusty cupboard
in the office.

A quick tear-down shows they contain a super-simple circuit - just a
SONiX Technology SN8P2203SB 8-Bit microcontroller that handles the USB protocol and drives an RGB LED. The SN8P2203SB is an old chip phased out 2010/04/30, superseded by the SN8P2240. They have a supremely primitive USB implementation - basically mimicking a very basic USB 1.0 HID device.

A quick google reveals quite a bit of old code lying around for various projects using devices like this. Most seem to use libusb for convenience - and often 0.1 legacy libusb that. As I'm mainly on MacOSX, the code is not much use since Apple no longer allows claiming of HID devices
and the libusb team decided not to try to get around that.

So to bring things up-to-date, I wrote a simple demo using hidapi
and things all work fine - see the video below.

Now I just need to ponder on good ideas for what to do with these things!

As always, all all notes, schematics and code are in the Little Arduino Projects repo on GitHub.

LittleArduinoProjects#181 Voltmeter Modules

Paul Gallagher - Thu, 2016-02-11 08:42
Voltmeter modules are a very convenient way of adding voltage display to any project, because they require no supporting circuitry or microcontrollers.

There are two and three wire modules in the market. Two-wire modules are the simplest (and generally cheapest). For most applications where a simple readout of a power supply is required, they are most convenient since a separate power supply connection is not required. It does mean of course that the meter draws current from the circuit under test, and will only work down to a certain voltage (usually ~4.5V).

For more precision measurement, the three-wire modules are best as they present relatively high input impedance (> 100kΩ), and can measure voltages all the way down to 0. As an example, Adafruit have this 3-wire module in their catalogue, but they are widely available from most online sellers.

I recently received a 3-wire module as part of a kit and put it to the test. Using my DMM as a reference, it appears accuracy is within 40mV, good enough for general purposes.

As always, all notes, schematics and code are in the Little Arduino Projects repo on GitHub.

LittleArduinoProjects#180 Colpitts Oscillator

Paul Gallagher - Thu, 2016-02-11 08:41
A Colpitts oscillator uses a combination of inductors and capacitors to produce an oscillation at the resonant frequency of LC circuit.

To see that in action, I built one on a protoboard and it delivers an almost perfect 22.9kHz .. compared to the theoretical 22.5kHz.

As always, all notes, schematics and code are in the Little Arduino Projects repo on GitHub.

Here's a trace of the output signal on CH1, and the mid-point of the capacitor pair on CH2:

LittleArduinoProjects#182 Building a Bench Power Supply

Paul Gallagher - Thu, 2016-02-11 08:40
I've wanted a variable mains-powered power supply for a while, so when I found this kit for a reasonable price I decided to give it a go. Some things that attracted me:

  • 220V/110V mains-powered
  • continuously adjustable output voltage
  • isolated output
  • nice acrylic case
  • built-in LED voltmeter

The kit and PCB comes with a few "valued-added features" unrelated to the power supply function (CD4069 square-wave generator, externally-triggered piezo buzzer, externally-triggered polarity tester), but I decided to leave those out of the build.

I also enjoyed investigating and old-school transformer-based power supply. These are getting rare .. it's hard to even find a 220/12 transformer for less than the price of the kit these days, and then they are mostly used/refurbished.

How does it perform? Nice! No smoke on power-up, but a few things to note and/or improve. And I'm sure it wouldn't pass a safety certification, so don't go building one like this for friends.

As always, all notes, schematics and code are in the Little Arduino Projects repo on GitHub.