OraFAQ Forum: Performance Tuning » Size of SYSAUX is getting bigger

Home » RDBMS Server » Performance Tuning » Size of SYSAUX is getting bigger (Oracle 10g R2 10.2.0.4)

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

Size of SYSAUX is getting bigger [message #648724]

Wed, 02 March 2016 00:47

snehalgandhi
Messages: 43
Registered: February 2011
Location: Ahmedabad

Member

I am using Oracle 10gR2 10.2.0.4 on Linux platform.
My SysAux tablespace is constantly increasing and i have checked v$sysAux_occupants, SM/AWR is taking high space.

Also checking DBA_Segments i found WRH$_SQL_PLAN and WRH$_SQLTEXT is occupying large space.
Is there any way to shrink dba_hist_sql_plan; & dba_hist_sqltext;

Whether i will be able to shrink the sysaux tablespace

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648725 is a reply to message #648724]

Wed, 02 March 2016 01:22

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

Senior Member
Account Moderator

http://www.orafaq.com/forum/m/648713/?srch=SYSAUX#msg_648713

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648727 is a reply to message #648724]

Wed, 02 March 2016 01:26

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

You remove the AWR with the catnoawr.sql script, drop and replace the tblespace, and then run catawr.sql.

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648729 is a reply to message #648725]

Wed, 02 March 2016 01:27

snehalgandhi
Messages: 43
Registered: February 2011
Location: Ahmedabad

Member

Dear Michel,
I have gone through the link, by executing drop_snapshot_range it does not reduces space, to manually shrink the tables
Can i execute
truncate table dba_hist_sql_plan;

truncate table dba_hist_sqltext;

Will it work.

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648731 is a reply to message #648729]

Wed, 02 March 2016 01:32

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

Senior Member
Account Moderator

Quote:

Can i execute

No, as I said, you have to work on the underlying tables, not on the views.
But if you want to empty AWR you can follow John's solution.

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648734 is a reply to message #648731]

Wed, 02 March 2016 01:34

snehalgandhi
Messages: 43
Registered: February 2011
Location: Ahmedabad

Member

But this post says to truncate.

http://www.dba-oracle.com/t_purge_awr_statistics.htm

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648735 is a reply to message #648734]

Wed, 02 March 2016 01:39

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

So try it. You have nothing to lose except your database. I would raise a TAR with Support first.

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648737 is a reply to message #648734]

Wed, 02 March 2016 01:43

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

Senior Member
Account Moderator

This site is b......t, forget it.

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648739 is a reply to message #648737]

Wed, 02 March 2016 01:55

snehalgandhi
Messages: 43
Registered: February 2011
Location: Ahmedabad

Member

I am not in a position to loose my DB.
Please guide to work on the underlying tables and shrinking them, instead of the dba_hist_sql_Plan & dba_hist_SqlText

Report message to a moderator

Re: Size of SYSAUX is getting bigger [message #648741 is a reply to message #648739]

Wed, 02 March 2016 03:17

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

Senior Member
Account Moderator

Shrinking SYS tables, if possible, is not safe, so you anyway have to first test on a test db.

[Updated on: Wed, 02 March 2016 03:17]

Report message to a moderator

Previous Topic:	Select with Union Performance issue
Next Topic:	Query Optimization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 22:03:49 CST 2024