The "Compile with PLScope" action should not touch Oracle-maintained schemas

[rvo-cs]

As of v1.0.0, the scope of the action is limited to the schema (plus public synonyms) of the current session user. This limitation could be a bug—see issue #55.

Now let's suppose, as a mental experiment, that that issue is fixed, and now a user with DBA privileges may use the action to recompile code with PL/Scope in any schema. That's cool. What about applying it to the SYS schema? After all, the idea of recompiling SYS packages with PL/Scope has been advertised before (e.g. in this article; see example 3b), and it sure sounds promising. And this nice "Compile with PL/Scope" action was created to do just that, wasn't it?

As a result, inexperienced users having DBA privileges¹ may not resist the temptation of applying the action to the SYS schema, resulting in possible disasters due to object invalidations—both in the SYS schema and in related Oracle-maintained schemas, e.g. LBACSYS—which could be difficult to repair.

Therefore, as a safety precaution, the scope of the "Compile with PL/Scope" action should be narrowed in order to prevent from it being applied to Oracle-maintained schemas. Recompiling the SYS schema with PL/Scope may indeed be possible, but it seems that doing it requires a lot more than what can be provided in a simple context-menu action², so it should be made clear that Oracle-maintained schemas are beyond the scope of the action, and that restriction should be enforced.

Footnotes

1. Production databases are generally in the hands of experienced DBAs, or at least extremely careful people. On the contrary, it is not infrequent on some sites that developers, experienced or not, may have full DBA privileges on development databases. They are the ones who will find the highest interest in PL/Scope, and they may not always be as cautious as production DBAs. ↩

2. Recompiling all objects in the SYS schema with PL/Scope is far from a trivial task: it requires putting the database in UPGRADE mode, running utlirp.sql with a specially modified version of utlip.sql, and then recompiling everything using utlrp.sql—all this through catcon.pl if on multitenant. And after that you still have to do the recompilation of public synonyms, this time with containers started in RESTRICTED mode... This operation can be done for fun, or out of curiosity, but of course it is absolutely not supported by Oracle, and what happens through subsequent patching/upgrade sessions remains to be seen—chances are plscope_settings will revert to the default for affected objects. ↩

[PhilippSalvisberg]

I agree. In case option b) is implemented in #55 we should ensure that Oracle maintained schemas are not compiled. We can do that by adding a fail-safe step.