Why keeps Oracle writing into WARNING_SETTINGS$?

Question

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

WARNING_SETTINGS$

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

begin
  dbms_warning.add_warning_setting_cat('ALL', 'DISABLE', 'SYSTEM');
end;
/
commit;

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?

Answer 1

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

Answer 2

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?!?

(tap-tap-tap-tap)

Eyyaaaaaah! Cthulhu!! Cthulhu fthagn!!!

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

Answer 3

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)
END OF STMT
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
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'
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.

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

PHIL@PHILL11G2 > CREATE OR REPLACE PROCEDURE phil_1
  2  IS
  3  BEGIN
  4    NULL;
  5  END;
  6  /

SP2-0804: Procedure created with compilation warnings

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

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

PHIL@PHILL11G2 >