点击(此处)折叠或打开
- DECLARE
- v_tbs_name varchar2(30);
- v_tbs_size number;
- v_auto_extend boolean;
- v_undo_retention number;
- v_retention_guarantee boolean;
-
- v_problem varchar2(1000);
- v_recommendation varchar2(1000);
- v_rationale varchar2(1000);
- v_rec_undo_retention number;
- v_rec_tbs_size number;
-
- v_autotune_enable boolean;
-
- v_return_boolean boolean;
- v_return_number number;
- v_return_varchar varchar2(1000);
- BEGIN
- --undo info
- v_return_boolean:=dbms_undo_adv.undo_info(table_space_name => v_tbs_name,
- table_space_size => v_tbs_size,
- auto_extend => v_auto_extend,
- undo_retention => v_undo_retention,
- retention_guarantee => v_retention_guarantee);
- dbms_output.put_line('table_space_name:'||v_tbs_name);
- dbms_output.put_line('table_space_size:'||v_tbs_size);
- dbms_output.put_line('auto_extend:'||case when v_auto_extend then 'YES' else 'NO' end);
- dbms_output.put_line('undo_retention:'||v_undo_retention);
- dbms_output.put_line('retention_guarantee:'||case when v_retention_guarantee then 'YES' else 'NO' end);
-
- --以下函数都可以加上时间范围,awr快照范围
- --required_undo_size
- v_return_number := dbms_undo_adv.required_undo_size(retention => v_undo_retention);
- dbms_output.put_line('required_undo_size:'||v_return_number);
- --undo_health
- v_return_number := dbms_undo_adv.undo_health(problem => v_problem,
- recommendation => v_recommendation,
- rationale => v_rationale,
- retention => v_rec_undo_retention,
- utbsize => v_rec_tbs_size);
- dbms_output.put_line('problem:'||v_problem);
- dbms_output.put_line('recommendation:'||v_recommendation);
- dbms_output.put_line('rationale:'||v_rationale);
- dbms_output.put_line('retention:'||v_rec_undo_retention);
- dbms_output.put_line('utbsize:'||v_rec_tbs_size);
- --undo_advisor
- v_return_varchar := dbms_undo_adv.undo_advisor(instance => 1);
- dbms_output.put_line('undo_advisor:'||v_return_varchar);
- --autotune enable
- v_return_boolean := dbms_undo_adv.undo_autotune(v_autotune_enable);
- dbms_output.put_line('undo_autotune:'||case when v_autotune_enable then 'YES' else 'NO' end);
- --longest_query
- v_return_number := dbms_undo_adv.longest_query;
- dbms_output.put_line('longest_query:'||v_return_number);
- --required_retention
- v_return_number := dbms_undo_adv.required_retention;
- dbms_output.put_line('required_retention:'||v_return_number);
- END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2113778/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28539951/viewspace-2113778/