For tests we have Oracle 11 instance running on a RAM disc. Since the RAM disc is limited in size the database must not do any auditing or anything that keeps increasing the size of anything.

We managed to fix many such leaks but have one left which we don't know how to handle:

The database tries to write to the table


resulting (after a couple of weeks) in a

ORA-01653: unable to extend table SYS.WARNING_SETTINGS$ by 8 in tablespace SYSTEM 

while trying to create a database object (a trigger right now)

We tried to disable all writes to that table with this statement

  dbms_warning.add_warning_setting_cat('ALL', 'DISABLE', 'SYSTEM');

The questions are:

What is the purpose of that table?

Why does Oracle keep writing into it?

How do we make Oracle stop doing that?

migrated from stackoverflow.com Apr 20 '12 at 12:50

This question came from our site for professional and enthusiast programmers.

  • Why the close vote? – Jens Schauder Apr 19 '12 at 13:12
  • According to psoug.org/reference/dbms_warn_int.html it appears to be related to the PL/SQL compiler warnings. What if you run: ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'; whenever you compile anything? Not sure if this would be equivalent to your add_warning_setting_cat or not. – Jeffrey Kemp Apr 19 '12 at 14:47
  • We rebuild the database and I applied an ALTER SYSTEM SET PLSQL_WARNINGS='DISABLE:ALL'. I'll try to find if this does anything tomorrow. – Jens Schauder Apr 19 '12 at 16:19
  • 1
    This may be applicable: Bug 11720698 : SYS.WARNING_SETTINGS$ NOT CLEANED UP WHEN OBJECT IS DROPPED. – Leigh Riffel Apr 20 '12 at 19:36
  • @LeighRiffel Thanx that seems to be the problem. Wanna make an answer out of the comment? – Jens Schauder Apr 25 '12 at 15:53

Bug 11720698 with the title "SYS.WARNING_SETTINGS$ NOT CLEANED UP WHEN OBJECT IS DROPPED." may be causing this.

  • This seems to be the reason for our problems. In the bug report they provide a workaround by deleting unused records from the table. – Jens Schauder Apr 25 '12 at 16:07

Oracle inserts into warning_settings$ when you compile an object that produces a compilation warning.

I traced an object creation with (ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL') & without (ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL') warnings enabled to show this.

With warnings set, we can see the following SQL in the trace file:

PARSING IN CURSOR #5 len=94 dep=1 uid=0 oct=2 lid=0 tim=1334927684277374 hv=2385338619 ad='d891dc48' sqlid='fdvfrjy72us7v'
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
PARSE #5:c=0,e=250,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1334927684277373

Interestingly, ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL' only seems to suppress the output of the warnings to SQL Plus. A trace still shows the table being populated by Oracle:

oracle@ironforge:/u01/app/oracle/diag/rdbms/phill11g2/PHILL11G2/trace$ tail -f PHILL11G2_ora_26759_PHIL_DBA.trc | grep -i warning
delete from warning_settings$ where obj# = :1
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  WARNING_SETTINGS$ (cr=2 pr=0 pw=0 time=0 us)'
STAT #6 id=2 cnt=1 pid=1 pos=1 obj=254 op='INDEX RANGE SCAN I_WARNING_SETTINGS (cr=2 pr=0 pw=0 time=0 us cost=1 size=5 card=1)'
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)

Test PL/SQL snippet used for this & the warning it generated:

PHIL@PHILL11G2 > ALTER SESSION SET sql_trace = true;

Session altered.

PHIL@PHILL11G2 > ALTER SESSION SET tracefile_identifier = PHIL_DBA;

Session altered.


Session altered.

  2  IS
  3  BEGIN
  4    NULL;
  5  END;
  6  /

SP2-0804: Procedure created with compilation warnings

PHIL@PHILL11G2 > show errors;
Errors for PROCEDURE PHIL_1:

-------- -----------------------------------------------------------------
1/1  PLW-05018: unit PHIL_1 omitted optional AUTHID clause; default
     value DEFINER used


I can only find a reference to this in Oracle's TimesTen in-memory database docs, but it's clearly there in our 11.1 "plain" database. See here. It's listed as a PL/SQL system table, and there's a comment about "don't query this directly - use the PL/SQL system views instead".

Hmmm... Perhaps it contains foul, forbidden secrets, the kind which have been known to drive men mad. But...shall we turn our eyes from this knowledge? Are we not men?!?


Eyyaaaaaah! Cthulhu!! Cthulhu fthagn!!!

#&^$#&$^#*&NO CARRIER

  • Don't you know the SO motto is "We hate fun"? – APC Apr 19 '12 at 15:00
  • @APC: say it long and say it loud - we're funny, and it's not just looks! (OK, needs work...) – Bob Jarvis Apr 19 '12 at 15:24
  • I'm somewhat disappointed nobody caught the Mark Motherspaugh reference. <*sigh*> WE ARE DEVO! WE ARE DEVO!! WE ARE DEVO!!! – Bob Jarvis Apr 23 '12 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.