000014092 - maximum open cursors exceeded

Customer experience following error when load configuration files to database:

2008-07-13 04:43:58,659 ERROR [main] [] [] [com.passmarksecurity.dao.sp.ConfigSpaceStoredProcBase] - <failed to set configSpace: ORA-01000: maximum open cursors exceeded

ORA-06512: at "PMSECURE.SETCONFIGDEFINITION", line 68

ORA-06512: at line 1

Some customers have dramatically increased the question ref bean in c-config-challenge.xml file, which caused increased cursor needed when loading the file to database.

The error is caused by not enough cursor to open in order to load the configrations.

1) run following SQL to get how many open cursors and its owners.

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc 

2) Run following command to get the max open cursor setting in database

SQL> show parameter curs

3) increase the max cursor setting as needed.