We started getting lots of ORA-07445 errors that are being reported in the Exadata production database from a client.
Checking on the traces we can see that someone is running the below SQL and that is triggering these errors:
declare i number := 0; begin for c1 in ( select address, hash_value, sql_id from v$sql where last_active_time < sysdate - 1/24 and executions < 2 ) loop begin dbms_shared_pool.purge(c1.
address || ' ' || to_char(c1.hash_value), 'C'); i := i + 1; exception when others then dbms_output.put_line(c1.sql_ id); end; end loop; dbms_output.put_line('Cursors purged = ' || to_char(i)); end; /
I’m not even going to discuss the PL above, it’s purging all new SQLs executed less than 2 times in the last 24hours. I’m not really agreeing with this…
What matters is: in the end, it was being caused because hash value of non-cursor is being passed to dbms_shared_pool.purge(). This is a match to Bug 29281112 – ORA-7445: [kxspurgecursor()+517] [sigsegv] (Doc ID 29281112.8)
There is no actual workaround besides stop passing a non-cursor hash to dbms_shared_pool.purge, however we have a fix for a better addressing of those cases on: