ORA-00604: error occurred at recursive SQL level 1, ORA-22914: DROP of nested tables not supported

In the last few days we've suddenly started getting this error. The really weird thing is we get this when inserting data into a table! There was a trigger on the table we were inserting into so I disabled it but the problem did not go away. In fact it was easily reproducible with a simple insert into the "problem" table. Looking at the table in question it had around 500000 rows and included a clob but other than that nothing special. Since this is only a development system we deleted all the rows in the table and reenabled the trigger and the problem went away. Then a few days later it came back but when inserting into a different table in the same database/schema.

We noticed that the system and sysaux tablespaces were pretty close to full so added additional datafiles in there to make sure that wasn't the issue, and it had no effect.

Further playing with the table and disabling constraints led to us being able to insert again but only when the primary key was disabled. When attempting to re-enable the primary key constraint:

alter table "xxx"."yyy" enable constraint "yyy_PK";

Error starting at line 1 in command:
alter table "xxx"."yyy" enable  constraint "yyy_PK"
Error report:
SQL Error: ORA-22914: DROP of nested tables not supported
22914. 00000 - "DROP of nested tables not supported"
*Cause:    Attempted to DROP a nested table.
*Action:   nested tables cannot be explicitly dropped. nested tables can
           only be dropped by dropping their containing parent table.

So then we created a new table and tried inserting into it:

create table test(p1 integer);
insert into test(p1) select level from dual connect by level <= 2000;

Same problem.

Eventually after several runs through this things got to the state where we couldn't even create the table, so it looked like some sort of quota is being hit but nothing on the oracle side seems to be showing any problems.

Running things as sys instead of the the aaa user once this has happened worked ok too (sys user in a different tablespace).

After much scratching of heads it turns out (we didn't know) that since Oracle 10 (we are using 11) tables are not deleted on drop but simply moved to the recycle bin and on our developmnent system where we often drop tables and recreate them we've never cleared this recycle bin.

After purging (PURGE TABLESPACE USERS) the user table space all was well (but it took at least 5 minutes).

It seems something is wrong here as the error looks misleading. Oracle seems to think the table space is full but everything in the enterprise manager is showing the amount free (without including the recycle bins contents). I'm guessing Oracle tries to purge things from the recycle bin when it needs space and fails with the nested table error but I don't know.

Anyway, for us, on this system we now just do "ALTER SESSION SET recyclebin = OFF" as we are not bothered about recovering accidental dropped tables.

Many thanks to my colleague Jason who did most of the hard work on this. We thought it may be useful to others as a search for this error seems to cover a massive number of problems.