set role

Trying to get autotrace out of SQL Developer and getting error message.

Failed to access V$MYSTAT.

Please obtain read catalog privilege

from your database administrator:

grant SELECT_CATALOG_ROLE to RAFU

grant SELECT ANY DICTIONARY toRAFU

NOTE: you need to reconnect your current session

in order for the settings change to have an effect

Granted the privileges. I guess less would be enough. Weird note. Why do I need to reconnect the session? There exists SET ROLE command.

SET ROLE ALL;

That does the job. No reconnect needed.

Crabby code

ORA-00020: maximum number of processes 500 exceeded

Connections are using DEDICATED connections. The pooling is done in the middle tier. Several pools. Connection leakage is not a database fault but it becomes to a everybody problem. While figuring out the problem, I managed to produce a following 10046 SQL trace.

ERROR #1:err=12899 tim=1312894876206660

WAIT #1: nam='SQL*Net break/reset to client' ela= 20 driver id=675562835 break?=1 p3=0 obj#=-1 tim=1312894876206740

WAIT #1: nam='SQL*Net break/reset to client' ela= 303 driver id=675562835 break?=0 p3=0 obj#=-1 tim=1312894876207059

WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876207098

WAIT #1: nam='SQL*Net message from client' ela= 247133 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876454273

Using the Oracle friendly search.

Talking about SQL*Net break/reset to client events Tanel Poder mentions these breaks are caused by bad application design. This is something I was trying to find out, but he is talking about MERGE statements. My trace included only inserts.

In the Xtrace manual it is mentioned "err" being equal to 12899 (ORA-12899 is "value too large for column"): That makes sense.

Luckily I had taken the trace with binds. And could point out the actual column causing the root problem. Only the first 255 characters of the bind values seems to be populated to the trace. The problem column was larger in this case. Using My Oracle support page Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1] it is possible to read the trace. In the bind part avl Actual value length (array length too).. Compared that to the column size at the position of the bind in the insert statement and the root problem was found.

About bad application design
* The application should know how long values can be inserted to each column.
* If the application receives an error it should have an error handler that releases the reserved resources. This time the unreleased resource was a database connection.