Код пакета pkg_partitions для случая включенного автоматического сбора статистики


Если вы анализируете статистику таблиц и используете автоматический сбор статистики с помощью dbms_stats, то у вас в data dictionary будут заполнены данные о количестве строк в секциях. Таким образом, задача определения не пустых последний секций значительно упрощается.
Ниже приведен полный текст моего пакета для данного случая.

  1. create or replace package pkg_partitions is
  2. -- Author : Саян Малакшинов
  3. -- Created : 30.09.2010
  4. -- Purpose : Partitions management
  5. type table_props is record (
  6.      table_name varchar2(4000),
  7.      table_owner varchar2(4000),
  8.      partitions_count number,
  9.      partition_name varchar2(4000));
  10. type tables_props_array is table of table_props;
  11. /** Функция возвращающая параметр для предпоследней секции
  12. * @param i_table_name Имя таблицы
  13. * @return varchar2
  14. */
  15. function get_penultimate_maxvalue(p_table_owner varchar2, p_table_name varchar2) return varchar2;
  16. /** Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция
  17. * @return
  18. *     table_name varchar2(4000),
  19. *     table_owner varchar2(4000),
  20. *     partitions_count number,
  21. *     partition_name varchar2(4000));
  22. */
  23. function get_maxvalued_partitions return tables_props_array pipelined;
  24. function get_maxvalued_partitions_html return varchar2;
  25. procedure send_partitions_report(mail varchar2);
  26. end pkg_partitions;
  27. /
  28. create or replace package body pkg_partitions is
  29. /** Функция возвращающая параметр для предпоследней секции
  30. * @param i_table_name Имя таблицы
  31. * @return varchar2
  32. */
  33. function get_penultimate_maxvalue(p_table_owner varchar2,p_table_name varchar2) return varchar2 is
  34.      l_cursor    integer default dbms_sql.open_cursor;
  35.      l_ignore    number;
  36.      l_long_val varchar2(4000);
  37.      l_long_len number;
  38.      l_buflen    number := 4000;
  39.      l_curpos    number := 0;
  40. begin
  41.      dbms_sql.parse( l_cursor,
  42.                      'select p.high_value
  43.                      from all_tab_partitions p
  44.                      where
  45.                      p.table_owner like :o
  46.                      and p.table_name like :x
  47.                      and p.partition_position=
  48.                          (
  49.                          select max(p1.partition_position)-1
  50.                          from all_tab_partitions p1
  51.                          where
  52.                          p.table_owner like :o
  53.                          and p1.table_name like :x
  54.                          )'
  55.                      ,
  56.                      dbms_sql.native );
  57.      dbms_sql.bind_variable( l_cursor, ':x', p_table_name );
  58.      dbms_sql.bind_variable( l_cursor, ':o', p_table_owner );
  59.      dbms_sql.define_column_long(l_cursor, 1);
  60.      l_ignore := dbms_sql.execute(l_cursor);
  61.      if (dbms_sql.fetch_rows(l_cursor)>0)
  62.      then
  63.         dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
  64.                                  l_long_val, l_long_len );
  65.      end if;
  66.      dbms_sql.close_cursor(l_cursor);
  67.      return l_long_val;
  68. end;
  69. /** Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция
  70. * @return
  71. *     table_name varchar2(4000),
  72. *     table_owner varchar2(4000),
  73. *     partitions_count number,
  74. *     partition_name varchar2(4000));
  75. */
  76. function get_maxvalued_partitions return tables_props_array pipelined is
  77.      l_cursor    integer default dbms_sql.open_cursor;
  78.      l_count     number;
  79.      l_ignore    integer;
  80.      l_data     table_props;
  81.      cursor l_partitions is
  82.         select
  83.          pl.table_owner,
  84.          pl.table_name,
  85.          count(1) cnt,
  86.          max(pl.num_rows) keep(dense_rank last order by (pl.partition_position)) partition_rows,
  87.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
  88.         from dba_tab_partitions pl
  89.         where pl.table_name not like 'BIN$%'
  90.         group by pl.table_owner,pl.table_name
  91.         having count(1)>1
  92.         and max(pl.num_rows) keep(dense_rank last order by (pl.partition_position))>1;
  93. BEGIN
  94.      FOR part in l_partitions
  95.         LOOP
  96.             l_data.table_name:=part.table_name;
  97.             l_data.table_owner:=part.table_owner;
  98.             l_data.partitions_count:=part.cnt;
  99.             l_data.partition_name:=part.partition_name;
  100.             pipe row(l_data);
  101.         END LOOP;
  102. END;
  103. /** Функция возвращающая названия таблиц и их владельцев в виде html, у которых начала заполняться последняя секция
  104. * @return
  105. *     table_name varchar2(4000),
  106. *     table_owner varchar2(4000),
  107. *     partitions_count number,
  108. *     partition_name varchar2(4000));
  109. */
  110. function get_maxvalued_partitions_html return varchar2 is
  111.      l_cursor    integer default dbms_sql.open_cursor;
  112.      l_count     number;
  113.      l_ignore    integer;
  114.      l_data     varchar2(4000);
  115.      cursor l_partitions is
  116.         select
  117.          pl.table_owner,
  118.          pl.table_name,
  119.          count(1) cnt,
  120.          max(pl.num_rows) keep(dense_rank last order by (pl.partition_position)) partition_rows,
  121.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
  122.         from dba_tab_partitions pl
  123.         where pl.table_name not like 'BIN$%'
  124.         group by pl.table_owner,pl.table_name
  125.         having count(1)>1
  126.         and max(pl.num_rows) keep(dense_rank last order by (pl.partition_position))>1;
  127. BEGIN
  128.      l_data:='<html><body><table border=1>'
  129.             ||'<tr><th>Table name</th>'
  130.             ||'<th>Table owner</th>'
  131.             ||'<th>Partitions count</th>'
  132.             ||'<th>Partition name</th>'
  133.             ||'<th>Pre maxvalue</th>';
  134.     
  135.      FOR part in l_partitions
  136.         LOOP
  137.             l_data:=l_data||'<tr><td>'
  138.                          ||part.table_name
  139.                          ||'</td><td>'
  140.                          ||part.table_owner
  141.                          ||'</td><td>'
  142.                          ||part.cnt
  143.                          ||'</td><td>'
  144.                          ||part.partition_name
  145.                          ||'</td></tr>';
  146.         END LOOP;
  147.      l_data:=l_data||'</table></body></html>';
  148.      return l_data;
  149. END;
  150. /**
  151. * Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция
  152. */
  153. procedure send_partitions_report(mail varchar2)
  154.     is
  155.      msg_body varchar2(4000);
  156.     BEGIN
  157.      select pkg_partitions.get_maxvalued_partitions_html into msg_body from dual;
  158.      --EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''our_mailserver''';
  159.      utl_mail.send(
  160.             sender => 'oracleDBA@dbdomain.com',
  161.             recipients => mail,
  162.             subject => 'Maxvalued partitions Report',
  163.             message => msg_body,
  164.             mime_type => 'text/html');
  165.     END;
  166. end pkg_partitions;
  167. /
* This source code was highlighted with Source Code Highlighter.

Comments

Отправка комментария