OraFAQ Forum: Server Administration » SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000)

Home » RDBMS Server » Server Administration » SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) (Oracle Solaris 11.1.0.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SPM: SMB space usage (1369833472) exceeds 10.000000% of SYSAUX size (4194304000) [message #544719]

Thu, 23 February 2012 07:59

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, if there are plans you know you will not use them you can delete them.

Regards
Michel

Report message to a moderator

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

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?

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How is this related to the question?

Regards
Michel

Report message to a moderator

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

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,

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And did you try the other option?

Regards
Michel

Report message to a moderator

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

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,

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 14:28:41 CST 2024