-1

I am developing a PL/SQL script, using TOAD. At this point of the development, I am debugging it. This has involved: wrap a section in begin/end, F5 run it, receive error info, fix problem, repeat.

All of a sudden, out of nowhere, I am receiving

  ORA-00604: error occurred at recursive SQL level 2
  ORA-01654: unable to extend index SYS.I_OBJ5 by 128 in tablespace SYSTEM

The script begins with a drop table/create table set of instructions for a simple 2-field table, in my logon schema. After this started happening, I narrowed the part I am re-running to just one line: drop table <tblName>

In trying to narrow this down, I finally went to the TOAD Schema Browser, right-clicked on the table, and selected "Drop table" from the context menu — same result.

I must have run this statement 120 times yesterday, without this act giving me any trouble. Now? Not happenin! I am really stumped. Did all those runs maybe load up some area that is now full? Part of this script opens file system files. I didn't know I had to then close them, and I ran into "`This action would result in ‘too many files open’ (each iterative run opened one more). Have I done something like that by dropping and recreating this table so many times?

wolφi
  • 8,091
  • 2
  • 35
  • 64
Brian Wren
  • 367
  • 5
  • 15
  • 1
    your system tablespace is out of space (that is what the "unable to extend" means most likely). You could try to purge your old tables (the command is "purge recyclebin") -- by default oracle will keep backup copies of the old tables – Peter M Jun 08 '18 at 17:45
  • 1
    Can the 'recycle bin' be viewed to confirm this? – Brian Wren Jun 08 '18 at 17:59
  • OK, I viewed it. Lots and lots of this particular table. Right-Click the Bib, "Purge.' Is there a way to do a drop/create sequence, and have it NOT put an entry into the Recycle Bin? – Brian Wren Jun 08 '18 at 18:06
  • 1
    `drop table xyz purge;` – William Robertson Jun 08 '18 at 18:13
  • 2
    but why you are using tablespace system ? whyyou dont create a tablespace for your project/test and specify datafiles – Moudiz Jun 08 '18 at 18:20
  • I am in a developer role, and a helpdesk Level 2 technician, and do not have much say. And to make thins worse, I am a contractor, here. That limits my voice even more. Also, I don't know very much about configuration. – Brian Wren Jun 08 '18 at 19:07

1 Answers1

0

I agree with @Peter M, most likely your SYSTEM tablespace is full.

The error message says it quite clearly: unable to extend index ... in tablespace SYSTEM means that Oracle ran out of space while trying to make an index bigger. The tablespace SYSTEM is used by Oracle for internal purposes, for instance for the list of tables and columns. It is therefore quite important and normally well supervised by DBAs and kept clean of other objects like developer tables. The schema name SYSalso points in this direction.

The other hint is recursive SQL: Oracle runs not only your SQL (like CREATE TABLE) but sometimes needs to do some housekeeping, like updating said list of table, which is also done by SQL. The second flavour is called recursive.

I'd guess therefore that it is not your table that causing the SYSTEM tablespace to overflow, but the many changes.

If this happened at my place of work, I'd got a friendly phone call by a DBA by now, asking what's going on...

wolφi
  • 8,091
  • 2
  • 35
  • 64