点击(此处)折叠或打开
-
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;