| SYSAUX tablespace getting 99% [message #655408] |
Tue, 30 August 2016 12:12  |
 |
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  |
John Watson
Messages: 8960
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'; |
|
|
|
|
|
|
|
|
|
|
|
| Re: SYSAUX tablespace getting 99% [message #657259 is a reply to message #655427] |
Thu, 03 November 2016 07:14 |
 |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
