Script:以下脚本可以用于诊断SYSAUX表空间使用情况
./opatch lsinventory -detail
@?/rdbms/admin/awrinfo
select dbms_stats.get_stats_history_retention from dual;
select dbms_stats.get_stats_history_availability from dual;
select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_TAB_HISTORY;
select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_ind_history;
select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histhead_history;
select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histgrm_history;
select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_aux_history;
select count(*) from sys.wri$_optstat_tab_history;
select count(*) from sys.wri$_optstat_ind_history;
select count(*) from sys.wri$_optstat_histhead_history;
select count(*) from sys.wri$_optstat_histgrm_history;
select count(*) from sys.wri$_optstat_aux_history;
select count(*) from sys.wri$_optstat_opr;
示例输出:
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at
08:57:12 on Jun 01, 2012 ( Friday ) in Timezone -04:00
select count(*) from sys.wri$_optstat_aux_history;
select count(*) from sys.wri$_optstat_opr;
Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days