Home » RDBMS Server » Performance Tuning » Size of SYSAUX is getting bigger (Oracle 10g R2 10.2.0.4)
Size of SYSAUX is getting bigger [message #648724] Wed, 02 March 2016 00:47 Go to next message
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
Re: Size of SYSAUX is getting bigger [message #648725 is a reply to message #648724] Wed, 02 March 2016 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Size of SYSAUX is getting bigger [message #648727 is a reply to message #648724] Wed, 02 March 2016 01:26 Go to previous messageGo to next message
John Watson
Messages: 7262
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.
Re: Size of SYSAUX is getting bigger [message #648729 is a reply to message #648725] Wed, 02 March 2016 01:27 Go to previous messageGo to next message
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.
Re: Size of SYSAUX is getting bigger [message #648731 is a reply to message #648729] Wed, 02 March 2016 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Size of SYSAUX is getting bigger [message #648734 is a reply to message #648731] Wed, 02 March 2016 01:34 Go to previous messageGo to next message
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
Re: Size of SYSAUX is getting bigger [message #648735 is a reply to message #648734] Wed, 02 March 2016 01:39 Go to previous messageGo to next message
John Watson
Messages: 7262
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.
Re: Size of SYSAUX is getting bigger [message #648737 is a reply to message #648734] Wed, 02 March 2016 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Size of SYSAUX is getting bigger [message #648739 is a reply to message #648737] Wed, 02 March 2016 01:55 Go to previous messageGo to next message
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
Re: Size of SYSAUX is getting bigger [message #648741 is a reply to message #648739] Wed, 02 March 2016 03:17 Go to previous message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Thu Feb 22 14:43:38 CST 2018

Total time taken to generate the page: 0.01742 seconds