Oracle partition table data migration and Management Automation Process

Oracle partition table data migration and Management Automation Process

The following process is centered on the company's Oracle Database runtime environment. It is mainly written to achieve automation.
Process completion tasks include
1. automatically add partitions from the previous day to partition by Time
2. automatically delete T_partition_1 Partition Table 6 days ago, T_partition_1 is the current table
3. The T_partition_2 Partition Table is automatically deleted one year ago. T_partition_2 is a historical table that stores historical data.
4. Only exchange data one day before five days. Exchange the data in Table T_partition_1 to T_PARTITION_SWAP, and exchange the data in the T_PARTITION_SWAP to the T_partition_2 history table.
5. If an exception occurs, an error log table will be inserted for ease of viewing.

Process name: manage_partition

Create or replace procedure manage_partition is

Partition_name_add_1 varchar2 (20 );
Partition_name_reduce_5 varchar2 (20 );
Current_time varchar2 (20 );
V_ SQL varchar2 (1000 );
Partiton_name varchar2 (50 );
Partition_values varchar2 (20 );
Swap_count number (38 );
Pro_name varchar2 (20 );
Err_info varchar2 (20 );
Sj varchar2 (20 );

Cursor all_data is select table_name, max (partition_name) as partition_name, tablespace_name from user_tab_partitions where table_name in ('t_ partition_1 ', 't_ partition_2') group
Table_name, tablespace_name;

Type mt_his is record (table_name varchar2 (20), partiton_name varchar2 (20), tablespace_name varchar2 (50 ));

All_table mt_his;

Begin

Select to_char (sysdate + 1, 'yyyy-mm-dd hh24: mi: ss') into partition_values from dual;

Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') into current_time from dual;

Select 'P _ '| substr (to_char (sysdate + 1, 'yyyymmdd'), 1, 8) |' _ 23' into partition_name_add_1 from dual;
Select 'P _ '| substr (to_char (sysdate-interval '5' day, 'yyyymmdd'),) |' _ 23' into partition_name_performance_5 from dual;

For all_table in all_data loop

If partition_name_add_1 <> all_table.partition_name then

V_ SQL: = 'alter table' | all_table.table_name | 'add partition' | partition_name_add_1 | 'values less than (TO_DATE ('| ''' | partition_values |'' ''| ', '| ''' YYYY-MM-DD HH24: MI: s''' |') tablespace '| all_table.tablespace_name | '';
Execute immediate v_ SQL;

End if;

End loop;

Declare
Cursor old_partition_1 is select partition_name, table_name from region where table_name = 't_ partition_1 'and substr (partition_name,) <to_char (sysdate-interval '6' day, 'yyyymmdd ');
-- Old_p_1 user_tab_partitions.partition_name % type;
Begin
For old_p_1 in old_partition_1 loop
V_ SQL: = 'alter table' | old_p_1.table_name | 'drop partition' | old_p_1.partition_name | '';
Execute immediate v_ SQL;

End loop;
End;

Declare

Cursor old_partition_2 is select partition_name, table_name from region where table_name = 't_ partition_2 'and substr (partition_name,) <to_char (sysdate-interval '1' year, 'yyyymmdd ');
-- Old_p_1 user_tab_partitions.partition_name % type;
Begin
For old_p_2 in old_partition_2 loop
V_ SQL: = 'alter table' | old_p_2.table_name | 'drop partition' | old_p_2.partition_name | '';
Dbms_output.put_line (old_p_2.table_name );
Execute immediate v_ SQL;

End loop;
End;
Select count (1) into swap_count from T_PARTITION_SWAP;
If swap_count = 0 then

V_ SQL: = 'alter table T_partition_1 exchange partition' | partition_name_performance_5 | 'with table T_PARTITION_SWAP UPDATE INDEXES ';
Execute immediate v_ SQL;
V_ SQL: = 'alter table T_partition_2 exchange partition' | partition_name_performance_5 | 'with table T_PARTITION_SWAP UPDATE INDEXES ';
Execute immediate v_ SQL;
Else
V_ SQL: = 'truncate table T_SMSGATEWAY_MT_SWAP ';
Execute immediate v_ SQL;
V_ SQL: = 'alter table T_SMSGATEWAY_MT exchange partition' | partition_name_performance_5 | 'with table T_SMSGATEWAY_MT_SWAP UPDATE INDEXES ';
Execute immediate v_ SQL;
V_ SQL: = 'alter table T_SMSGATEWAY_MT_HIS exchange partition' | partition_name_performance_5 | 'with table T_SMSGATEWAY_MT_SWAP UPDATE INDEXES ';
Execute immediate v_ SQL;
End if;
Exception
When others then
-- Sg_log_err ('Manage _ partition ', sqlerrm );
Pro_name: = 'Manage _ partition ';
Err_info: = sqlerrm;
Select sysdate into sj from dual;
V_ SQL: = 'insert into err_log values ('| ''' pro _ name''' |', ''' | err_info | ''', ''' | sj | ''')';
Execute immediate v_ SQL;
Commit;

Dbms_output.put_line (sqlcode | sqlerrm );
End manage_partition;

Error Log table used to record exception logs
Create statement
Create table err_log (pro_name varchar2 (20), err_log varchar2 (200), error_time date );