From 11a8440bc43aa9f7eb6f2cb7b7f43e0cf6680f41 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Mon, 8 Feb 2016 22:57:02 -0500
Subject: [PATCH] - Testing reveals that we have *no* weak references to any
 cx_Oracle connections at all, yet cx_Oracle still has open sessions that
 cannot be killed until process dies. Oracle wins!   Add a completely separate
 DB reaper script that runs after py.test is done.

---
 lib/sqlalchemy/testing/provision.py | 41 ++++++++++++++++++++++-------
 reap_oracle_dbs.py                  | 24 +++++++++++++++++
 tox.ini                             |  2 ++
 3 files changed, 58 insertions(+), 9 deletions(-)
 create mode 100644 reap_oracle_dbs.py

diff --git a/lib/sqlalchemy/testing/provision.py b/lib/sqlalchemy/testing/provision.py
index ba9754d900..6615eabaf5 100644
--- a/lib/sqlalchemy/testing/provision.py
+++ b/lib/sqlalchemy/testing/provision.py
@@ -5,6 +5,8 @@ from sqlalchemy.util import compat
 from . import config, engines
 import os
 import time
+import logging
+log = logging.getLogger(__name__)
 
 FOLLOWER_IDENT = None
 
@@ -255,18 +257,39 @@ def _oracle_configure_follower(config, ident):
     config.test_schema_2 = "%s_ts2" % ident
 
 
+def _ora_drop_ignore(conn, dbname):
+    try:
+        conn.execute("drop user %s cascade" % dbname)
+        log.info("Reaped db: %s" % dbname)
+        return True
+    except exc.DatabaseError as err:
+        log.warn("couldn't drop db: %s" % err)
+        return False
+
 @_drop_db.for_db("oracle")
 def _oracle_drop_db(cfg, eng, ident):
     with eng.connect() as conn:
-        for row in conn.execute(
-                "select sid, serial# from v$session "
-                "where username='%s'" % ident.upper()):
-            sid, serial = row
-            conn.execute("alter system disconnect session '%s, %s' immediate" % (sid, serial))
-            # conn.execute("alter system kill session '%s, %s'" % (sid, serial))
-        conn.execute("drop user %s cascade" % ident)
-        conn.execute("drop user %s_ts1 cascade" % ident)
-        conn.execute("drop user %s_ts2 cascade" % ident)
+        # cx_Oracle seems to occasionally leak open connections when a large
+        # suite it run, even if we confirm we have zero references to
+        # connection objects.
+        # while there is a "kill session" command in Oracle,
+        # it unfortunately does not release the connection sufficiently.
+        _ora_drop_ignore(conn, ident)
+        _ora_drop_ignore(conn, "%s_ts1" % ident)
+        _ora_drop_ignore(conn, "%s_ts2" % ident)
+
+def reap_oracle_dbs(eng):
+    log.info("Reaping Oracle dbs...")
+    with eng.connect() as conn:
+        to_reap = conn.execute(
+                 "select u.username from all_users u where username "
+                 "like 'TEST_%' and not exists (select username "
+                 "from v$session where username=u.username)")
+        dropped = 0
+        for total, (username, ) in enumerate(to_reap, 1):
+            if _ora_drop_ignore(conn, username):
+                dropped += 1
+        log.info("Dropped %d out of %d stale databases detected", dropped, total)
 
 
 @_follower_url_from_main.for_db("oracle")
diff --git a/reap_oracle_dbs.py b/reap_oracle_dbs.py
new file mode 100644
index 0000000000..ff638a01ed
--- /dev/null
+++ b/reap_oracle_dbs.py
@@ -0,0 +1,24 @@
+"""Drop Oracle databases that are left over from a 
+multiprocessing test run.
+
+Currently the cx_Oracle driver seems to sometimes not release a
+TCP connection even if close() is called, which prevents the provisioning
+system from dropping a database in-process.
+
+"""
+from sqlalchemy.testing.plugin import plugin_base
+from sqlalchemy.testing import engines
+from sqlalchemy.testing import provision
+import logging
+
+logging.basicConfig()
+logging.getLogger(provision.__name__).setLevel(logging.INFO)
+
+plugin_base.read_config()
+oracle = plugin_base.file_config.get('db', 'oracle')
+from sqlalchemy.testing import provision
+
+engine = engines.testing_engine(oracle, {})
+provision.reap_oracle_dbs(engine)
+
+
diff --git a/tox.ini b/tox.ini
index 83f551fadf..ef91f44859 100644
--- a/tox.ini
+++ b/tox.ini
@@ -60,6 +60,8 @@ passenv=ORACLE_HOME NLS_LANG
 commands=
   {nocext}: sh -c "rm -f lib/sqlalchemy/*.so"
   {env:BASECOMMAND} {env:SQLITE:} {env:POSTGRESQL:} {env:MYSQL:} {env:ORACLE:} {env:MSSQL:} {env:BACKENDONLY:} {env:COVERAGE:} {posargs}
+  {oracle}: python reap_oracle_dbs.py
+
 
 [testenv:pep8]
 deps=flake8
-- 
2.47.2