We had an issue in a development database where a group of developers were not using a connection manager, but trying to manage their own connections. This did not end well.<\/p>\n
To limit our exposure and the impact on other application users, we implemented a simple profile for the problematic user. A simple test case, where the names and values are changed, is shown below.<\/p>\n
\r\nSQL> create profile foo limit sessions_per_user 5;\r\n\r\nProfile created.\r\n\r\nSQL> alter user foo_user profile foo;\r\n\r\nUser altered.\r\n\r\nSQL> alter system set resource_limit = true;\r\n\r\nSystem altered.\r\n\r\nSQL> \r\n<\/pre>\nWe then use the python code below to test.<\/p>\n
\r\n#!\/home\/oracle\/local\/bin\/python\r\nimport cx_Oracle, time, sys, thread\r\nfrom threading import Thread\r\n\r\n#------------------------------------------------------------------\r\n\r\nclass getTxns(Thread):\r\n def __init__ (self):\r\n Thread.__init__(self)\r\n\r\n def run(self):\r\n con = cx_Oracle.connect(\"foo_user\",\"foo_user\")\r\n print \"got connection\"\r\n time.sleep(10)\r\n\r\n#------------------------------------------------------------------\r\n\r\nthreadList = []\r\n\r\nfor _ in range(7):\r\n current = getTxns()\r\n threadList.append(current)\r\n current.start()\r\n\r\n\r\nfor thisThread in threadList:\r\n thisThread.join()\r\nprint \"Finished\"\r\n<\/pre>\nThe output of a test where we try to open seven connections is shown below…<\/p>\n
\r\nemgrid01:oracle:emprod1:\/home\/oracle>.\/foo_user.py\r\ngot connection\r\ngot connection\r\ngot connection\r\ngot connection\r\ngot connection\r\nException in thread Thread-2:\r\nTraceback (most recent call last):\r\n File \"\/home\/oracle\/local\/lib\/python2.6\/threading.py\", line 525, in __bootstrap_inner\r\n self.run()\r\n File \".\/foo_user.py\", line 12, in run\r\n con = cx_Oracle.connect(\"foo_user\",\"foo_user\")\r\nDatabaseError: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit\r\n\r\n\r\nException in thread Thread-7:\r\nTraceback (most recent call last):\r\n File \"\/home\/oracle\/local\/lib\/python2.6\/threading.py\", line 525, in __bootstrap_inner\r\n self.run()\r\n File \".\/foo_user.py\", line 12, in run\r\n con = cx_Oracle.connect(\"foo_user\",\"foo_user\")\r\nDatabaseError: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"We had an issue in a development database where a group of developers were not using a connection manager, but trying to manage their own connections. This did not end well. To limit our exposure and the impact on other…<\/p>\n