Large listener.log file size causing Oracle Listener to fail

Recently, I ran into a case in which the user was attempting to log on to a database, but the log on process was simply churning and churning without coming to an end. When I tried to perform a TNSPING against it, that process also failed to return any results, or at least not in a reasonable amount of time.

Thinking that it might be caused by a dead listener, I went on to the server and attempted to use the LSNRCTL command to restart the listener. Surprisingly, I was encountered with a number of errors, including “TNS-12541: TNS:no listener” and “TNS-12560: TNS:protocol adapter error”.

I was able to find a much more knowledgeable DBA to locate root problem — On this Windows-hosted Oracle database server, the listener.log file had grown too large. Our solution:

1. Archive the old listener.log file
2. Create an empty file by the same name

After those two action, the listener was able to restart successfully, allowing user connections once again.

Implementing OpenStreetMap

Google recently announced a change in their Maps API usage quota that will inevitably move some current users from the previously free tier into a paid tier. I thought this might perhaps drive more developers to switch from Google Maps to OpenStreetMap if only for budget reasons, so hopefully the following guide would help get things started.

OpenStreetMap implementation is actually just as simple as Google Maps, especially when coupled with Javascript libraries focused on building maps. In the following example, I used Leaflet. As far as tiles are concerned, I used tiles by Stamen. While I personally like Stamen’s tiles a lot, there are many tiles available across the web with different styles, and some might fit your particular niche or style better; check out this link for a few others. Finally, for the data, I used some data from Lava’s WW2DB project.

To get things started, I created a div that will eventually contain the map. Then, I linked to the .css stylesheet and the .js package files:

<div id="mapdiv" style="width: 400px; height: 400px;"></div>
<link rel="stylesheet" href="" integrity="sha512-Rksm5RenBEKSKFjgI3a41vrjkw4EVPlJ3+OiI65vTjIdo9brlAacEuKOiQ5OFh7cOI1bkDwLqdLw3Zg0cRJAAQ==" crossorigin=""/>
<script src="" integrity="sha512-/Nsx9X4HebavoBvEBuyp3I7od5tA0UzAxs+j83KgC8PU0kgB4XiK4Lfe4y4cgBtaRJQEIFCW+oC506aPT2L1zw==" crossorigin=""></script>

Then, a snippet of PHP code fetched necessary data from the WW2DB backend database, and used the data to build a Javascript array. The resulting Javascript array that I built looked something like this:

var rawDataArray = [
	{ "name": "This is marker #1", "lat": "10", "lng": "10" },
	{ "name": "This is marker #2", "lat": "20", "lng": "20" },
	{ "name": "This is marker #3", "lat": "30", "lng": "30" },

Now comes the main part. You will see that it is not too complex, even if have little experience with Google Maps, OpenStreetMap, or Leaflet.

// Initialize the map (parameters: initial latitude, initial longitude, zoom level)
var mymap ='mapdiv').setView([0, 0], 13);

// Specify tiles
L.tileLayer('https://stamen-tiles-{s}{z}/{x}/{y}.{ext}', {
	attribution: 'Tiles <a href="">Stamen Design</a>, <a href="">CC BY 3.0</a> — Data <a href="">OpenStreetMap</a>',
	subdomains: 'abcd',
	minZoom: 0,
	maxZoom: 18,
	ext: 'png'

// Loops through the raw data, place a point on the map, and build an array of the points
var circleArray = [];
for (var i = 0; i < rawDataArray.length; i++) {
	var rawData = rawDataArray[i];
	circle = new[rawData["lat"], rawData["lng"]], {
		color: 'red',
		fillColor: '#f03',
		fillOpacity: 0.5,
		radius: 50

// Adjust the map view based on the array of points, so the map won't appear to be zoomed overly in or out
var group = new L.featureGroup(circleArray);

Here's an example I implemented for showing WW2-era facilities found on the island of Taiwan:

While this worked for 99% of maps found on WW2DB, I discovered a small problem with Leaflet in which the .fitBounds() method did not handle things well when a single data set contains points on both sides of the International Date Line. To be fair, this is not truly a Leaflet problem; this issue is generally a headache across all mapping programs, although this is something I did not notice with Google Maps before. Here is an example demonstrating said problem involving points located in the Territory of Alaska:

I developed a work-around for this. Recall I used a PHP snippet to fetch data from the backend and to build the Javascript array for Leaflet to use. I modified the PHP code so that as I looped through the data, I would also keep counts of points with really low (I used 120 or less) or really high (I used 120 or more) longitude values. With this two counts, I could then have the PHP determine whether I would bring some points to the other side of the International Date Line by adding or subtracting 360 degrees to their longitude values. At this time, I made that determination by evaluating the number of points that would remain/would be moved. For the case of Alaska, with this logic applied, the PHP code would decide that all data points with longitude value less than 120 will have 360 added to it; for example, (54.359349,-159.66807) will become (54.359349,200.33193). After applying this work-around, the Alaska map becomes this:

Installing xRDP on Ubuntu

I installed xRDP on my Ubuntu 16.04 LTS so that I can easily connect to my Linux box from any Windows machine using the remote desktop tool that comes by default with every Windows installation. The actually installation is very simple:

sudo apt-get install xrdp

Through another post, you will see that I have the Xfce4 desktop environment installed:

Xfce4 plays very well with xRDP. To launch Xfce4 when a xRDP session is conneted, add this line to your .xsession file:

echo xfce4-session >~/.xsession

And then edit your file using your favorite text editor (nano is used below as example); add “startxfce4” without the quotes to the end of that file.

sudo nano /etc/xrdp/

At this point, you can either restart the machine, or run the two commands below to ensure xrdp is ready to accept connections.

sudo service xrdp restart
sudo /usr/sbin/xrdp-sesman

Finally, I noticed that when I am connected from a Windows machine, my tab key did not work correctly, causing me to lose the ability to autocomplete file names, among other things. It ended up the fix is very easy. Again, launch your favorite text editor to open up this Xfce configuration file:

nano ~/.config/xfce4/xconf/xfce4-keyboard-shortcuts.xml

Look for this line:

<property name="<Super>Tab" type="string" value="switch_window_key" />

And modify it to this line below:

<property name="<Super>Tab" type="empty" />

Installing Xfce4 Desktop Environment

Xfce is a light weight desktop environment for Linux that is suitable for those who prefer to not waste system resources on eye candy, for those who prefer to keep things simple, or for those who want to add a few more years of useful life to older machines. Below are the steps I followed to install Xfce4 on my machine running Ubuntu 16.04 LTS.

1. Minimally, I needed to install the main Xfce4 package.

sudo apt-get install xfce4

One of the first things I did was to adjust how the clock displayed. This guide helped me get started:

%% a literal %
%a locale's abbreviated weekday name (e.g., Sun)
%A locale's full weekday name (e.g., Sunday)
%b locale's abbreviated month name (e.g., Jan)
%B locale's full month name (e.g., January)
%c locale's date and time (e.g., Thu Mar  3 23:05:25 2005)
%C century; like %Y, except omit last two digits (e.g., 21)
%d day of month (e.g, 01)
%D date; same as %m/%d/%y
%e day of month, space padded; same as %_d
%F full date; same as %Y-%m-%d
%g last two digits of year of ISO week number (see %G)
%G year of ISO week number (see %V); normally useful only with %V
%h same as %b
%H hour (00..23)
%I hour (01..12)
%j day of year (001..366)
%k hour ( 0..23)
%l hour ( 1..12)
%m month (01..12)
%M minute (00..59)
%n a newline
%p locale's equivalent of either AM or PM; blank if not known
%P like %p, but lower case
%r locale's 12-hour clock time (e.g., 11:11:04 PM)
%R 24-hour hour and minute; same as %H:%M
%s seconds since 1970-01-01 00:00:00 UTC
%S second (00..60)
%t a tab
%T time; same as %H:%M:%S
%u day of week (1..7); 1 is Monday
%U week number of year, with Sunday as first day of week (00..53)
%V ISO week number, with Monday as first day of week (01..53)
%w day of week (0..6); 0 is Sunday
%W week number of year, with Monday as first day of week (00..53)
%x locale's date representation (e.g., 12/31/99)
%X locale's time representation (e.g., 23:13:48)
%y last two digits of year (00..99)
%Y year
%z +hhmm numeric timezone (e.g., -0400)
%Z alphabetic time zone abbreviation (e.g., EDT)

If you are curious, my setup is:

%a, %d %b %Y, %r

Which translates to, for example, “Fri, 30 Mar 2018, 10:25:25 PM”

Xfce follows the “do one thing, and do it well” philosophy, so it is literally just a desktop environment and nothing else. Read on to see the few additional packages I installed as add-ons for my Xfce installation.

2. Out of box, Xfce did not come with an application menu. I opted for Whisker menu.

sudo add-apt-repository ppa:gottcode/gcppa
sudo apt-get install xfce4-whiskermenu-plugin

You can customize various things with Whisker menu for the right look and usability that suits you.

3. I had installed this on a laptop, so it would be nice to display a battery meter. This can be done through xfce4-power-manager. As a bonus, this package also gave the ability to adjust screen brightness via a GUI tool.

sudo apt-get install xfce4-power-manager

4. There are tons of screenshot tools available for Linux, and there are actually several that are better than the Xfce one. I installed the Xfce screenshot tool nevertheless, to try it out as part of the greater Xfce offering.

sudo apt-get install xfce4-screenshooter-plugin

I set up a keyboard shortcut to the Print Screen key. The short cut runs:

xfce4-screenshooter -w -s ~/pics/screenshots/

I had wanted to try out Xfce purely out of curiocity, but I did get a nice bonus out of it — Xfce4 plays well xith xRDP, that means I could easily open a remote desktop session to my Linux machine from any Windows machine, since remote desktop comes installed by default on Windows. For details on how I installed and configured xRDP, please see:

Customizing a DZ60 Keyboard using QMK

This is my 60% keyboard built on the DZ60 Rev 2.0 printed circuit board.

These are the steps I took to configure it for my use. Please note that I had configured this keyboard using Linux; Windows and Mac alternatives exist, but they are outside the scope of this article.

1. The DZ60 can be customized using Quantum Mechanical Keyboard Firmware. I downloaded a copy of it here:

2. I then unzipped the files to ~/software/qmk/

3. Run this shell script to ensure all dependencies are installed:

sudo ~/software/qmk/util/

4. Create a folder that will hold my custom keymapping file: ~/software/qmk/keyboards/dz60/keymaps/cpc1/keymap.c

I am utilizing 4 layers. Generally speaking:

Layer 0 is the main layer containing QWERTY and other such keys that I will use most often.

Layer 1 is activated by holding the key located beneath B. This layer contains F1-F12, basic navigation (ESDF for cursor movement, page up/down), volume control, and number pad (at right hand home row region).

Layer 2 is activated by holding the key located beneath NM.. This layer contains mouse navigation keys (ESDF for mouse movement, XCV as the three mouse buttons).

Layer 3 is activated by holding the key located beneath ENTER and to the right of UP. This layer contains keyboard hardware control keys (reset, LED controls).

This is my full keymap.c file:

#include "dz60.h"
#define __      KC_TRNS
#define ___     KC_TRNS
#define _______ KC_TRNS
#define HY      KC_HYPR
#define LST     KC_LSFT
#define SP_MO1  LT(1, KC_SPC)
#define BS_MO2  LT(2, KC_BSPC)
#define PN_MO3  LT(3, KC_PSCR)
const uint16_t PROGMEM keymaps[][MATRIX_ROWS][MATRIX_COLS] = {

	// Layer 0 - Standard keyboard
		KC_GESC, KC_1   , KC_2   , KC_3   , KC_4   , KC_5   , KC_6   , KC_7   , KC_8   , KC_9   , KC_0   , KC_MINS, KC_EQL , KC_DEL , KC_INS, 
		KC_TAB , KC_Q   , KC_W   , KC_E   , KC_R   , KC_T   , KC_Y   , KC_U   , KC_I   , KC_O   , KC_P   , KC_LBRC, KC_RBRC, KC_BSLS, 
		KC_ESC , KC_A   , KC_S   , KC_D   , KC_F   , KC_G   , KC_H   , KC_J   , KC_K   , KC_L   , KC_SCLN, KC_QUOT, KC_ENT , 
		HY, LST, KC_Z   , KC_X   , KC_C   , KC_V   , KC_B   , KC_N   , KC_M   , KC_COMM, KC_DOT , KC_SLSH, KC_RSFT, KC_UP  , PN_MO3 ,

	// Layer 1 - Cursor Navigation, Volume, Number Pad
		_______, KC_F1  , KC_F2  , KC_F3  , KC_F4  , KC_F5  , KC_F6  , KC_F7  , KC_F8  , KC_F9  , KC_F10 , KC_F11 , KC_F12 , _______, _______, 
		KC_TAB , KC_HOME, KC_PGUP, KC_UP  , KC_PGDN, _______, _______, KC_7   , KC_8   , KC_9   , KC_PPLS, KC_PMNS, _______, _______, 
		_______, KC_END , KC_LEFT, KC_DOWN, KC_RGHT, _______, _______, KC_4   , KC_5   , KC_6   , KC_PAST, KC_PSLS, KC_PENT, 
		HY, LST, KC_MUTE, KC_VOLD, KC_VOLU, _______, _______, _______, KC_1   , KC_2   , KC_3   , _______, _______, _______, _______, 
		KC_LCTL, KC_LGUI, KC_LALT, _______, _______, KC_0   , KC_PDOT, _______, _______, _______, _______),

	// Layer 2 - Mouse Navigation
		_______, _______, KC_ACL0, KC_ACL1, KC_ACL2, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		KC_TAB , _______, _______, KC_MS_U, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, _______, KC_MS_L, KC_MS_D, KC_MS_R, _______, _______, _______, _______, _______, _______, _______, _______, 
		HY, LST, _______, KC_BTN1, KC_BTN3, KC_BTN2, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		KC_LCTL, KC_LGUI, KC_LALT, _______, _______, _______, _______, _______, _______, _______, _______),

	// Layer 3 - Keyboard Hardware Controls
		RESET  , _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, RGB_TOG, RGB_MOD, RGB_HUI, RGB_HUD, RGB_SAI, RGB_SAD, RGB_VAI, RGB_VAD, _______, _______, _______, _______, 
		__, ___, BL_TOGG, BL_DEC , BL_INC , _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______),

5. Open a terminal window and navigate to ~/software/qmk/

6a. Run this command if you just want to create a hex file without actually flashing the keyboard:

sudo make dz60:cpc1

6b. Or, hold spacebar and B on the keyboard while plugging it into your computer; this will start the keyboard in bootloader mode. Now I can run the following command to compile the hex and then flash it onto the keyboard:

sudo make dz60:cpc1:dfu

6c. Or, if the keyboard is already plugged in and in use, run this command (same as 5b above). When the hex is built, it will prompt you to reset the keyboard so that the hex can be flashed onto the keyboard. In my case, my reset button is in Layer 3, as noted earlier.

sudo make dz60:cpc1:dfu

Additional info:

Full Documentation:
GitHub of QMK Project:

Scheduling Jobs with Oracle 9i DBMS_JOB Package

Creating a new job that runs every day at 4:00am:

	l_job number; 
		l_job, -- OUT; the job ID number that will be generated
		'schema_name.procedure_name;', -- IN; the name of the job you wish to run, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated

Regarding the interval, here are some examples:

-- Every 15 minutes starting from the minute/second of the previous execution

-- Every hour, same minute/second as the previous execution

-- Every hour, at the 15-minute mark
'trunc(sysdate, 'hh')+1/24+15/24/60'

-- Every hour, limited to between 9:00am and 5:00pm
'case when to_char(sysdate, ''hh24mi'') between ''0900'' and ''1700'' then sysdate+1/24 else null end'

-- Every 3 days, same hour/minute/second as the previous execution

-- Every day at 5:00am

-- Every Monday at 5:00am
'next_day(trunc(sysdate), ''monday'')+5/24'

To see a list of existing jobs:

select * from dba_jobs;

Altering all properties of an existing job:

		123, -- IN; job ID number
		'schema_name.procedure_name;', -- IN; the name of the job, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run after this change
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated

Altering just the “what”:

		123, -- IN; job ID number
		'schema_name.procedure_name;' -- IN; the name of the job, aka. "what"

These procedures allows you to make changes in a manner very similar to dbms_job.what illustrated above:


Force a job to run:

	-- ... where the "123" is the job's ID number

Removing an existing job:

	-- ... where the "123" is the job's ID number

Ubuntu Root Partition Full

The other day when I attempted to run some regular updates for my Linux box (running Ubuntu 14.04 LTS), I encountered the message that the update could no proceed because the root partition was full. Here are the steps I took to clear unneeded files from the root partition.

1. First, I found out that I am running kernel 3.19.0-65 with this command below.

me@computer:~$  uname -r


2. Next, list what kernel images are present in my root partition.

me@computer:~$ dpkg --list | grep linux-image

ii  linux-image-3.19.0-61-generic
3.19.0-61.69~14.04.1                                amd64        Linux
kernel image for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-3.19.0-65-generic
3.19.0-65.73~14.04.1                                amd64        Linux
kernel image for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-extra-3.19.0-61-generic
3.19.0-61.69~14.04.1                                amd64        Linux
kernel extra modules for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-extra-3.19.0-65-generic
3.19.0-65.73~14.04.1                                amd64        Linux
kernel extra modules for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-generic-lts-vivid               
                                       amd64        Generic Linux
kernel image

3. The above was actually a truncated example; the actual list was much longer. In summary, I had many older kernel images that I do not need anymore. In the example shown above, I decided that since I am running 3.19.0-65, I will not need the -61 image anymore. Below is the command I used to clear out -61; I ran similar commands for all the kernel images with even lower versions as well to clear up space.

me@computer:~$ sudo apt-get purge linux-image-3.19.0-61-generic

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
0 upgraded, 0 newly installed, 1 to remove and 8 not upgraded.
After this operation, 47.8 MB disk space will be freed.
Do you want to continue? [Y/n] Y

Bonus: Useful related commands

sudo apt-get clean
sudo apt-get update

The first empties /var/cache/apt/archives/ and /var/cache/apt/archives/partial/. The second updates apt-get’s list of available software packages.

Backing up Android Phone to Linux

My environment: Smart phone running Android version 6.0.1, and computer running Ubuntu 16.04 LTS

I have a few key folders on my phone that I would like to back up regularly. For simplicity sake, let us say that I am just dealing with the “Camera” folder which holds all my latest photographs. The following commands makes use of gvfs-commands (Gnome virtual file system) to copy/move files, assuming the phone has been connected via USB to the computer, and the phone has been unlocked.

The bash script I currently run looks like this:


for D in /run/user/1000/gvfs/*
	gvfs-copy ${D}/Phone/DCIM/Camera/*.* /picfolder/ 
	gvfs-move ${D}/Phone/DCIM/Camera/*.* /tmpfolder/
	gvfs-move /tmpfolder/*.* ${D}/Phone/Pictures/1-ToSort/ 

	echo "${D} done" 

As you may have noticed, I took the safe approach of copying the photos to my computer, then moving the same photos again to a temporary directory on my computer, and finally moving the temporary files back onto the phone in an archive folder. The final steps effectively represents a move from the Camera folder on the phone to the archive folder on the phone; this is because gvfs-move does not support the move of files within the same device at this time.

Combining Oracle trigger and DBMS_UTILITY.FORMAT_CALL_STACK to track transactions

I recently encountered a situation where a small number of records in a large Oracle table contain wrong values, and naturally I need to find out exactly which program is causing this problem. I decided to use Oracle triggers to do this job, making use of the built-in DBMS_UTILITY.FORMAT_CALL_STACK function as the main ingredient.

create or replace trigger trg_stack_trace_logger
before insert or update on inventory_table
for each row
	if (:old.expiration_date <> :new.expiration_date) then
		insert into stack_trace_log
		'User=' || user || '; ' ||
		'Date=' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') || '; ' ||
		'Old Value=' || :old.expiration_date || '; ' ||
		'New Value=' || :new.expiration_date || '; ' ||
	end if;

As you can see, the output contains both old/new values of the transaction as well as some metadata (ie. the stack trace) of the transaction itself. The output is inserted into a table called “stack_trace_log”, which, for simplicity sake, is just a table consisted of a single varchar2 field; if you will use this type of tracking over a longer period, it is probably best to track username, date, etc. in their own fields for better reporting capabilities.

select * from stack_trace_log;

Date=11/24/2015 08:33:39; 
Old Value=2015-11-15-00.00.00; 
New Value=2030-11-15-08.33.39; 
----- PL/SQL Call Stack -----    
object handle line number object name
0x91626018    1           anonymous block
0x8dcb7b30    3           ERP.TRG_STACK_TRACE_LOGGER
0x9657ec50    354         package body ERP.INVENTORY_API
0x9657ec50    1483        package body ERP.INVENTORY_API
0x8c7d2758    4254        package body ERP.INVENTORY_API
0x969315a0    650         package body ERP.RECEIVING_API
0x969315a0    3524        package body ERP.RECEIVING_API
0x969315a0    2861        package body ERP.RECEIVING_API
0x91411208    342         package body ERP.BARCODE_ARRIVAL_API
0x8bd5cca8    1           anonymous block
0x82871f48    1120        package body SYS.DBMS_SYS_SQL
0x82886f48    323         package body SYS.DBMS_SQL
0x99f8e6c0    138         package body ERP.BARCODE_INTERFACE_API
0x93980f88    1           anonymous block

1 rows selected

Insert Microsoft Word content into Oracle database

The following sample is over-simplified, but it shows how we can iterate through tables (and their columns and rows) to extract text, and in turn inserting them into an Oracle table.

Option Explicit

Public Sub InsertIntoOracle()
    Dim cn As ADODB.Connection
    Dim source, user, password, str As String
    Dim aTable As Table
    Dim tbl, row, col As Long

    source = "database"
    user = "scott"
    password = "tiger"
    tbl = 0

    Set cn = New ADODB.Connection
    cn.Open "Provider = OraOLEDB.Oracle; Data Source=" & source & "; User Id=" & user & "; Password=" & password & ""
    For Each aTable In ActiveDocument.Tables
        tbl = tbl + 1
        For row = 1 To aTable.Rows.Count
            For col = 1 To aTable.Columns.Count
                str = Trim(aTable.Cell(row, col).Range.Text)
                If (Len(str) > 2) Then
                    cn.Execute "insert into document_content values('" & tbl & "-" & row & "-" & col & ": " & str & "')"
                End If

    If cn.Errors.Count = 0 Then
    End If

End Sub