The "Compile with PL/Scope" action should not use dbms_utility.compile_schema

[rvo-cs]

As of v1.0.0, the "Compile with PL/Scope" action recompiles PL/SQL code by calling dbms_utility.compile_schema ¹. Replacing that procedure should be considered, for the following reasons:

1. It's not complete, as it does not handle objects of type TYPE and TYPE BODY (the action includes ad-hoc code for these object types to work around that)
2. It is a black box, causing BEFORE / AFTER DDL triggers to not fire

And above all:

3. It uses UTL_RECOMP internally, which is known to break if 2 database users happen to call it simultaneously, even if they are working in distinct schemas, resulting in infrequent but (very) unexpected failures—see MOS Doc ID 1568324.1. This makes it an intrinsically unreliable method of recompilation, unless one knows for certain that nobody else in the database will use it at the same time.

For the above reasons, getting through the trouble of providing an ad-hoc recompilation routine would seem a better option, though more ambitious ².

Footnotes

1. Except for objects of types TYPE and TYPE BODY, which happen to be ignored by dbms_utility.compile_schema, hence must be recompiled using an ad-hoc routine. ↩

2. An ad-hoc schema recompilation routine has to comprehensive, and process objects in proper dependency order, if possible. ↩

[PhilippSalvisberg]

I compiled two schemas in parallel on an 21c and 19c instance. It worked. No ORA-600. No ORA-20000: Unable to set values for index UTL_RECOMP_SORT_IDX1: does not exist.

The MOS Doc ID 1568324.1 is based on 11.2.0.3. The mentioned bug is not public. Hence, I cannot see the status of it.

When you look at the code generated by the action Recompile Schema... you see that dbms_utility.compile_schema is used. So, SQLDev is using it as well.

Writing a replacement for compile schema handling all object types correctly and in the right order, is not a simple task. I've done something like that in different projects, e.g. to minimize the total invalid objects to the really defective ones. However, the scope was always reduced to the object types that were relevant for the project. Furthermore, I think the dbms_utility.compile_schema works good enough for a PL/Scope quick start. As soon as you plan to use it on a regular basis you will adapt your deployment processes and change the settings of SQL Developer as mentioned in #55.

If you have problems in your environment with "bug 13631826 - PL/SQL SHOULD SUPPORT UTL_RECOMP RUNNING IN PARALLEL ON DIFFERENT SCHEMAS" then I suggest to open an SR. In the end Oracle should fix this issue. And please ask them to make the associated bug public, so we can monitor the progress.

I'd like to avoid adding code to this repository which is duplicating the logic of dbms_utilty.compile_schema. It's not the burden of writing it. It's the burden of maintaining it. I believe we should use the code Oracle provides us whenever possible. And IMO it is possible in this case.

[PhilippSalvisberg]

To reproduce #65 I compiled the ut3 schema. The result was a complete mess. - So I see the need to replace dbms_utility.compile_schema with something that works.

[rvo-cs]

Regarding MOS Doc ID 1568324.1: yes, the document dates back to the 11.2 era...

I was hit by that issue on 11.2.0.4, back in 2017; we used multiple schemas for creating insulated test environments, each of which could be used as a deployment target in our CI system. The deployment of schema changes was automated using Liquibase, and dbms_utility.compile_schema was called in the end of that process. And it would randomly fail every now and then, with obscure exceptions similar to those in the MOS note... So we had a hard time implementing retries in order to work around it.

On my 19.9 test PDB, the following DDL activity is seen on the SYS schema while dbms_utility.compile_schema is being run:

DDL_TIME                 EVENT_TYPE   OBJECT_TYPE  OBJECT_OWNER    OBJECT_NAME                    DDL_TEXT
------------------------ ------------ ------------ --------------- ------------------------------ ----------------------------------------------------------------------------------------------------
2022-11-05 19:59:06.8699 DROP         SEQUENCE     SYS             UTL_RECOMP_SEQ                 DROP SEQUENCE utl_recomp_seq
2022-11-05 19:59:06.8819 CREATE       SEQUENCE     SYS             UTL_RECOMP_SEQ                 CREATE SEQUENCE utl_recomp_seq SHARING=NONE START WITH 0 MINVALUE 0 ORDER NOCACHE
2022-11-05 19:59:07.1569 DROP         SEQUENCE     SYS             UTL_RECOMP_SEQ                 DROP SEQUENCE utl_recomp_seq
2022-11-05 19:59:07.1594 CREATE       SEQUENCE     SYS             UTL_RECOMP_SEQ                 CREATE SEQUENCE utl_recomp_seq SHARING=NONE START WITH 0 MINVALUE 0 ORDER NOCACHE
2022-11-05 19:59:07.1831 TRUNCATE     TABLE        SYS             UTL_RECOMP_SORTED              TRUNCATE TABLE utl_recomp_sorted
2022-11-05 19:59:07.2061 DROP         INDEX        SYS             UTL_RECOMP_SORT_IDX1           DROP INDEX utl_recomp_sort_idx1
2022-11-05 19:59:07.7682 CREATE       INDEX        SYS             UTL_RECOMP_COMP_IDX1           CREATE /*+ NOPARALLEL */ INDEX utl_recomp_comp_idx1 ON utl_recomp_compiled(obj#)
2022-11-05 19:59:07.8564 CREATE       INDEX        SYS             UTL_RECOMP_SORT_IDX1           CREATE /*+ NOPARALLEL */ UNIQUE INDEX utl_recomp_sort_idx1 ON utl_recomp_sorted(obj#)
2022-11-05 19:59:07.8613 DROP         INDEX        SYS             UTL_RECOMP_COMP_IDX1           DROP INDEX utl_recomp_comp_idx1
2022-11-05 19:59:07.9887 DROP         SEQUENCE     SYS             UTL_RECOMP_SEQ                 DROP SEQUENCE utl_recomp_seq
2022-11-05 19:59:07.9919 CREATE       SEQUENCE     SYS             UTL_RECOMP_SEQ                 CREATE SEQUENCE utl_recomp_seq SHARING=NONE START WITH 0 MINVALUE 0 ORDER NOCACHE
2022-11-05 19:59:07.9984 TRUNCATE     TABLE        SYS             UTL_RECOMP_SORTED              TRUNCATE TABLE utl_recomp_sorted
2022-11-05 19:59:08.0030 DROP         INDEX        SYS             UTL_RECOMP_SORT_IDX1           DROP INDEX utl_recomp_sort_idx1
2022-11-05 19:59:08.0803 CREATE       INDEX        SYS             UTL_RECOMP_COMP_IDX1           CREATE /*+ NOPARALLEL */ INDEX utl_recomp_comp_idx1 ON utl_recomp_compiled(obj#)
2022-11-05 19:59:08.2179 CREATE       INDEX        SYS             UTL_RECOMP_SORT_IDX1           CREATE /*+ NOPARALLEL */ UNIQUE INDEX utl_recomp_sort_idx1 ON utl_recomp_sorted(obj#)
2022-11-05 19:59:08.2227 DROP         INDEX        SYS             UTL_RECOMP_COMP_IDX1           DROP INDEX utl_recomp_comp_idx1
2022-11-05 19:59:08.2573 DROP         SEQUENCE     SYS             UTL_RECOMP_SEQ                 DROP SEQUENCE utl_recomp_seq
2022-11-05 19:59:08.2599 CREATE       SEQUENCE     SYS             UTL_RECOMP_SEQ                 CREATE SEQUENCE utl_recomp_seq SHARING=NONE START WITH 0 MINVALUE 0 ORDER NOCACHE
2022-11-05 19:59:08.2693 CREATE       INDEX        SYS             UTL_RECOMP_SORT_IDX2           CREATE /*+ NOPARALLEL */ INDEX utl_recomp_sort_idx2 ON utl_recomp_sorted(batch#)
2022-11-05 19:59:08.6659 DROP         SEQUENCE     SYS             UTL_RECOMP_SEQ                 DROP SEQUENCE utl_recomp_seq
2022-11-05 19:59:08.6710 CREATE       SEQUENCE     SYS             UTL_RECOMP_SEQ                 CREATE SEQUENCE utl_recomp_seq SHARING=NONE START WITH 1 MINVALUE 1 ORDER NOCACHE
2022-11-05 19:59:08.7008 TRUNCATE     TABLE        SYS             UTL_RECOMP_SORTED              TRUNCATE TABLE utl_recomp_sorted
2022-11-05 19:59:08.7054 DROP         INDEX        SYS             UTL_RECOMP_SORT_IDX1           DROP INDEX utl_recomp_sort_idx1
2022-11-05 19:59:08.7329 DROP         INDEX        SYS             UTL_RECOMP_SORT_IDX2           DROP INDEX utl_recomp_sort_idx2
2022-11-05 19:59:08.8103 CREATE       INDEX        SYS             UTL_RECOMP_COMP_IDX1           CREATE /*+ NOPARALLEL */ INDEX utl_recomp_comp_idx1 ON utl_recomp_compiled(obj#)
2022-11-05 19:59:08.9132 CREATE       INDEX        SYS             UTL_RECOMP_SORT_IDX1           CREATE /*+ NOPARALLEL */ UNIQUE INDEX utl_recomp_sort_idx1 ON utl_recomp_sorted(obj#)
2022-11-05 19:59:08.9186 DROP         INDEX        SYS             UTL_RECOMP_COMP_IDX1           DROP INDEX utl_recomp_comp_idx1

(This is captured by a post-DDL database trigger.)

On 11.2, apparently there wasn't enough synchronization, if at all, to prevent concurrent sessions running the above (or similar) statements from walking on each other's feet. Has this been fixed since then? I don't know.

Meanwhile, the SQL Developer extension should still work nicely on 11.2, shouldn't it?

[rvo-cs]

Regarding recompiling the UT3 schema, to be fair it could be impossible to do that entirely, for the following reasons:

1. The schema contains types with table dependents
   And:
2. Apparently, the UT_TRIGGER_ANNOTATION_PARSING trigger can neither be recompiled, nor disabled.

alter trigger "UT3"."UT_TRIGGER_ANNOTATION_PARSING" compile plsql_optimize_level=2 plsql_code_type=INTERPRETED plsql_debug=FALSE plsql_warnings='ENABLE:ALL,DISABLE:  6009' nls_length_semantics=BYTE plscope_settings='IDENTIFIERS:ALL, STATEMENTS:ALL'
Error report -
ORA-01031: insufficient privileges

Even if attempted as SYSDBA.

Trying to disable the trigger beforehand fails in exactly the same fashion, even with "_system_trig_enabled"=false.