Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am receiving this error in a development database. It happens on some specific queries (simple inserts and deletes via .NET application). The database is used by one user, and error happens regardless for how long the database was running.

The only memory related parameter which is set is: MEMORY_TARGET=1.2G

Oracle 11.2 x64 Standard Edition One Windows Server 2008 R2

share|improve this question
add comment

2 Answers

Your application is probably not using bind variables, which has caused the SGA to become fragmented (it fills up with multiple copies of SQL that use literals instead of binds).

You can temporarily fix the problem by flushing the shared pool with:

alter system flush shared_pool;

There's a workaround you can try to stop it happening in the future.

Turn on cursor sharing, then bounce the database:

ALTER SYSTEM SET cursor_sharing='SIMILAR' SCOPE=BOTH;

The only real way of fixing this is to rewrite your application so that it uses bind variables.

share|improve this answer
    
"then bounce the database" ==? "shutdown - startup" ? –  Atilla Ozgur Feb 1 '12 at 13:59
    
Yes, shut the database down & start it up again –  FreshPhilOfSO Feb 1 '12 at 14:00
    
problem "solved" in a strange way –  spm Feb 1 '12 at 14:02
    
As I said, if the application not using bind variables is the cause of the problem, the only way to "solve" it is to rewrite the application to use them. –  FreshPhilOfSO Feb 1 '12 at 14:05
    
I will try both solutions if problem occurs again. But i don't think it applies to this case, because it happens even if it is first query after restart. –  spm Feb 1 '12 at 14:07
show 2 more comments

As an addition to previous answer, it turns out that ALTER SESSION SET NLS_COMP=LINGUISTIC;ALTER SESSION SET NLS_SORT=GENERIC_M_CI; for some reason quickly exhausts memory. Problem dissapears when using another NLS_SORT.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.