Thursday, January 1, 2009

splitting table partitions with BLOB in streams

The last hours of the last day of the last year I spent trying to get
capture process of streams working.

Developers executed the script that prepared application tables
for new year splitting and rebuilding indexes on them.

After that capture process was aborted due to
ORA-04031 for streams pool.

The setting for stream_pool_size was 384M only but increase of
the value up to 1, 2 even 5Gb did not help either.

Capture was failing with the errors:

ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","kol raw")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","sob_kgqmrec")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","kolccst obj")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","sob_kgqmrec")


After research on Metalink one interesting note
had been found:

STREAMS CAPTURE ERRORS OUT WITH ORA-4031 ON SPLIT PARTITION COMMAND
which exactly describes the problem that we have.
But they were able to go through all transactions for capture process
only when streams_pool_size was increased up to 16Gb!
Unfortunately there is no chance for now to allocate
so much memory for streams and there is no patch for it yet.

Might be upgrade (from 10.2.0.4) to 11g will help,
might be additional memory will be found
or Metalink will come with some ideas, will see...

Have a good day!


2 comments:

goryunov said...

Even increase to 16Gb for streams_pool_size did not help and everyhting ended up through re-addition of the tables to streams after those transactions had been cancelled.

Shiva Prabhu said...

Hello, I am seeing this error in 11gr2 as well, I think SGA/SQL tuning will be a better option rather than Oracle devtha's help!