Answer from Oracle about the PL/Scope Database Corruption Issue
One of the benefits of being an Oracle ACE Director is having access to a private forum where ACE Directors can share knowledge with each other. But, more importantly, they can also use this forum to “ask Oracle”, with the expectation that Oracle will respond to inquiries as quickly as possible.
I used the ACE Director private forum to let Oracle know and ask for an explanation about the 11g database corruption problem that Dan had discovered and that I blogged about a few days ago. In short, the database corruption was caused by compiling the STANDARD package after an ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'.
Indeed the answer came back quickly and was posted by Mark Townsend, the vice president of database product management at Oracle. Here it is:
An answer from our esteemed PL/SQL product manager
You pretty much cannot manage to write a single PL/SQL unit without creating a dependency on package Standard. I think that most customers come to understand it, but it’s among the gazillion fundamental conceptual foundations of Oracle Database that we don’t document well.
Moreover, even given our new-in-11.1 fine-grained dependency tracking, if you recompile Standard, then you invalidate its dependants. This is not due to careless implementation. There are deep causes (too hard for me to explain here).
So even in 11.1, as ever before, we say that it’s unsafe to recompile Standard except in upgrade mode. (There’s even more to the story, actually.) We provide the script utlirp.sql on $ORACLE_HOME/rdbms/admin for that purpose. It has reasonable instructions for use in its comments. The steps that are described include re-compiling everything in the whole db.
Sadly, an 11.1 Tahiti search for utlirp.sql fails to find anything that looks like a canonical account (sigh) — only en passant references.
The key question is this: why did Eddie (or was it Dan) attempt to recompile Standard? Pls answer this.
I can think of two plausible reasons.
The first would be as part of an attempt to recompile the whole database using native compilation (or taking it back to all interpreted). We provide a script to do this in a safe way (using the utlirp.sql approach). It’s described in “Compiling the Entire Database for PL/SQL Native or Interpreted Compilation” the PL/SQL Language Reference.
The second would be to make its identifiers available in the new DBA_Identifiers view family (see PL/Scope). Our script to do this sadly missed the release. We’re about to post it on OTN.
The credit goes to Dan who attempted to recompile STANDARD and discovered this issue first. At least now we know it was because of a script that missed the 11.1 database release.
Related articles:
- PL/Scope in Oracle Database 11g – Revisited
- PL/Scope in Oracle Database 11g
- Check These SQL Beauties Out
