This year’s Dutch Java Nerd event called J-Fall was held in Nijkerk, in a beautiful location called “Hart van Holland” . With plenty of sessions by speakers from all over the world it promised to be a great day for Java enthusiasts, at a great location for meeting friends and colleagues. I took a day off from work and it was well worth it.
At work, we have two applications which connect to the same database. For all kinds of business reasons, we need to make sure that only one of the applications accesses certain data at the same time. To do this, we use a row in a table as a semaphore.
While working on the locking mechanism, we had a closer look at the Transaction Management and the Isolation Levels we were using. There is a lot of good documentation on Transaction and Isolation, but it tends to be over complete, elaborate and therefore hard to read. I’ll try to share our insights with you in a slightly more digestable form (I hope).
If you’re a SQuirreL SQL fan like me, and you use a Mac, you probably also want to know how to add a driver to the OSX version of the application. Most people just point to an external jar file, but you can also add drivers to the application bundle. There’s not much to it, but it took me some time to discover that I actually had to edit the plist file in the bundle after adding the driver.
As you can see on the left, my SQuireLSQL.app now comes with the Oracle OCI and Thin driver built in, and because they are part of the application bundle, I can’t wreck my installation by misplacing jarfiles or reconfiguring classpaths. Nice.
If you want to drivers to your SQuireLSQL.app yourself, here’s what you need to do:
Read the rest of this entry »
At work, we’ve got two Oracle databases and a Java web application. One of the tasks of the web application is to copy tables over from one database to the other. Last week we ran into a funny problem which turns out to be an Oracle bug.
I’ve posted a question about this last week on stackoverflow.com but it seems that not many people are affected by this particular problem, or at least not many people know the answer to this particular little problem. We set out to tackle this and we did.
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || '''; --', s.sid, s.serial#, s.osuser, s.username, s.program, status FROM v$session s WHERE status = 'INACTIVE';
You will get a list of statements you need to execute (just copy-pase) to kill the inactive sessions. Don’t listen to the guys telling you to do intricate System Administrator stuff on a command prompt, just use any JDBC tool.
EDIT: Single query which also lists the kill command if the “alter system kill session” trick did not work:
SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || '''; -- kill -9 ' || p.spid, s.sid, s.serial#, p.spid, s.username, s.program, s.status FROM v$session s, v$process p WHERE s.paddr = p.addr and (s.state='INACTIVE' or s.state='KILLED');
Thanks Bas en Jeroen!