Sectioning Auto add sections

In version 11g, Oracle has several new remarkable partitioning schemes — for example, convenient interval partitioning functionality — automatic section creation as the range leaves the specified boundaries.
In versions up to 11g, you must periodically manually either add sections in advance or split the default section. That is, it is constantly necessary to monitor the status of such tables. In this article, I will share my solutions for automating such partitioning tasks.
First give an example for 11g:

1. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
2. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
3. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
4. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
5. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
6. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
7. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
8. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
9. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
10. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
11. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );

This script creates a section p1 for records whose column value of res_id is in the range of 1-100. When records with a res_id column value less than 101 are inserted, they are placed in section p1, and when in the new record the value of this column is equal to or greater than 101, Oracle Database 11g creates a new section, the name of which is generated by the system. You can learn more about this example and other new partitioning schemes in the translation of the article by Arup Nanda in the Russian edition of Oracle Magazine .
The solutions discussed below can be applied to other DBMSs that do not support automatic addition of sections.

Solution for a uniformly increasing partitioning key without gaps

Create a test table:

1. create table test_part (
2. id number not null
3. name varchar2 (100) not null ,
4. owner varchar2 (100) not null ,
5. type varchar2 (100) not null ,
6. created date not null ,
7. constraint test_part_pk
8. primary key (id)
9. )
10. partition by range (id) (partition p1 values less than (10000));

It is logical that if there are no gaps in such a table, it would be desirable to create new sections before the partition key approaches the border of the maximum section. How many key values ​​we have left up to the border, we can easily determine according to a simple formula: partition_size - (key-start_key_in_partition) , where key is the current partitioning key, start_key_in_partition is the first key that falls into this section, partition_size is the number of keys in the section, and% is an integer division (div) operation. Typically, such a partitioning is made into equal sections, and with this in mind, we can simplify this formula to this: partition_size - key% partition_size.
What it gives us: knowing the moment we can create a trigger that will add sections when this event occurs.
Create this trigger:

1. create or replace trigger tr_test_part
2. before insert on test_part
3. for each row
4. when (mod ( NEW .id, 10000) = 6000)
5. declare
6. l_part_name number;
7. l_maxvalue number;
8. l_exist number;
9. l_partition_exists exception ;
10. PRAGMA EXCEPTION_INIT (l_partition_exists, -14074);
11. PRAGMA AUTONOMOUS_TRANSACTION;
12. begin
13. l_part_name: = ceil (: NEW .ID / 10000) +1;
14. BEGIN
15. execute immediate alter table xtender.test_part add partition p ||| l_part_name || 'values ​​less than (' || l_maxvalue || ')' ;
16. EXCEPTION
17. when l_partition_exists then null ;
18. END ;
19. end tr_test_part;

This trigger using autonomous transactions automatically creates a new section named 'P' + a section number of 10,000 in size when the ID - our partition key - remains 4,000 values ​​before the section boundary (10,000–4,000 = 6000, i.e. ID = 6000, 16000.26000, and so on.), But first it is checked whether this section already exists (this can happen, for example, when you add the 6000th record again, or manually add a section). The partitioning parameters are 10,000 and 4,000, you should select based on your specific situation, but note that the border (4,000 in the example) must be greater than the maximum number of records that are added at once. otherwise, at the time of the insert data transaction, the transaction will not be “aware” of the new section, since it did not exist at the beginning of the transaction, therefore the data will not be inserted with a complaint about the absence of a section mapping to this key. This could have been avoided with the use of alter table split default_partition , which I will discuss further, but this will affect the runtime.
Check our trigger by filling out the section:

insert into xtender.test_part
select rownum, o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, o.CREATED
from all_objects o
where rownum<1000;


In addition, in the case of using sequences that, due to caching, “step out”, the trigger cannot be consistently changed so that it is executed for a set of values ​​from 4000 to 3900 records from the end of the section:
replace the condition

  when (mod (NEW.id, 10000) = 6000) 

on

  when (mod (NEW.id, 10000) between 6000 and 6100) 

Solution for other cases

In cases where we specify a section by default, we can split it when records have already got there, the question is how to automatically track this.
')
In the Data dictionary, we can get information about all sections of partitioned tables by sampling from dba_tab_partitions , in which partition_position indicates the order of the section in the table, and high_value indicates the section parameters. Therefore, we can get the name of the last section in the table and select from it to get the number of records in it.

After receiving information about the tables in which writing into the default sections began, we should send a notification. For this we can use the options:

• if you have set up automatic sending of alerts by mail, then just write the event in alert.log;
• just write a procedure for sending notification emails.

The first option is implemented using the dbms_system.ksdwrt procedure, which has two parameters:

• the first (BINARY_INTEGER) is where to record, with possible values: 1 - to the standard trace file, 2 - to alert.log, 3 - to both;
• and the second (varchar2) is the string itself, which we write.

Example:

 exec dbms_system.ksdwrt (2, 'Test Alert Message');

The second option is to use the utl_mail package or lower-level ones - utl_smtp or utl_tcp .
utl_mail is a more convenient wrapper for utl_smtp , but to use it, it is necessary to set the smtp_out_server parameter. You can do this as soon as for the session - “ALTER SESSION SET smtp_out_server = ...” and for the “ALTER SYSTEM SET smtp_out_server = ...” system .
Do not be surprised if you cannot find this package in your home - initially it is not included and to create it you must execute two scripts:

 sqlplus sys/<pwd> SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb 

Putting it all together in the pkg_partitions package
Package methods:

• function get_penultimate_maxvalue (p_table_owner varchar2, p_table_name varchar2) return varchar2;
  The function accepts the owner and table name as parameters and returns the condition value (high_value) of the last but one section. This information may be needed, for example, in cases where the last section is a section with a maxvalue parameter, and, accordingly, the parameter of the penultimate section may be needed to define a parameter for a new section.
  
• function get_maxvalued_partitions return tables_props_array pipelined;
  The function returns the names of the tables and their owners, who have started filling out the last section.
  Example of use:
  

  1. select
  2. p. *,
  3. sys.pkg_partitions.get_penultimate_maxvalue (p.table_owner, p.table_name) pre_maxvalue
  4. from
  5. table (sys.pkg_partitions.get_maxvalued_partitions) p

  
  
• function get_maxvalued_partitions_html return varchar2;
  The function returns the same as get_maxvalued_partitions, but in the form of an html table
  
• procedure send_partitions_report (mail varchar2);
  The procedure for sending a report with tables that have started filling out the last section. The only parameter is the address to send to.
  

Package Code:

1. create or replace package body pkg_partitions is
2. / ** Function returning parameter for the penultimate section
3. * @param i_table_name Table Name
4. * @ return varchar2
5. * /
6. function get_penultimate_maxvalue (p_table_owner varchar2, p_table_name varchar2) return varchar2 is
7. l_cursor integer default dbms_sql.open_cursor;
8. l_ignore number;
9. l_long_val varchar2 (4000);
10. l_long_len number;
11. l_buflen number: = 4000;
12. l_curpos number: = 0;
13. begin
14. dbms_sql.parse (l_cursor,
15. '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) '
16. ,
17. dbms_sql.native);
18. dbms_sql.bind_variable (l_cursor, ': x' , p_table_name);
19. dbms_sql.bind_variable (l_cursor, ': o' , p_table_owner);
21. dbms_sql.define_column_long (l_cursor, 1);
22. l_ignore: = dbms_sql. execute (l_cursor);
23. if (dbms_sql.fetch_rows (l_cursor)> 0)
24. then
25. dbms_sql.column_value_long (l_cursor, 1, l_buflen, l_curpos,
26. l_long_val, l_long_len);
27. end if ;
28. dbms_sql.close_cursor (l_cursor);
29. return l_long_val;
30. end ;
32. / ** Function returning the names of tables and their owners, who have started filling out the last section
33. * @ return
34. * table_name varchar2 (4000),
35. * table_owner varchar2 (4000),
36. * partitions_count number,
37. * partition_name varchar2 (4000));
38. * /
39. function get_maxvalued_partitions return tables_props_array pipelined is
40. l_cursor integer default dbms_sql.open_cursor;
41. l_count number;
42. l_ignore integer ;
43. l_data table_props;
44. cursor l_partitions is
45. select
46. pl.table_owner,
47. pl.table_name,
48. count (1) cnt,
49. max (pl.partition_name) keep (dense_rank last order by (pl.partition_position)) partition_name
50. from dba_tab_partitions pl
51. where pl.table_name not like 'BIN $%'
52. group by pl.table_owner, pl.table_name
53. having count (1)> 1;
54. BEGIN
55. FOR part in l_partitions
56. LOOP
57. dbms_sql.parse (l_cursor,
58. 'select count (1) from' || part.table_owner || '.' || part.table_name
59. || 'partition (' || part.partition_name || ')'
60. || 'where rownum <2'
61. ,
62. dbms_sql.native);
63. dbms_sql.define_column (l_cursor, 1, l_count);
64. l_ignore: = dbms_sql.execute_and_fetch (l_cursor);
65. dbms_sql.column_value (l_cursor, 1, l_count);
66. if (l_count> 0) then
67. l_data.table_name: = part.table_name;
68. l_data.table_owner: = part.table_owner;
69. l_data.partitions_count: = part.cnt;
70. l_data.partition_name: = part.partition_name;
71. pipe row (l_data);
72. end if ;
73. END LOOP;
74. END ;
76. / ** The function returns the names of the tables and their owners in the form of html, which began to fill the last section
77. * @ return
78. * table_name varchar2 (4000),
79. * table_owner varchar2 (4000),
80. * partitions_count number,
81. * partition_name varchar2 (4000));
82. * /
83. function get_maxvalued_partitions_html return varchar2 is
84. l_cursor integer default dbms_sql.open_cursor;
85. l_count number;
86. l_ignore integer ;
87. l_data varchar2 (4000);
88. cursor l_partitions is
89. select
90. pl.table_owner,
91. pl.table_name,
92. count (1) cnt,
93. max (pl.partition_name) keep (dense_rank last order by (pl.partition_position)) partition_name
94. from dba_tab_partitions pl
95. where pl.table_name not like 'BIN $%'
96. group by pl.table_owner, pl.table_name
97. having count (1)> 1;
98. BEGIN
99. l_data: = '<html> <body> <table border = 1>'
100. || '<tr> <th> Table name </ th>'
101. || '<th> Table owner </ th>'
102. || '<th> Partitions count </ th>'
103. || '<th> Partition name </ th>'
104. || '<th> Pre maxvalue </ th>' ;
106. FOR part in l_partitions
107. LOOP
108. dbms_sql.parse (l_cursor,
109. 'select count (1) from' || part.table_owner || '.' || part.table_name
110. || 'partition (' || part.partition_name || ')'
111. || 'where rownum <2'
112. ,
113. dbms_sql.native);
114. dbms_sql.define_column (l_cursor, 1, l_count);
115. l_ignore: = dbms_sql.execute_and_fetch (l_cursor);
116. dbms_sql.column_value (l_cursor, 1, l_count);
117. if (l_count> 0) then
118. l_data: = l_data || '<tr> <td>'
119. || part.table_name
120. || '</ td> <td>'
121. || part.table_owner
122. || '</ td> <td>'
123. || part.cnt
124. || '</ td> <td>'
125. || part.partition_name
126. || '</ td> </ tr>' ;
127. end if ;
128. END LOOP;
129. l_data: = l_data || '</ table> </ body> </ html>' ;
130. return l_data;
131. END ;
132. / **
133. * The procedure for sending a report with tables that have started to fill the last section
134. * /
135. procedure send_partitions_report (mail varchar2)
136. is
137. msg_body varchar2 (4000);
138. BEGIN
139. select pkg_partitions.get_maxvalued_partitions_html into msg_body from dual;
140. - EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server =' 'our_mailserver' '' ;
141. utl_mail.send (
142. sender => 'oracleDBA@dbdomain.com' ,
143. recipients => mail,
144. subject => 'Maxvalued partitions Report' ,
145. message => msg_body,
146. mime_type => 'text / html' );
147. END ;
148. end pkg_partitions;

* This source code was highlighted with Source Code Highlighter .

UPD
As correctly suggested by zhekappp, you can use num_rows when you turn on automatic statistics collection. Statistics collection can be enabled by adding a job using dbms_job with dbms_stats.gather_table_stats.
Then you will need to remove the request for the number of records in the section and change the request to:

1. select
2. pl.table_owner,
3. pl.table_name,
4. count (1) cnt,
5. max (pl.num_rows) keep (dense_rank last order by (pl.partition_position)) partition_rows,
6. max (pl.partition_name) keep (dense_rank last order by (pl.partition_position)) partition_name
7. from dba_tab_partitions pl
8. where pl.table_name not like 'BIN $%'
9. group by pl.table_owner, pl.table_name

The full package code for this case can be found here: http://www.xt-r.com/2010/10/pkgpartitions.html

Automatic execution

It remains only to configure the automatic execution. We do this with dbms_job.
For example, the daily automatic execution of the data retrieval script:

1. declare
2. job binary_integer;
3. begin
4. dbms_job.submit (
5. job,
6. 'pkg_partitions.send_partitions_report (' 'dba@domain.ru' ');' ,
7. sysdate
8. 'trunc (sysdate) +1' );
9. dbms_output.put_line (job);
10. end ;