Here’s an unexpected error that appeared recently while I was doing a test on a database running 11.1.0.6. (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.)
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!
Flavio
Comment by Flavio Casetta — September 12, 2010 @ 8:42 pm UTC Sep 12,2010 |
Can you reproduce it ? We’d better 10046 trace this odd one.
Comment by maclean — September 13, 2010 @ 3:37 am UTC Sep 13,2010 |
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 UTC Sep 13,2010 |
Hi Jonathan,
Surprised to see “ORA-00001: unique constraint (.) violated”
Anand
Comment by Anand — September 13, 2010 @ 6:38 am UTC Sep 13,2010 |
It must be an Oracle 11G R1 issue. I have repeated your exercise on 11G R2 and get the following:-
I think this was what you were attempting when the error occurred.
Comment by Tony Sleight — September 13, 2010 @ 9:13 am UTC Sep 13,2010 |
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 UTC Sep 13,2010 |