Behavior Change

Where do these large trace files come from in Oracle 12c?

Just had an observation about very large trace files on one of my customers I’m working with at the moment. When I write “very” I mean “VERY” as some grew over 10GB within a few hours.

Growth - Trace Files - (c) Mike Dietrich

The files contained a ton of such messages:

----- Cursor Obsoletion Dump sql_id=5p8a9d4017bq3 -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=00007FFB8AD45CB0 phd=00007FFB8AD45CB0

After doing a bit of research I came across this document and an explanation:

MOS Note:1955319.1;
Huge Trace Files Created Containing “—– Cursor Obsoletion Dump sql_id=%s —–“

Well, we introduced an Enhancement – via an unpublished bug (and I’d guess it is …

Continue reading...

New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing.

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) – and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter.

  • Oracle White Paper: Best Practices for Gathering Statistics –
Continue reading...

Oracle – Security Behavior Change with non-SYSDBA Triggers

Oracle Database SecuritySometimes things get revealed at unexpected occasions. This one happened during a recent customer upgrade to Oracle Database 12c with a 3rd party geospatioanl application installed (ESRI).

At the very end of the upgrade the customer saw many ORA-1031 (insufficient privileges) errors and it seemed to be that nothing was working correctly anymore.

This happened during the run of catupend.sql. The following code path in  catupend.sql causes the error.

cursor ddl_triggers
select o.object_id from dba_triggers t, dba_objects o
where t.owner = o.owner and t.trigger_name = o.object_name and o.object_type = 'TRIGGER'
and (t.triggering_event like '%ALTER%' or t.triggering_event like '%DDL%');
Continue reading...

New Behaviour in Oracle Database 12c and SELECT ANY DICTIONARY with reduced privilege set

You’ve just upgraded to Oracle Database 12c – but your favorite admin tool receives an ORA-1031: Insufficient Privileges after connection?

Then the reason may be the reduced set of privileges for the SELECT ANY DICTIONARY privilege. This privilege does not allow access to tables USER$, ENC$ and DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Actually such changes are not new. For instance in Oracle 10.1 we removed the access to  LINK$ in SELECT ANY DICTIONARY (well, this may have happened because the dblink’s password was stored in clear text in LINK$ – a misbehavior which is fixed since

Continue reading...