ouch: ORA-01418: specified index does not exist, when trying to drop a user with 1001 indexes on a table

simple test-case for 11.2.0.4 and 12.1.0.1 on linux x64:

connect / as sysdba
drop user u21 cascade;
create user u21 identified by u21 default tablespace users temporary tablespace temp;
grant create session, create table to u21;
connect u21/u21
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u21 cascade;
drop user u21 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

ouch …

does not happen with 1000 indexes:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

happens again with 1002:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
  execute immediate 'create index i1002 on t1 (c1,c2,c3)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

drop user u22 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

seems to be limit at 1000, somehow.

even more weird:

SYS@dbs101> select count(*) from dba_indexes where owner = 'U22';

  COUNT(*)
----------
	 0

SYS@dbs101> select count(*) from dba_objects where owner='U22';

  COUNT(*)
----------
       996