Oracle Scratchpad

September 12, 2010

Surprising Error

Filed under: Bugs,Oracle — Jonathan Lewis @ 6:06 pm BST Sep 12,2010

Here’s an unexpected error that appeared recently while I was doing a test  on a database running  (The fact that I got an error didn’t surprise me, it was Oracle’s choice of error for the mistake I’d made.)

Cut-n-Paste from a SQL*Plus session:

SQL> alter system set db_cache_size = 256m scope = memory;
alter system set db_cache_size = 256m scope = memory
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00001: unique constraint (.) violated

If you’re wondering, I was trying to reduce the db_cache_size from 384MB and the value had been set by the startup parameter file – so the ORA-02097 is probably trying to tell me that I can’t reduce the dynamic value below the initial minimum. (I didn’t check this, I just bounced the database with a change of parameter file, but it’s probably a reasonable guess.)


  1. I am much more surprised by that ORA-000001 unique contraint violated error.
    I can understand that you can’t lower memory below a certain level, especially if it is currently used, but what on earth has the constraint error to do in this case?

    It looks really odd!


    Comment by Flavio Casetta — September 12, 2010 @ 8:42 pm BST Sep 12,2010 | Reply

  2. Can you reproduce it ? We’d better 10046 trace this odd one.

    Comment by maclean — September 13, 2010 @ 3:37 am BST Sep 13,2010 | Reply

    • Maclean,

      Unfortunately I was running a stress test at the time and needed to reconfigure the instance to make it complete in less than 10 days – so I wasn’t too concerned about capturing the details, even though I took a few minutes to see if there was a particular boundary value at which the error would go away.

      Now that I have a little time to look at it the anomaly does not reproduce. It’s possible that it was a side effect of the first few hours of the test doing something unexpected with memory allocation in the SGA.

      Comment by Jonathan Lewis — September 13, 2010 @ 11:45 am BST Sep 13,2010 | Reply

  3. Hi Jonathan,

    Surprised to see “ORA-00001: unique constraint (.) violated”


    Comment by Anand — September 13, 2010 @ 6:38 am BST Sep 13,2010 | Reply

  4. It must be an Oracle 11G R1 issue. I have repeated your exercise on 11G R2 and get the following:-

    SQL*Plus: Release Production on Mon Sep 13 10:04:58 2010
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    SQL > startup
    ORACLE instance started.
    Total System Global Area  644468736 bytes
    Fixed Size                  1376520 bytes
    Variable Size             188747512 bytes
    Database Buffers          444596224 bytes
    Redo Buffers                9748480 bytes
    Database mounted.
    Database opened.
    SQL > show parameter db_cache_size
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_cache_size                        big integer 348M
    SQL > alter system set db_cache_size=256M scope=memory;
    System altered.
    SQL >

    I think this was what you were attempting when the error occurred.

    Comment by Tony Sleight — September 13, 2010 @ 9:13 am BST Sep 13,2010 | Reply

    • Tony,

      See my reposonse to Maclean – it’s possible that the anomaly doesn’t appear until the SGA has been thrashed for a few hours. I can’t reproduce it on 11.1 either at the moment.

      Comment by Jonathan Lewis — September 13, 2010 @ 11:48 am BST Sep 13,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by