Old Database Sessions and some more ORDS

I spent some time on two totally unrelated things at the weekend.

Old Database Sessions

We’ve been having some problems with old database sessions recently. One of our reporting servers seems to grab loads of sessions and just not let go. In many cases they seem to remain active for a long time, while doing trivial tasks. My first thought was it was a problem with the database, but there doesn’t seem to be any evidence of that. On occasion we’ve restarted the reporting server and everything has been fine for a few days. I have no control over this reporting server, so I took the pragmatic approach of killing old sessions. Yes, I know it’s an ugly solution, but it works.

I was planning on using a profile to do this, but found there were several applications using the same credentials for different purposes (I inherited this, it’s not my fault OK. 🙂 ), so the profile approach was not going to cut it. Instead I wrote some code to identify the dodgy sessions and kill them, then called this from a job. Ugly, but it worked!

On Saturday I received a Facebook message asking me how to handle this exact same issue, so rather than answer directly I decided to write it up as an article.

I didn’t put it on the front page of the website because it is a back-fill article, rather than bringing anything new to the table.

New ORDS Article

After my recent UKOUG Tech16 session, where I talked about how *I* think you should use ORDS, I wanted to give a better example than the simple CRUD examples seen in most articles. One of my points was RESTful web services against Oracle should represent what Mark Farnham would call Logic Units of Work, rather than a CRUD interface over a table. That resulted in this article.

It includes processing the JSON payload with JSON_TABLE and the APEX_JSON package.