Implementation of auto-expanding partitions in Oracle (use interval partition after 11 GB)

Implementation of auto-expanding partitions in Oracle (use interval partition after 11 GB)

Implementation of auto-expanding partitions in Oracle (use interval partition after 11 GB)

Create error table:

Create sequence seq_error_log_id;
Create table error_log (id number, unit_name varchar2 (30 ),
Err_code varchar2 (100 ),
Err_desc varchar2 (4000 ),
Err_date date );

Automatically expand the partition package:

Create or replace package dba_tools is

-- Author: RenFj
-- Created: 7/24/2013 9:26:12 AM
-- Purpose: My DBA's utility
--
--
YEAR constant varchar2 (1): = 'y ';
MONTH constant varchar2 (1): = 'M ';
DAY constant varchar2 (1): = 'D ';

QUARTER constant varchar2 (1): = 'q ';

-- ***** No matter you understand it or not, I do .******
-- Create table log_errors (id number, unit_name varchar2 (30 ),
-- Err_code varchar2 (100 ),
-- Err_desc varchar2 (4000 ),
-- Err_date date );
-- Log errors which raised in the programs.
-- P_unit_name program name
-- P_sqlc error code (sqlcode)
-- P_sqler error message (sqlerrm)
--


Procedure err_log (p_unit_name varchar2,
P_sqlc varchar2,
P_sqler varchar2 );

-- Auto extend partitions with the specificed table in the program.
-- Tb_name Specified table name which need to extend partitions.
-- Part_iden Partition identifier
-- The following identifier can be used:
-- D day
-- M month
-- Q quarter
-- Y year
-- Interval_num Interval number, that means the additional partition values less than (sysdate + part_iden * interval_num)
-- This program need partition's name has roles. Like ($ table_name | $ lessthan_date_string) order_20130101
Procedure dba_autoextend_partitions (p_tb_name varchar2,
P_part_iden varchar2,
P_interval_num number );

End dba_tools;

Create or replace package body dba_tools is

Procedure err_log (p_unit_name varchar2,
P_sqlc varchar2,
P_sqler varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION; -- autonomous transaction.
Begin
-- No exception handle in this unit.
Insert into error_log
(Id, unit_name, err_code, err_desc, err_date)
Values
(Seq_error_log_id.nextval, p_unit_name, p_sqlc, p_sqler, sysdate );
Commit;
End;

Procedure dba_autoextend_partitions (p_tb_name varchar2,
P_part_iden varchar2,
P_interval_num number) is
D_last_part_dat date; -- end time in the last partition range
D_next_dat date; -- end time of the partition created this time
Vanderbilt part_less_than_val varchar2 (4000); -- less than string
V_part_name varchar2 (30); -- partition name
V_add_part_ SQL varchar2 (4000); -- add partition SQL statement
Begin
-- Query the end time of the last created partition. The format of the partition name must be xxxx_yyyymmdd.
-- For example, orders_201020101, 20120101 indicates the end time of the created partition.
Select to_date (regexp_replace (partition_name,
'(. *) (2 [0-9] {3} [0-9] {4 })',
'\ 2 '),
'Yyyymmdd ')
Into d_last_part_dat
From (select max (partition_position) over (partition by table_name) mn,
T .*
From user_tab_partitions t
Where table_name = upper (p_tb_name) t
Where t. mn = partition_position;
-- Calculate the end time of the created partition based on the p_part_iden and p_interval_num parameters.
-- D Indicates day, m indicates month, q indicates quarter, and y indicates year
If p_part_iden in ('D', 'D') then
D_next_dat: = d_last_part_dat + p_interval_num;
Elsif p_part_iden in ('M', 'M') then
D_next_dat: = add_months (trunc (d_last_part_dat, 'mm'), p_interval_num );
Elsif p_part_iden in ('Q', 'q') then
D_next_dat: = add_months (trunc (d_last_part_dat, 'q '),
3 * p_interval_num );
Elsif p_part_iden in ('y', 'y') then
D_next_dat: = add_months (trunc (d_last_part_dat, 'q '),
12 * p_interval_num );
Else
Raise_application_error (-20999,
'Type following identifier with p_part_iden: d (day), m (month), q (quarter), y (year ).');
End if;
-- Assemble the partition name v_part_name and the less than clause v_part_less_than_val Based on the cutoff time.
-- Example: v_part_name: = ORDERS_20120101
-- V_part_less_than_val: = TO_DATE (TO_CHAR (20120101, 'yyyymmdd '))
V_part_name: = upper (p_tb_name) | '_' |
To_char (d_next_dat, 'yyyymmdd ');
V_part_less_than_val: = 'to _ date ('| to_char (d_next_dat, 'yyyymmdd') |
', ''Yyymmdd '')';
-- Assembled SQL statement v_add_part_ SQL for adding partitions
-- Example: alter table print_media_part add partition p4 values less than (400)
V_add_part_ SQL: = 'alter table'
| P_tb_name
| 'Add partition'
| V_part_name
| 'Values less ('
| V_part_less_than_val
| ')';

Execute immediate v_add_part_ SQL;
Exception
When others then
Err_log ('dba _ autoextend_partitions ', sqlcode, sqlerrm );
End;

End dba_tools;

Create test table