ORA-00604: error occurred at recursive SQL level 1

oracleoracle-12c

I was compiling a package on an Oracle Database just fine and suddenly this error occurred:

ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_IDENTIFIER$ by 8 in tablespace SYSAUX
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I believe it's a Tablespace issue and raised the issue to our DBA Team. However, my colleague tried the same package on the same database, using the same user and was able to compile it just fine. We checked and our Database credential details are the same as well.

Any idea why it's not compiling in my SQL Developer but his is working fine?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    0
PL/SQL Release 12.1.0.2.0 - Production  0
"CORE   12.1.0.2.0  Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production  0
NLSRTL Version 12.1.0.2.0 - Production  0

Best Answer

Even if you use the same credentials, your colleague may have a different environment with PL/Scope disabled.

SQL> create procedure p1 as
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

Compile the procedure without PL/Scope:

SQL> show parameter plscope

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plscope_settings                     string      IDENTIFIERS:NONE
SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

No change. Now compile with PL/Scope enabled:

SQL> alter session set plscope_settings='IDENTIFIERS:ALL';

Session altered.

SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2058

This created some extra metadata in the above table which happens to be in the SYSAUX tablespace. Now disable and compile again:

SQL> alter session set plscope_settings='IDENTIFIERS:NONE';

Session altered.

SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

Back to the original state.

This is the problem:

ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_IDENTIFIER$ by 8 in tablespace SYSAUX, which simply means the SYSAUX tablespace is full.

Being unable to compile the PL/SQL code with PL/Scope enabled is just a symptom of the above error. Ask the DBA to make/increase space in the SYSAUX tablespace. PL/Scope is a configurable option in SQL Developer (Tools - Preferences - Database - PL/SQL Compiler - PLScope identifiers).

Related Question