Import/export an Oracle Schema using JDBC

Friday 2015-01-02

Everybody gets a database!When doing integration testing or fixing a bug in a piece of Java code that uses Oracle as a database, being able to do quick exports and imports of your schema can be a big help. Sometimes just calling Oracle’s imp/exp commandline tools from your code can be of help, but I was looking for something a bit more portable and less demanding on my local development machine. I found that Oracle’s datapump functionality can be called from stored procedures, which in turn can be called from a normal JDBC statement.

Read the rest of this entry »


Anonimatron: Quick Start

Sunday 2013-11-03

Anonymous customerAfter reading my last blogpost on Anonimatron, you must have asked yourself “Great, but how do I actually use Anonimatron to de-personalize my database”? I tried my best to make basic Anonimatron configuration as self-explanatory as possible, just start it without any command line arguments and it will tell you.

Less adventurous or in a big hurry? This blogpost will show how simple it is to install and configure Anonimatron on an example MySQL database.

Read the rest of this entry »


Having fun at J-Fall 2011

Thursday 2011-11-03

J-Fall 2011This 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.

Read the rest of this entry »


Transactions and Isolation levels

Saturday 2009-08-29

Safety googlesAt 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).

Read the rest of this entry »


How to add drivers to SQuirreLSQL.app

Tuesday 2008-12-30

SQuireL SQL on OSX with Oracle driversIf 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 »


Transporting Oracle CHARs over a dblink

Tuesday 2008-11-04

Oracle LogoAt 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.

Read the rest of this entry »


Killing oracle sessions, the easy (JDBC) way.

Thursday 2006-02-23
Does Oracle complain about not being able to drop a table for a currently connected user, but you are sure you disconnected? Do the sessions “hang” in “inactive” state? Just log on as system, and execute the following query:¬†

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!