Home » RDBMS Server » Server Administration » SYSAUX tablespace getting 99% (Oracle Database 11g Enterprise Edition ,11.1.0.7.0, linux64_86)
SYSAUX tablespace getting 99% [message #655408] Tue, 30 August 2016 12:12 Go to next message
abdo2203
Messages: 22
Registered: August 2016
Junior Member
Hi ,
the tablspace SYSAUX reached the 99% , and i am afraid if there are consequences of getting full
can you please help ?
regards
Re: SYSAUX tablespace getting 99% [message #655410 is a reply to message #655408] Tue, 30 August 2016 12:22 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
As an immediate measure,
select 'alter database datafile '''||file_name||''' autoextend on;' from dba_data_files where tablespace_name='SYSAUX';
that will buy you time to sort it out properly.
Re: SYSAUX tablespace getting 99% [message #655411 is a reply to message #655410] Tue, 30 August 2016 12:52 Go to previous messageGo to next message
abdo2203
Messages: 22
Registered: August 2016
Junior Member
thank you John did it need commit or not ?
Re: SYSAUX tablespace getting 99% [message #655412 is a reply to message #655411] Tue, 30 August 2016 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
DDL does COMMIT both before & after DDL executes.
Re: SYSAUX tablespace getting 99% [message #655413 is a reply to message #655408] Tue, 30 August 2016 13:26 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

The next thing to do is to figure out what is taking up the space. Query V$SYSAUX_OCCUPANTS. One of the columns shows you how much space is used by each component. Sort on this column.

HTH,
Brian
Re: SYSAUX tablespace getting 99% [message #655427 is a reply to message #655411] Wed, 31 August 2016 02:47 Go to previous messageGo to next message
gazzag
Messages: 909
Registered: November 2010
Location: Bristol, UK
Senior Member
COMMIT is only required after DML (INSERT, UPDATE, DELETE, etc.), not DDL.
Re: SYSAUX tablespace getting 99% [message #657259 is a reply to message #655427] Thu, 03 November 2016 07:14 Go to previous messageGo to next message
naveen_rc2000@yahoo.com
Messages: 7
Registered: November 2016
Location: India
Junior Member
May be we can follow the steps to identify and fix the issue :

Step 1 : Run the following query

col "Space (M)" for 999,999.99
SELECT occupant_name, round( space_usage_kbytes/1024) "Space (M)", schema_name, move_procedure FROM v$sysaux_occupants ORDER BY 1

Step 2 : Check the Occupant Name occupying more space, normally SM/AWR will be and if it the case you can follow the steps to remove the Old Snapshots

a) retrieve the oldest and latest AWR snapshot

SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/

Based on the result use the dbms_workload_repository package to remove the AWR snapshots

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => <start Snapshot ID>, high_snap_id=> <EndSnapshot ID>);
END;
/

Speed up 'removal' of old AWR reports
removing the entries takes ages and fails on undo errors ... Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.
If none of the above suits as everything is set proper then consider clean up and rebuild AWR repository to clear all the space.
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

Re: SYSAUX tablespace getting 99% [message #657260 is a reply to message #657259] Thu, 03 November 2016 07:28 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: SYSAUX tablespace getting 99% [message #657261 is a reply to message #657259] Thu, 03 November 2016 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is just a copy of the following page:
http://remidian.com/2009/08/purging-sysaux-tablespace-purging-awr-reports/
(or maybe you copy it from some other blogs which are copies of this page)

Is your blog you ask us to read in your profile just copies of others pages?

When you copy something from someone the least honesty and fairness would be to give the link instead of behaving like it is from you.
And the correct behavior would be to just post the link and not the content unless you have the permission of the author.

Re: SYSAUX tablespace getting 99% [message #657262 is a reply to message #657260] Thu, 03 November 2016 08:21 Go to previous messageGo to next message
naveen_rc2000@yahoo.com
Messages: 7
Registered: November 2016
Location: India
Junior Member
Dear Michel,

I am sorry you are right to give as reference.

However it's not my intention to say it as my own composition.

I gave documented this issue an hour ago and posted it as it is.

Thought it might be related.

Anyway I welcome your suggestion.
Re: SYSAUX tablespace getting 99% [message #657263 is a reply to message #657262] Thu, 03 November 2016 08:25 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
BTW, the OP has been absent from this forum for more than 1 month.
I suspect OP no longer cares about any solution to this situation, but it might assist other folks in the future.
Re: SYSAUX tablespace getting 99% [message #657267 is a reply to message #657263] Thu, 03 November 2016 10:25 Go to previous message
abdo2203
Messages: 22
Registered: August 2016
Junior Member
Thank you all for your contribution

the problem solved but you advices help me next time

Rgards
Previous Topic: alter table ... enable row movement
Next Topic: ORA-4031
Goto Forum:
  


Current Time: Sat Feb 24 17:56:18 CST 2018

Total time taken to generate the page: 0.01432 seconds