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

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

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

* This source code was highlighted with Source Code Highlighter.