What I Learned Today

Extract my data from runtastic and upload to strava

2015-10-16T11:25:00.000-05:00

Background

So I started using Strava a few months ago after one of the super nice employees at Sunlight Bike Shop in Glenwood Springs, CO showed me some local routes on his phone (he also owned a big dummy). He was using Strava and it looked really nice.

I had been having some problems with Runtastic RoadBike Pro, where it was slowing down every time I saved a new activity. It was so bad that it was taking 2 minutes to save anything, so I was already looking for a better solution.

I really like Strava and have been looking for ways to get the past 3 years of my data out of Runtastic, with limited results. This was the easiest way I could figure out how to do this. I did see some JS scripts and here to get the files out of the runtastic UI, but I didn't want to click the "OK" button to download 275 TCX files.

Disclaimer and high level overview

This is not for non-technical folk, you will be extracting an encrypted backup from your phone, unencrypting it, finding the sqlite DB, getting some data out of the DB, extracting your session cookie from runtastic after logging in with your browser, getting the files using curl, slicing them up into directories with 25 or less TCX files, then uploading them to Strava.

Overall you should expect to spend 1-2 hours, but the end result is all of your data will be in one place!

My setup is as follows

Nexus 6 with the Runtastic Roadbike APP installed, and all of the activities downloaded
Ubuntu laptop running 15.10 (I'm sure any recent version is fine, or any other linux or Mac)
adb installed (sudo apt-get install android-tools-adb)
ant installed (sudo apt-get install ant)
sqlite installed (sudo apt-get install sqlite)
openjdk 8 installed (sudo apt-get install openjdk-8-jdk)
USB cable
Developer options turned on
USB debugging enabled
Authorized my computer to USB debug my phone

Instructions

Here are some (mostly) step by step instructions, although this does assume that you have a few things installed (or can install them) and are running on a linux machine with a USB cable to your phone.

Extract the DB from your phone, my phone required setting a password.

 adb backup -f runtastic-data.ab -noapk 'com.runtastic.android.roadbike.pro'

Unencrypt the backup

I used https://github.com/nelenkov/android-backup-extractor for this. The ant build was the easiest, and the openJDK 8 already had the encryption libs that are referenced. Just edit the build.xml file, change it to the version you have and type 'ant'.

 ABE_PASSWD=<your password> java -jar abe.jar unpack ../runtastic-data.ab ../runtastic-data.tar

Get all of your IDs from the runtastic DB from your phone

 sqlite3 runtastic.sqlite 'select serverSessionID from session where serverSessionID <> -1 order by startTime' > runtastic-ids.txt

Login to runtastic

Pull your cookie from a request to their site. Put it in an ENV variable for the download script.

 export RT_COOKIE='Cookie:<the whole thing pulled from a GET request'

Get your username and set that in another ENV variable. Your username is in the URL right after '/users/'. Mine was "Torleiv-Flatebo"

 export RT_NAME="Torleiv-Flatebo"

Runtastic Download Script

Save the below into a file called runtastic-download-script.sh

 curl -s -w "%{filename_effective} %{http_code}\n" -O -J "https://www.runtastic.com/en/users/$RT_NAME/sport-sessions/$1.tcx" -H "$RT_COOKIE"

The magic happens

Cat out the IDs and run the download script, watch as each file is downloaded. It took about 5-10 minutes to get all of my 275 activities.

 mkdir tcx
 cd tcx
 cat ../runtastic-ids.txt | xargs -n 1 sh ../runtastic-download-script.sh   
 runtastic_20120628_1755_Race Cycling.tcx 200  
 runtastic_20120630_1106_Race Cycling.tcx 200

Prep files for upload to Strava (optional if more than 25 activities)

 mkdir 1 2 3 4 5 6 7 8 9 10 11  
 ls -1 *.tcx | head -25 | sed 's/.*/"&"/' | xargs -n1 mv -t 1  
 ls -1 *.tcx | head -25 | sed 's/.*/"&"/' | xargs -n1 mv -t 2  
 etc.

Upload to Strava

Now you have all of the tcx files, you can upload them to strava using their website. You are limited to 25 files per upload, so I just put them all in separate dirs (see above).

I only had about 275 tcx files, so I figured it would be easier to use their UI, since you can tag and edit the activities in a screen after you select 25 of the files. For me I like to tag the activities to be a commute, or pick the bike that I am using for that ride, so their UI was preferable.

If you have more activities from runtastic you can use their API to upload.

Resources

1 http://blog.shvetsov.com/2013/02/access-android-app-data-without-root.html
2 http://nelenkov.blogspot.jp/2012/06/unpacking-android-backups.html
3 https://github.com/nelenkov/android-backup-extractor

Cassandra: 'Segmentation fault: 11' when trying to run any command in cqlsh

2014-03-02T09:32:00.001-06:00

Trying to get cassandra running on my Mavericks laptop, I ran into an issue right away. Other posts mentioned JVM versions, but I upgraded that and made sure my brew stuff was all up to date.

There were no errors at all in the cassandra server logs, so I figured it must be something with Python, since cqlsh was running inside of Python.

Any time I would run a command (other than help) in cqlsh, I would get a "Segmentation fault: 11", with this in the details:

Process:         Python [35348]
Path:            /Library/Frameworks/Python.framework/Versions/2.7/Resources/Python.app/Contents/MacOS/Python
Identifier:      Python
Version:         2.7.3 (2.7.3)
<snip>
Crashed Thread:  0  Dispatch queue: com.apple.main-thread

Exception Type:  EXC_BAD_ACCESS (SIGSEGV)
Exception Codes: KERN_INVALID_ADDRESS at 0x0000000000000000

This turned out to be a bug in Python < 2.7.6. An upgrade to Python 2.7.6 solved the issue.

A note on Python's site referred to

Python 2.7.6 was released on November 10, 2013. This is a 2.7 series bugfix release. Most importantly, it resolves an issue that caused the interactive prompt to crash on OS X 10.9. It also includes numerous bugfixes over 2.7.5.

Monthly hackathon or unconference in Mpls or St. Paul

2013-11-09T10:28:00.001-06:00

I want to hear from people who are interested in doing the City Camp MN and Open Twin Cities idea more often primarily in a hackathon format.

Doesn't have to be amazing or awesome every month, just a group of creative and interested and civic minded people getting together once per month or so.

I can help to organize and find space for this.

Especially interested in having non-coders attend, developers aren't the best with idea creation and collaboration between non-technical and techies is always the best.

I know there are people looking for projects to code on, and I know there are people looking for help moving their ideas forward.

Post in the comments if you are interested in this and I will contact you or post on this blog as this moves forward.

#hackmonthlymn

My favorite XML editor and viewer for linux

2012-06-05T10:00:00.000-05:00

mlview

http://packages.ubuntu.com/hardy/mlview

I always forget this because I don't use it enough. It's pretty fast, but can be a little slow on very large documents.

Increase memory size for SQLDeveloper

2011-12-23T12:23:00.000-06:00

For some reason, they make it very difficult to increase the memory size of SQLDeveloper, you can't even pass in the options on the command line.

I'm not sure about Mac or Windows, but on Linux, it's pretty easy, just edit your ide/bin/ide.conf file and change the lines "AddVMOption -Xmx640M" and "AddVMOption -Xms128M" lines to something larger (or smaller). You can see what I have mine set to below.

Here is mine after I changed it:

tor@XXX:~/sqldeveloper$ pwd
/home/tor/sqldeveloper
tor@XXX:~/sqldeveloper$ cat ./ide/bin/ide.conf
#-----------------------------------------------------------------------------
#
# Oracle IDE Configuration File
# Copyright 2000-2008 Oracle Corporation.
# All Rights Reserved.
#
#-----------------------------------------------------------------------------
IncludeConfFile ../../ide/bin/jdk.conf
AddJavaLibFile ../../ide/lib/ide-boot.jar
SetMainClass oracle.ide.boot.Launcher
#
# If you are getting the 'Low Memory Warning' Message Dialog while running
# JDeveloper, please increase the -Xmx value below from the default 768M to
# something greater, like 1024M or 1250M. If after increasing the value,
# JDeveloper is no longer starting up because it fails to create a virtual
# machine, then please reduce the modified -Xmx value.
#
AddVMOption -Xmx2048M
AddVMOption -Xms512M
#
# Turn off verifications since the included classes are already verified
# by the compiler. This will reduce startup time significantly. On
# some Linux Systems, using -Xverify:none will cause a SIGABRT, if you
# get this, try removing this option.
#
AddVMOption -Xverify:none
#
# On some Windows Terminal Server installations, relocation errors of
# system DLLs can sometimes occur when using OJVM. Use this option to
# specify a heap base address for OJVM to resolve this problem. This
# option only works with OJVM and cannot be used with any HotSpot JVM.
#
# AddVMOption -Xheapbase100000000

Time Machine on Mac OS 10.6.8 failing backing up to an Ubuntu netatalk server with "(Error) IndexSDB in si_writeBackDBO:Error 2 storing dbo"

2011-10-30T09:00:00.005-05:00

I ran into some problems about four months ago with being able to back up my 10.6 Mac to an Ubuntu netatalk server. I just started failing, with a generic error message on the UI. All other netatalk services and shares were working fine.

It was getting to the point where it looked like it was working, but once it actually began backing up, it would get to about 100kb, and then fail with this message in the system log on the Mac:

Oct 29 16:37:48 my-macbook mds[38]:
(/Volumes/Backup of MyMac/.Spotlight-V100/Store-V1/Stores/XXX-XXX-XXX-XXX-XXX)
(Error) IndexSDB in si_writeBackDBO:Error 2 storing dbo(9770a3,430,182,4b076d10a69e9,0,1d1686,0)

I was able to follow the instructions copied below from a comment on this page:

10.6: Set up Time Machine on networked AFP volume

Obviously I didn't follow them exactly, since I have an Ubuntu server and not a Mac OSX server, but it now turns out that my Mac now creates the sparsebundle file for me! I basically deleted the whole folder (sparsebundle that I had created for the previous version of Mac OS) that I was using for the netatalk share, created an empty one with 777 perms, added the .com.apple.timemachine.supported file, and my Mac did the rest!

Note: The backup did fail once or twice after following these instructions, the third time it started working all by itself, all I did was retry.

10.6: Set up Time Machine on networked AFP volume
Authored by: Henk Poley on Feb 27, '11 01:52:53AM

Maybe I'm doing something wrong.. but the only thing I have to do is:

1. Create an AFP share on an OS X machine (set permissions, user accounts & passwords as you like)
2. Run `touch .com.apple.timemachine.supported` from Terminal.app on that share
3. Mount the AFP share by clicking around in Finder
4. Point Time Machine to the disk

It then goes off to create a .sparsebundle, mounts that and does a backup.

I may have run the magic `defaults write com.apple.systempreferences TMShowUnsupportedNetworkVolumes 1` way back when, so you may have to run that too.

Both machines run 10.6.6, but I (maybe miss-)remember doing it like that on Leopard too.

(Before anyone says this doesn't work anymore, I'm doing an initial backup using that method right now)

Edited on Feb 27, '11 01:59:53AM by Henk Poley

SQuirreL and Oracle ORA-01867

2010-08-10T13:27:00.000-05:00

Global Preference (v3.1)

I have had a specific error plaguing me for some time now, when using the SQuirreL SQL client to access an Oracle Scheme with a database link to another Scheme. The query might look something like this:

SELECT * FROM SOMETABLE@OTHERSCHEME

I would consistently get an ORA-01867 error. Every forum I searched seemed to point back to DATE or TIMESTAMP handling. Playing with the Global Preferences, I found that the DATE handling option titled Interpret DATE columns as TIMESTAMP must be un-selected. -- Chad

How to set chrome as your default browser for Thunderbird on Ubuntu

2010-06-02T10:22:00.003-05:00

From this bug report:

it appears that in Thunderbird 3 the protocol handlers are ignored in favor of the
settings in the "Attachment" preferences. Indeed, once I set the http and https
"content types" to xdg-open, Thunderbird correctly opened http and https links in
Chrome.

Export from iMovie to play on Blackberry Bold 9700

2010-03-14T20:28:00.017-05:00

Want to export a video from iMovie to play on your Blackberry Bold 9700? Tired of showing your videos on the youtube player? No problem!

For the impatient: use MPEG4, 25fps, auto key frames and restrict to 1248 kbits/sec at 480x270 letterbox. Audio is AAC codec, Mono, 44.1, and a target bitrate of 64k.

A 3:30 video renders to about 33MB.

First choose 'Export using Quicktime' from the menu

Then you will see a dialog like this. Choose options

After that you should see the setting for the video formatting and audio. Choose the top 'Settings' button.

Now you will get a dialog for the video format. Use MPEG4, 25fps, auto key frames and restrict to 1248 kbits/sec. Now close this dialog, and hit the 'size' button.

Choose a custom size, and enter 480x270, preserve aspect using letterbox, and deinterlace. Close this dialog, and choose the sound settings button.

Use an AAC codec, Mono, 44.1, and a target bitrate of 64k. Close this dialog.

Now give your video a name, and hit save

Squirrel not starting fix

2010-02-11T11:51:00.004-06:00

If OOM kills squirrel hard, and squirrel will not restart giving a Null Pointer error, you can follow this thread below by removing the SQLAliases23_treeStructure.xml file from your $HOME/.squirrel dir. It is a transient file.

Stack trace:


tor@unwise:~/SQuirreL SQL Client$ ./squirrel-sql.sh
Exception in thread "main" java.lang.reflect.InvocationTargetException
    at java.awt.EventQueue.invokeAndWait(EventQueue.java:997)
    at javax.swing.SwingUtilities.invokeAndWait(SwingUtilities.java:1323)
    at net.sourceforge.squirrel_sql.fw.gui.GUIUtils.processOnSwingEventThread(GUIUtils.java:350)
    at net.sourceforge.squirrel_sql.client.gui.WindowManager.(WindowManager.java:161)
    at net.sourceforge.squirrel_sql.client.Application.executeStartupTasks(Application.java:753)
    at net.sourceforge.squirrel_sql.client.Application.startup(Application.java:246)
    at net.sourceforge.squirrel_sql.client.Main.main(Main.java:60)
Caused by: java.lang.RuntimeException: java.lang.NullPointerException
    at net.sourceforge.squirrel_sql.client.gui.db.JTreeAliasesListImpl.initTree(JTreeAliasesListImpl.java:233)
    at net.sourceforge.squirrel_sql.client.gui.db.JTreeAliasesListImpl.(JTreeAliasesListImpl.java:101)
    at net.sourceforge.squirrel_sql.client.gui.db.AliasesList.(AliasesList.java:44)
    at net.sourceforge.squirrel_sql.client.gui.WindowManager.createAliasesListUI(WindowManager.java:877)
    at net.sourceforge.squirrel_sql.client.gui.WindowManager.initialize(WindowManager.java:861)
    at net.sourceforge.squirrel_sql.client.gui.WindowManager.access$200(WindowManager.java:77)
    at net.sourceforge.squirrel_sql.client.gui.WindowManager$1.run(WindowManager.java:165)
    at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:199)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:597)
    at net.sourceforge.squirrel_sql.client.Application$1.dispatchEvent(Application.java:195)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.lang.NullPointerException
    at net.sourceforge.squirrel_sql.client.gui.db.AliasFolderState.applyExpansionAndSelectionToNode(AliasFolderState.java:144)
    at net.sourceforge.squirrel_sql.client.gui.db.AliasFolderState.applyExpansionAndSelection(AliasFolderState.java:160)
    at net.sourceforge.squirrel_sql.client.gui.db.JTreeAliasesListImpl.applyAliasFolderState(JTreeAliasesListImpl.java:264)
    at net.sourceforge.squirrel_sql.client.gui.db.JTreeAliasesListImpl.initTree(JTreeAliasesListImpl.java:220)
    ... 15 more

Test ldap connection against Active Directory

2009-12-01T15:07:00.002-06:00

How to test a connection to AD using linux ldapsearch


ldapsearch -LLL -x -H 'ldap://server.com:389' -b 'DC=domain,DC=office,DC=com' -D 'CN=openfire,OU=Service Accounts,OU=User Accounts,OU=DOMAIN,DC=domain,DC=office,DC=com' -W '(sAMAccountName=tor)'

This will make sure that your user can bind, and the in the search they can see user accounts.

We used this for ldap integration for authentication for JIRA and openfire.

Locking the keyboard on a Blackberry Bold

2009-05-30T09:17:00.003-05:00

Because I never read the manual, I was having problems locking the keyboard on my new Blackberry Bold. First, you have to turn off the language switching in the menu to be able to use the old 'Alt-Enter' shortcut for locking the keyboard.

Thing is, with the new trackball, that key combination is a little awkward, and I am so paranoid about dropping the dang thing, that I have been using the menu option for locking.

Until now.

Jeez, the manual for this thing is literally hundreds of pages, and to be honest, I've never even looked at it. I mean, seriously, why wouldn't I already know everything about using a Blackberry?

So, two key shortcuts I learned today are;

hold down '*', this will lock the keyboard
hold down '#', this will toggle between vibrate and normal profiles

Sweet.

ruby script to search for email addresses in a log file

2009-04-27T11:40:00.003-05:00

Given input that is ":" delimited, this script will search out email addresses in any field in the input:


cat log_file.txt | ruby -ne 'split(":").each { |col| print col , "\n" if col.match(/^\S+@\S+$/) }' | sort | uniq

awk script to add up lines in a file

2009-04-22T16:25:00.002-05:00

You want to add up the numbers in a file?

Input:

Script:


cat file | awk '{ total = total + $1} END { print total}'

Getting jruby running with mysql on ubuntu

2008-09-23T22:44:00.004-05:00

You want to get jruby running quickly on ubuntu using mysql as a backing store?

Unfortunately, it does not "just work" with sqlite3 out of the box on ubuntu for some reason. I don't really care why, as mysql is very easy to setup. The most important step that flexed my googling muscles was setting the database adapter to jdbcmysql in the database.yml file.

I will post later on a howto to get this working with glassfish and war files. For some reason, warbler nor goldspike "just work" on ubuntu either.


# install mysql
sudo apt-get install mysql-server mysql-client libhtml-template-perl mailx dbishell libcompress-zlib-perl mysql-doc-5.0 tinyca

# install ant and jdk
sudo apt-get install ant sun-java6-jdk

# install jruby
cd software
wget http://dist.codehaus.org/jruby/jruby-bin-1.1.4.tar.gz
tar xvfz jruby-bin-1.1.4.tar.gz
ln -s jruby-1.1.4 jruby
export PATH=$PATH:$HOME/software/jruby/bin

# install the version of rails wanted by jruby
jruby -S gem install jruby-openssl
jruby -S gem install rails

# list your gems
jruby -S gem list

# install the gems needed for db
jruby -S gem install activerecord-jdbc-adapter activerecord-jdbcmysql-adapter

# generate some code
cd ~/scripts/ruby
jruby -S rails wherehaveyoubeen -d mysql
cd wherehaveyoubeen
jruby script/generate controller states index

# configure access to the database server
vi config/database.yml

#development:
#  adapter: jdbcmysql <- very important!
#  encoding: utf8
#  database: wherehaveyoubeen_development
#  username: root
#  password: database
#  socket: /var/run/mysqld/mysqld.sock

# generate the db
jruby -S rake db:create:all
jruby -S rake db:migrate


# edit your app
sudo apt-get install emacs ruby-elisp irb1.8 emacs22-el
emacs -bg black -fg wheat app/views/states/index.html.erb

# uncomment the secret in app/controllers/application.rb
vi app/controllers/application.rb

# run your app
jruby script/server

# go to your app
GET http://0.0.0.0:3000/states

Adding subversion to eclipse on linux (ubuntu)

2008-09-22T09:37:00.002-05:00

You want to use subversion in eclipse? You will need a javahl implementation.

Ubuntu has a javahl package:


sudo apt-get install libsvn-java

Then add this to your eclipse.ini:


-Djava.library.path=/usr/lib/jni

Simple shell for loop

2008-08-04T11:56:00.003-05:00

Simple for loop in shell to run a command n times.


for i in `seq 1 10`; do your_command_here.sh; done;

Find a class in a lot of JAR files

2008-07-27T13:58:00.002-05:00

Where did that class come from? Which JAR do I need to import to resolve this compilation error? Arrgh.


find ./ -type f -name "*.jar" -print -exec jar tvf {} \; | less

Example Java code for posting to blogger.com using labels/tags

2008-07-10T19:31:00.004-05:00

So I really can't believe how long it took to get this right, but here is some example code for posting to blogger.com with the ability to add some tags or categories or labels.

You just try searching for some code example on how to "post to a blog using tags". Jeez, it took about 45 minutes of searching to find an actual post that WASN'T a blog post that had a tag.

public class GoogleBlogPostTest extends TestCase 
{
public void testBlogPost() throws Exception
{
GoogleService myService = new GoogleService("blogger", "company-xampleApp-1");
myService.setUserCredentials("googleusername", "googlepassword");

String title = "This is my title " + (new Date()).toString();
String blogID = "blognumber";
String contentStr = "This is a test of the emergency blogging system";
String authorName = "Test Author";
String userName = "author@email.com";
String summary = "This is the summary";

ArrayList tags = new ArrayList();
tags.add("News");
tags.add("Alerts");

Entry myEntry = new Entry();

for(String tag : tags)
{
Category category = new Category();
category.setScheme("http://www.blogger.com/atom/ns#");
category.setTerm(tag);
myEntry.getCategories().add(category);
}

Content content = new TextContent(new PlainTextConstruct(contentStr));
myEntry.setTitle(new PlainTextConstruct(title));
myEntry.setContent(content);
Person author = new Person(authorName, "http://flatebo.org", userName);
myEntry.getAuthors().add(author);
myEntry.setSummary(new PlainTextConstruct(summary));

URL postUrl = new URL("http://www.blogger.com/feeds/" + blogID + "/posts/default");

Entry newEntry = myService.insert(postUrl, myEntry);

assertEquals(title, newEntry.getTitle().getPlainText());
}
}

Using microsoft VPN on ubuntu hardy in KDE

2008-06-03T10:52:00.005-05:00

In the newest ubuntu, there is a package for kde to use network-manager in the tray to connect to microsoft VPN (PPTP).

sudo apt-get install network-manager-pptp network-manager-vpnc \
 network-manager-openvpn network-manager-kde

Then just use the network manager app in the tray to define your VPN connections. When you are ready to connect, just right click and connect.

How to reset IIS

2008-06-03T08:57:00.003-05:00

c:\> iisreset

This also resets the exchange services on a Small Business Server install.

How to unzip 'encrypted' zip files in linux

2008-06-02T09:49:00.003-05:00

sudo apt-get install p7zip-full p7zip p7zip-rar

bash$ 7z e filename.zip

(it will prompt you for the password)

Managing IMAP accounts on Sprint blackberry devices

2008-05-30T15:49:00.003-05:00

I forgot how to add an IMAP email account to my Sprint Blackberry 8830. I found the direct line to Sprint's blackberry support:

1-877-654-9111

He was able to tell me my username and reset my password. What I really needed was this web address:

http://sprint.blackberry.com

which is where you manage your email accounts.

Actually getting lpr to work with cups

2008-05-30T09:32:00.005-05:00

Yes, it should 'just work', but it doesn't. After installing cupsys-bsd to provide lpr, you also have to set the printer's name in your environment.

After setting up the printer in cups, list out the printer's names like this:


bash$ lpadmin -p
printer PDF is idle.  enabled since Fri 25 Apr 2008 02:37:51 PM CDT
printer Savin is idle.  enabled since Tue 20 Nov 2007 06:03:36 PM CST

Then pick the one you want and set the environment variable PRINTER:


export PRINTER=Savin

Now you can use lpq:


bash$ lpq
Savin is ready
no entries

As a follow up, you can also use the '-P' switch like this:

lpq -P Savin
Savin is ready
no entries

Oracle open transactions

2008-05-29T18:24:00.007-05:00

Here is another neat Oracle query you can run that will give you any open transactions:


SELECT
   s.osuser, vp.spid as os_pid, S.BLOCKING_SESSION blocker,
   S.SID, S.SERIAL#, S.USERNAME, S.MACHINE,
   Q.SQL_FULLTEXT cur_sql, PQ.SQL_FULLTEXT prev_sql, 
   vt.used_urec, vt.start_date
FROM 
   v$session S
   LEFT JOIN v$sqlarea Q on S.SQL_ID = Q.SQL_ID
   LEFT JOIN v$sqlarea PQ on S.PREV_SQL_ID = PQ.SQL_ID
   LEFT JOIN v$process vp on s.paddr = vp.addr
   LEFT JOIN v$transaction vt on s.saddr = vt.ses_addr
WHERE
   vt.start_date < SYSDATE - (5/1440)
--   AND
--   s.machine = 'machine.name'
ORDER BY 
   S.SID
;

With this query, you can track down any open transactions to their Oracle subprocess PID on the operating system. Any transaction that has been running for longer than five minutes will show up with this query.

Now if you wanted to create an alert (I love alerts) that will tell you when you have an open transaction for more than five minutes:


select
   LTRIM(COUNT(1))
from
   v$transaction
where
   start_date < SYSDATE - (5/1440)
;

Thanks to Ted for this query.