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.
Tag: oracle
Anonimatron: Quick Start
After 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.
Having fun at J-Fall 2011
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.
Transactions and Isolation levels
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).
How to add drivers to SQuirreLSQL.app
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:
Transporting Oracle CHARs over a dblink
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.
Killing oracle sessions, the easy (JDBC) way.
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!