The last real one that comes to mind I didn't track down all of the parameters on. However in the middle of an insanely complex query, in several nested subqueries if I did not put an explicit alias on the table and refer to it in the subquery for a specific field, I got no error but that field wasn't populated with any information. I added the aliases and data appeared. (If it was truly ambiguous, then I should have had an error message. It wasn't ambiguous, and I didn't get an error, but somehow Oracle got confused when it shouldn't have. I checked that Oracle doesn't get confused on simple queries, and didn't bother taking the time to figure out what combination of things it took to confuse Oracle.)
We also have a database where for reasons nobody has figured out, if you set timing on, timing always reports that things took twice as long as they really took. What I mean is that if I do something like this:
select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;
set timing on;
select (something complex and slow)
;
set timing off;
select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;
the time that Oracle says the query took is consistently double the difference between the dates.
There is a non-bug that will irritate some users. There is a trick that a group by does an implicit sort, so you can speed up some queries by leaving out the sort. However the sorting of NULL versus everything else is different when you have 1 column versus more than 1 column. Since this is officially undefined behaviour, it isn't Oracle's bad that this changed. However it may still trip up some people.
There were a couple more bugs that I remember hitting which Oracle already had patches for. But those brain cells have been repurposed so I forget what they were.
I've run across a number of situations where Oracle simply can't find a good query plan that I wish it did. This is not a bug though. For example consider a query like this:
\nSELECT (stuff)\nFROM big_table, small_table_1, small_table_2, ...\nWHERE big_table.id = small_table_1.id (+)\n AND big_table.id = small_table_2.id (+)\n ...\n
Oracle will only consider query plans where it takes the big table, merges it with one small table, merges that with another, etc. That gets slow because it is throwing around a lot more temporary data than it needs to. The best workaround that I know of is to create a temporary table that has all of the fields from small_table_1..n in it. Do separate inserts for each small table, then do a join between a group by off of that temporary table with the big table.
Again, not a bug. But this workaround has proven useful to me more than once. (And there are other cases where I've found it faster to offload data from the database, process it in code, and then load the result back into the database. In fact I'm in the middle of a project like that.)
Cheers,
Ben