How to housekeeping SYSAUX tablespace

DBA Notes: 2012/01/19

Cheng Li

(Database: Oracle 10g, OS: Solaris)

 

How to housekeeping SYSAUX tablespace

 

Following Steps to perform. SYSAUX tablespace

SELECT * FROM dba_jobs WHERE owner = '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%';

 

SELECT 'exec em_severity.delete_current_severity(''' || target_guid ||

''',''' || metric_guid || ''',''' || key_value || ''');'

FROM sysman.MGMT_SEVERITY

 

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

 

 

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'

 

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

Reference:

http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26136400/viewspace-715137/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26136400/viewspace-715137/