what do u think is this

SQL> alter procedure P_FINDFULLSQL compile;
alter procedure P_FINDFULLSQL compile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04030: out of process memory when trying to allocate 1040896 bytes (cursor work he,kllcqas:kllsltba)

The first error is synamous with the second.

The error 004030 is caused when the operating system process's private memory has been exhuasted.

The likely cause if your DB seems to run fine unless you try to compile the procedure is thier is a bug in the procedure or related called procedures that causes excessive allocations of process memory space (e.g. an accidental loop in the code maybe).

You could try and look at your trace files to get a better idea of what the exact cause is but it does sound very like your PL/SQL procedure has a bug.

Hope this helps.