Following Steps to perform. SYSAUX tablespace


SELECT *  FROM dba_jobs WHERE SCHEMA_USER='SYSMAN';

SELECT *  FROM SYSMAN.mgmt_metrics_1hour;

SELECT *  FROM SYSMAN.MGMT_SEVERITY WHERE LOAD_TIMESTAMP<TRUNC(ADD_MONTHS(SYSDATE,-2));

DELETE FROM SYSMAN.MGMT_SEVERITY WHERE LOAD_TIMESTAMP<TRUNC(ADD_MONTHS(SYSDATE,-2));
COMMIT;

SELECT trigger_name FROM dba_triggers WHERE trigger_name LIKE '%SEV%DEL%';

spool c:\del_severity.sql
SELECT 'exec sysman.em_severity.delete_current_severity('''|| target_guid || ''','''|| metric_guid ||''','''|| key_value ||''');' FROM sysman.MGMT_SEVERITY;
spool off
@c:\del_severity.sql
commit;

-------------
EXEC em_severity.delete_current_severity('142E273EE3BDA54ECF9C42EF7CCB7616','6E65075DA52ACA744B4B8C3FCB018289','/billing01');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','10633143D11A8FCF6CB1A732CEE5352A','Sat Dec 31 20:03:38 2011/115843');
COMMIT;

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','10633143D11A8FCF6CB1A732CEE5352A','Sat Dec 31 20:47:33 2011/134506');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','3E6F70DB22758B7B9756EF342180E7BB','SYSAUX');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','F95BA0D95585002889E1ABF92B2DA7C3','ARBORAD');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','F95BA0D95585002889E1ABF92B2DA7C3','SYSMAN');

COMMIT;
-----------------------------------
SELECT s.target_guid,
           s.metric_guid,
           s.key_value
       FROM mgmt_targets t JOIN mgmt_current_severity s
           ON s.target_guid != t.target_guid
      WHERE LOWER(t.target_name) LIKE '%myhost%';
     

SELECT DBMS_STATS.get_stats_history_retention FROM dual;
EXEC DBMS_STATS.alter_stats_history_retention(15);
EXEC DBMS_STATS.PURGE_STATS(SYSDATE-15);

col Mb FORM 9,999,999
col SEGMENT_NAME FORM a40
col SEGMENT_TYPE FORM a6
SET lines 120
SELECT SUM(bytes/1024/1024)Mb,segment_name,segment_type FROM dba_segments
 WHERE  tablespace_name='SYSAUX'
AND segment_name LIKE 'WRI$_OPTSTAT%'
AND segment_type='TABLE'
GROUP BY segment_name,segment_type ORDER BY 1 ASC;


spool c:\rebuild_index.sql
SELECT 'alter index '||segment_name||' rebuild online parallel (degree 14);' FROM dba_segments WHERE tablespace_name='SYSAUX'
AND segment_name LIKE '%OPT%' AND segment_type='INDEX';
spool off
@c:\rebuild_index.sql


SELECT *  FROM dba_indexes WHERE owner='SYS' AND status!='VALID';