Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

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

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

3 Answers

up vote 2 down vote accepted

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

share|improve this answer
    
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
add comment

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

share|improve this answer
    
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
1  
+1 for Cthulhu reference. Fits Oracle nicely. –  Jens Schauder Apr 19 '12 at 16:18
    
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
add comment

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 > 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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