Spotlight on Oracle
ORA-04031: unable to allocate num bytes of shared memory num,
ORA-04031 unable to allocate shared memory error occurs when running user wizard in Spotlight.
Cause: More shared memory is needed than was allocated in the operating system process. SGA private memory has been exhausted.
Action: If you can flush the shared pool, it will probably free up enough memory to run Spotlight. But generally, there is no other way around this, and it is not a problem with Spotlight but an Oracle limitation caused by the configuration settings.
command to flush shared pool. ALTER SYSTEM FLUSH SHARED_POOL
Another alternative, Bouncing the database will probably get you around the error but just to be safe you can increase your shared pool size in the init.ora file and then bounce the database so that the changes take effect.
If possible, please try making the shared pool bigger. Flush shared memory may only delay the problem.