Automatically add partitions for Oracle user tables

Automatically add partitions for Oracle user tables

This program can be executed as an Oracle JOB before the 28 th day of every month (taking into account the reason for the day of May 1), automatically adding partitions to the partition table of this user. [@ more @]
Create or replace procedure guan_add_partition
/*
/* Automatically add partitions to all partition tables under a user. The partition column is of the date type, and the partition name is similar to p200706.
/* Create by David
*/
As
V_table_name varchar2 (50 );
V_partition_name varchar2 (50 );
V_month char (6 );
V_add_month_1 char (6 );
V_ SQL _string varchar2 (2000 );
V_add_month varchar2 (20 );
Cursor cur_part is select distinct u. table_name, max (p. partition_name) max_part_name from user_tables u, user_tab_partitions p
Where u. table_name = p. table_name and u. partitioned = 'yes'
Group by u. table_name;
Begin
Select to_char (sysdate, 'yyyymmm ') into v_month from dual;
Select to_char (add_months (sysdate, 1), 'yyymmm') into v_add_month_1 from dual;
Select to_char (add_months (trunc (sysdate, 'mm'), 2), 'yyyy-mm-dd') into v_add_month from dual;
Open cur_part;
Loop
Fetch cur_part into v_table_name, v_partition_name;
Exit when cur_part % notfound;
If to_number (substr (v_partition_name, 2) <= to_number (substr (v_month, 1) then

V_ SQL _string: = 'alter table' | v_table_name | 'add partition P' | v_add_month_1 |
'Values less than (to_date (''' | v_add_month | ''', ''yyyy-mm-dd'') tablespace users ';
Execute immediate v_ SQL _string;
Else
Null;
End if;
End loop;
Close cur_part;
End;