Home » RDBMS Server » Server Administration » SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) (Oracle Solaris 11.1.0.7)
SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #544719] Thu, 23 February 2012 07:59 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
All:

I see the following message in my alter log "SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000)"


Is there a query available that can identify plans that have
not been used in N number of days or a query/procedure that
can remove all NON active plans?? Or is this something I need
not worry about and just add the space and see if the problem
rectifies itself?

Thanks in advance to all who answer





Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #544720 is a reply to message #544719] Thu, 23 February 2012 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can either increase the SYSAUX tablespace size or change the limit you allow using (for instance):
EXEC DBMS_SPM.CONFIGURE('space_budget_percent',30);

Regards
Michel
SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #544729 is a reply to message #544720] Thu, 23 February 2012 09:17 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Michel,

Thanks for the prompt respone. You mention limit below, this is
just warning as per the documentauton and will not prevent
new PLAN from being added.

Secondly, what about removing old and unused SQL plans. Is this
not a viable approach?

[Updated on: Thu, 23 February 2012 09:20]

Report message to a moderator

Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #544736 is a reply to message #544729] Thu, 23 February 2012 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, if there are plans you know you will not use them you can delete them.

Regards
Michel
SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #546965 is a reply to message #544736] Sat, 10 March 2012 15:21 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Actually, I was looking to find plans that have not been used
in N number of days.. Is there some way I can get this information via a query or some other method?

This leads me to my next question. I suspect that there maybe
queries issued by my APP that using literal values instead of
all bind variables.

Is there some automated way (some query) I can verify this?

Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #555412 is a reply to message #546965] Thu, 24 May 2012 00:18 Go to previous messageGo to next message
rajsoft88
Messages: 10
Registered: April 2011
Location: delhi
Junior Member
Thanks, it worked for me.

I did as mentioned below:

SQL> alter database datafile 'e:\app\rajkumar\oradata\sysaux01.dbf' resize 8192M;
Databas Altered.

Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #555418 is a reply to message #555412] Thu, 24 May 2012 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How is this related to the question?

Regards
Michel
icon4.gif  Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #564635 is a reply to message #555418] Mon, 27 August 2012 04:17 Go to previous messageGo to next message
rajsoft88
Messages: 10
Registered: April 2011
Location: delhi
Junior Member
I am again getting the same alert entry in the log file as mentioned above as it didn't resolved by increasing the size of SYSAUX tablespace.

Thanks,
Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #564636 is a reply to message #564635] Mon, 27 August 2012 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you try the other option?

Regards
Michel
Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #564639 is a reply to message #564636] Mon, 27 August 2012 04:45 Go to previous messageGo to next message
rajsoft88
Messages: 10
Registered: April 2011
Location: delhi
Junior Member
Now just going to apply the other option and will let you know after restarting my database as it occurs at database instance startup.

Thanks,
Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #564641 is a reply to message #564636] Mon, 27 August 2012 04:55 Go to previous messageGo to next message
rajsoft88
Messages: 10
Registered: April 2011
Location: delhi
Junior Member
Thanks a lot Michel,

The erros is resolved by your second option mentioned below:

EXEC DBMS_SPM.CONFIGURE('space_budget_percent',30);

after executing this procedure successfully I restarted my oracle database instance and now the alert entry is no longer there.


Thanks,
RajKumar
Re: SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #564642 is a reply to message #564641] Mon, 27 August 2012 05:42 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: non-expiring SYSTEM and SYS password
Next Topic: Hash_Area_Size Unit in "show parameter"
Goto Forum:
  


Current Time: Fri Mar 29 09:02:57 CDT 2024