获取指定用户和表的统计信息

create or replace procedure get_stat(i_owner in varchar2,
                                       i_table in varchar2 default null
                                     )
is
v_owner varchar2(40);
v_table varchar2(4000);
type cur_type is ref cursor;
stat_all_cur cur_type;
stat_sys_cur cur_type;
stat_task_cur cur_type;
stat_cur cur_type;
stat_rec all_tab_statistics%rowtype;
begin
 DBMS_OUTPUT.ENABLE(buffer_size => null);
 v_owner :=upper(i_owner);
 v_table :=replace(upper(i_table),chr(44),chr(39)||chr(44)||chr(39));
-- dbms_output.put_line(v_owner);
-- dbms_output.put_line(v_table);
/*
 open stat_cur for 'select * from all_tab_statistics where owner=:owner and table_name in :i_table'
 using v_owner,v_table;
*/
 if v_owner ='SYS_USERS' then
  open stat_sys_cur for 'select * from all_tab_statistics ats where ats.owner like '||chr(39)||'SYS%'||chr(39)||' or ats.owner like '||chr(39)||'%SYS'||chr(39)||' order by ats.owner';
   loop
    fetch stat_sys_cur into stat_rec;
    exit when stat_sys_cur%notfound;
--    dbms_output.put_line(stat_rec.owner||' '||stat_rec.table_name||' '||stat_rec.num_rows||' '||stat_rec.blocks||' '||stat_rec.object_type||' '||stat_rec.LAST_ANALYZED);
--    dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,7)||' '||rpad(stat_rec.blocks,7)||' '||rpad(stat_rec.object_type,12)||' '||rpad(stat_rec.LAST_ANALYZED,9));
--   dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,30)||' '||rpad(stat_rec.blocks,30)||' '||rpad(stat_rec.object_type,30)||' '||rpad(stat_rec.LAST_ANALYZED,30));
   dbms_output.put_line(rpad(coalesce(stat_rec.owner,chr(32)),30)||' '||rpad(coalesce(stat_rec.table_name,chr(32)),30)||' '||rpad(coalesce(to_char(stat_rec.num_rows),chr(32)),7)||' '||rpad(coalesce(to_char(stat_rec.blocks),chr(32)),7)||' '||rpad(coalesce(stat_rec.object_type,chr(32)),12)||' '||rpad(coalesce(to_char(stat_rec.LAST_ANALYZED),chr(32)),10));
   end loop;
  close stat_sys_cur;
 elsif v_owner ='ALL_USERS' then
  open stat_all_cur for 'select * from all_tab_statistics order by owner';
   loop
    fetch stat_all_cur into stat_rec;
    exit when stat_all_cur%notfound;
--    dbms_output.put_line(stat_rec.owner||' '||stat_rec.table_name||' '||stat_rec.num_rows||' '||stat_rec.blocks||' '||stat_rec.object_type||' '||stat_rec.LAST_ANALYZED);
--    dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,7)||' '||rpad(stat_rec.blocks,7)||' '||rpad(stat_rec.object_type,12)||' '||rpad(stat_rec.LAST_ANALYZED,9));
--    dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,30)||' '||rpad(stat_rec.blocks,30)||' '||rpad(stat_rec.object_type,30)||' '||rpad(stat_rec.LAST_ANALYZED,30));
   dbms_output.put_line(rpad(coalesce(stat_rec.owner,chr(32)),30)||' '||rpad(coalesce(stat_rec.table_name,chr(32)),30)||' '||rpad(coalesce(to_char(stat_rec.num_rows),chr(32)),7)||' '||rpad(coalesce(to_char(stat_rec.blocks),chr(32)),7)||' '||rpad(coalesce(stat_rec.object_type,chr(32)),12)||' '||rpad(coalesce(to_char(stat_rec.LAST_ANALYZED),chr(32)),10));
   end loop;
  close stat_all_cur;
 elsif v_owner ='TASK_USERS' then
  open stat_task_cur for 'select * from all_tab_statistics ats where ats.owner not like '||chr(39)||'%SYS'||chr(39)||' and ats.owner not like '||chr(39)||'SYS%'||chr(39)||' order by ats.owner';
   loop
    fetch stat_task_cur into stat_rec;
    exit when stat_task_cur%notfound;
--    dbms_output.put_line(stat_rec.owner||' '||stat_rec.table_name||' '||stat_rec.num_rows||' '||stat_rec.blocks||' '||stat_rec.object_type||' '||stat_rec.LAST_ANALYZED);
--    dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,7)||' '||rpad(stat_rec.blocks,7)||' '||rpad(stat_rec.object_type,12)||' '||rpad(stat_rec.LAST_ANALYZED,9));
--    dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,30)||' '||rpad(stat_rec.blocks,30)||' '||rpad(stat_rec.object_type,30)||' '||rpad(stat_rec.LAST_ANALYZED,30));
   dbms_output.put_line(rpad(coalesce(stat_rec.owner,chr(32)),30)||' '||rpad(coalesce(stat_rec.table_name,chr(32)),30)||' '||rpad(coalesce(to_char(stat_rec.num_rows),chr(32)),7)||' '||rpad(coalesce(to_char(stat_rec.blocks),chr(32)),7)||' '||rpad(coalesce(stat_rec.object_type,chr(32)),12)||' '||rpad(coalesce(to_char(stat_rec.LAST_ANALYZED),chr(32)),10));
   end loop;
  close stat_task_cur;
 else
 open stat_cur for 'select * from all_tab_statistics where owner='||chr(39)||v_owner||chr(39)||' and table_name in ('||chr(39)||v_table||chr(39)||')';
 loop
  fetch stat_cur into stat_rec;
  exit when stat_cur%notfound;
--  dbms_output.put_line(stat_rec.owner||' '||stat_rec.table_name||' '||stat_rec.num_rows||' '||stat_rec.blocks||' '||stat_rec.object_type||' '||stat_rec.LAST_ANALYZED);
--  dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,7)||' '||rpad(stat_rec.blocks,7)||' '||rpad(stat_rec.object_type,12)||' '||rpad(stat_rec.LAST_ANALYZED,9));
--  dbms_output.put_line(rpad(stat_rec.owner,30)||' '||rpad(stat_rec.table_name,30)||' '||rpad(stat_rec.num_rows,30)||' '||rpad(stat_rec.blocks,30)||' '||rpad(stat_rec.object_type,30)||' '||rpad(stat_rec.LAST_ANALYZED,30));
   dbms_output.put_line(rpad(coalesce(stat_rec.owner,chr(32)),30)||' '||rpad(coalesce(stat_rec.table_name,chr(32)),30)||' '||rpad(coalesce(to_char(stat_rec.num_rows),chr(32)),7)||' '||rpad(coalesce(to_char(stat_rec.blocks),chr(32)),7)||' '||rpad(coalesce(stat_rec.object_type,chr(32)),12)||' '||rpad(coalesce(to_char(stat_rec.LAST_ANALYZED),chr(32)),10));
 end loop;
  close stat_cur;
 end if;
end;

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

转载于:http://blog.itpub.net/29802484/viewspace-1850707/