The Oracle DBA and Ruby on Rails #oracle #dba #rubyonrails

February 7, 2011

Over time, most Oracle DBAs build up a library of scripts for day to day tasks. In the old days, when we had maybe 5 databases to manage, we used sqlplus and some shell scripting and we had our library of sql and plsql scripts to run individually on each database with shell scripting.

Now I have about 40 databases to manage and it just doesn’t scale. The idea of logging in 40 times to execute the same script isn’t appealing and then there is the problem of aggregating all the output.

I need a scripting environment that can handle multiple connections. There are several good scripting languages but I use Ruby because I like the syntax, it has excellent connection handling, and and straightforward access to the Win32 API. It’s easy to run some SQL across 40 databases and aggregate the output into Excel.

Ruby also has Rails. Dealing with a lot of databases requires metadata.The most obvious place to store metadata about databases is a database and Rails give me a data model and an app for little effort. I can use the migrations to easily manage the metadata and port it around as needed too.

That said, I’ve always been fascinated by the Lisp family of languages, I might try using Clojure some day.

And a note to Bryn Llewellyn at Oracle. If you would give me PLSQL that could run connection-less in the client, I would use that for some cases. Obviously it would only support anonymous PLSQL blocks but I could live with that. Nothing works better with Oracle than PLSQL but it is limited to a connection, and that won’t do anymore.