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

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

create or replace package pkg_partitions is -- Author : Саян Малакшинов -- Created : 30.09.2010 -- Purpose : Partitions management type table_props is record ( table_name varchar2(4000), table_owner varchar2(4000), partitions_count number, partition_name varchar2(4000)); type tables_props_array is table of table_props; /** Функция возвращающая параметр для предпоследней секции * @param i_table_name Имя таблицы * @return varchar2 */ function get_penultimate_maxvalue(p_table_owner varchar2, p_table_name varchar2) return varchar2; /** Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция * @return * table_name varchar2(4000), * table_owner varchar2(4000), * partitions_count number, * partition_name varchar2(4000)); */ function get_maxvalued_partitions return tables_props_array pipelined; function get_maxvalued_partitions_html return varchar2; procedure send_partitions_report(mail varchar2); end pkg_partitions; / create or replace package body pkg_partitions is /** Функция возвращающая параметр для предпоследней секции * @param i_table_name Имя таблицы * @return varchar2 */ function get_penultimate_maxvalue(p_table_owner varchar2,p_table_name varchar2) return varchar2 is l_cursor integer default dbms_sql.open_cursor; l_ignore number; l_long_val varchar2(4000); l_long_len number; l_buflen number := 4000; l_curpos number := 0; begin dbms_sql.parse( l_cursor, 'select p.high_value from all_tab_partitions p where p.table_owner like :o and p.table_name like :x and p.partition_position= ( select max(p1.partition_position)-1 from all_tab_partitions p1 where p.table_owner like :o and p1.table_name like :x )' , dbms_sql.native ); dbms_sql.bind_variable( l_cursor, ':x', p_table_name ); dbms_sql.bind_variable( l_cursor, ':o', p_table_owner ); dbms_sql.define_column_long(l_cursor, 1); l_ignore := dbms_sql.execute(l_cursor); if (dbms_sql.fetch_rows(l_cursor)>0) then dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len ); end if; dbms_sql.close_cursor(l_cursor); return l_long_val; end; /** Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция * @return * table_name varchar2(4000), * table_owner varchar2(4000), * partitions_count number, * partition_name varchar2(4000)); */ function get_maxvalued_partitions return tables_props_array pipelined is l_cursor integer default dbms_sql.open_cursor; l_count number; l_ignore integer; l_data table_props; cursor l_partitions is select pl.table_owner, pl.table_name, count(1) cnt, max(pl.num_rows) keep(dense_rank last order by (pl.partition_position)) partition_rows, max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name from dba_tab_partitions pl where pl.table_name not like 'BIN$%' group by pl.table_owner,pl.table_name having count(1)>1 and max(pl.num_rows) keep(dense_rank last order by (pl.partition_position))>1; BEGIN FOR part in l_partitions LOOP l_data.table_name:=part.table_name; l_data.table_owner:=part.table_owner; l_data.partitions_count:=part.cnt; l_data.partition_name:=part.partition_name; pipe row(l_data); END LOOP; END; /** Функция возвращающая названия таблиц и их владельцев в виде html, у которых начала заполняться последняя секция * @return * table_name varchar2(4000), * table_owner varchar2(4000), * partitions_count number, * partition_name varchar2(4000)); */ function get_maxvalued_partitions_html return varchar2 is l_cursor integer default dbms_sql.open_cursor; l_count number; l_ignore integer; l_data varchar2(4000); cursor l_partitions is select pl.table_owner, pl.table_name, count(1) cnt, max(pl.num_rows) keep(dense_rank last order by (pl.partition_position)) partition_rows, max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name from dba_tab_partitions pl where pl.table_name not like 'BIN$%' group by pl.table_owner,pl.table_name having count(1)>1 and max(pl.num_rows) keep(dense_rank last order by (pl.partition_position))>1; BEGIN l_data:='<html><body><table border=1>' ||'<tr><th>Table name</th>' ||'<th>Table owner</th>' ||'<th>Partitions count</th>' ||'<th>Partition name</th>' ||'<th>Pre maxvalue</th>'; FOR part in l_partitions LOOP l_data:=l_data||'<tr><td>' ||part.table_name ||'</td><td>' ||part.table_owner ||'</td><td>' ||part.cnt ||'</td><td>' ||part.partition_name ||'</td></tr>'; END LOOP; l_data:=l_data||'</table></body></html>'; return l_data; END; /** * Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция */ procedure send_partitions_report(mail varchar2) is msg_body varchar2(4000); BEGIN select pkg_partitions.get_maxvalued_partitions_html into msg_body from dual; --EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''our_mailserver'''; utl_mail.send( sender => 'oracleDBA@dbdomain.com', recipients => mail, subject => 'Maxvalued partitions Report', message => msg_body, mime_type => 'text/html'); END; end pkg_partitions; / * This source code was highlighted with Source Code Highlighter.