search

OraOpenSource / Logger

Logger is used by Oracle developers to instrument their PL/SQL code
http://www.oraopensource.com/logger/
MIT License
310 stars 118 forks source link

Error when using logger_configure creates zombie session #156

Closed bcarter closed 8 years ago

bcarter commented 8 years ago

I have been building a "how to use logger' presentation. Towards the end I demonstrate how plug ins work. When I added this part to the presentation I started getting an error and zombie processes.

There are about 45 minutes of other things I was doing before I get to the error, but I have narrowed it down to the below steps to reproduce it.

I have reproduced this on a Cloud DB and a VirtualBox DB both using Oracle Linux on a 12c DB.

  1. Connect to the DB as SYS, using SQLDeveloper (I have not tried this with SLQPlus.)
  2. Run create_user.sql (user name DD_LOGGER other values default)
  3. Connected as DD_LOGGER I run logger_instal.sql
  4. Connected as DD_LOGGER I run exec logger_configure; which throws an ORA-00600 error.
  5. At this point the DD_LOGGER session can't do anything so I reconnect.
  6. Connected as DD_LOGGER I run exec logger_configure; again and this time it succeeds.

At this point the original DD_LOGGER session still exists with the same SID but a SERIAL# of 0 with the new session also up. Trying to kill the old session from inside the database errors saying the session doesn't exist but trying to drop DD_LOGGER fails saying it does exist.

The only way to kill the session is to get its spid and kill the linux process at the os level.

martindsouza commented 8 years ago

@bcarter I've seen this happen as well where you can't run logger_configure twice in the same session. It could be (but not necessarily) because of conditional compilation.

bcarter commented 8 years ago

I thought about that. When I ran my last presentation, I did a reconnect (in sqldev) just before running it for that part, I still got the error. I'll try it with a full disconnect and then connect, if that doesn't work, I'll keep digging.

bcarter commented 8 years ago

I have verified that it does not happen if I disconnect then connect the session before running logger_configure the second time. I also left the zombie session alive and it stayed alive for days.

Using reconnect in sqldev does not stop the problem, it has to be a full disconnect then connect again.

martindsouza commented 8 years ago

@bcarter Are you ok if we tag this as wontfix. I think it's a DB thing caused when you do conditional compilation twice on a package in the same session. I'm not sure we can fix it with a code change.

bcarter commented 8 years ago

Makes sense to me. If I can determine that it's fixable, I'll re-submit.