range分区交换过程
对于大数据的表，采用分区的方法无疑是最好的选择，分区表建议采用local index以减低分区交换对索引的维护成本，以下是基于local index分区交换的一个procedure ,写出来，分享一下：
/////////////////////////////////////////////////////////创建procedure////////////////////////////////////////////////
create or replace procedure p_test
is
l_owner VARCHAR2(30);
l_table_name VARCHAR2(30);
l_part_name VARCHAR2(30);
l_temp_table_name VARCHAR2(30);
l_his_table_name VARCHAR2(30);
l_his_part_name VARCHAR2(30);
l_high_value VARCHAR2(100);
l_date DATE;
l_sql VARCHAR2(2000);
l_flag NUMBER;
l_orders_count NUMBER;
l_orders_his_count NUMBER;
l_sql1 VARCHAR2(2000);
l_sql2 VARCHAR2(2000);
t_sql1 VARCHAR2(2000);
t_sql2 VARCHAR2(2000);
t_sql3 VARCHAR2(2000);
cursor orders_cursor is SELECT table_owner, table_name, partition_name, high_value
FROM dba_tab_partitions
WHERE table_owner = 'test1'----表所属的user
AND table_name = 'table1'----要转历史table
AND partition_name <> 'P001'----table1的初始化分区
ORDER BY partition_position;
begin
open orders_cursor;
LOOP
FETCH orders_cursor INTO l_owner, l_table_name, l_part_name, l_high_value;
EXIT WHEN orders_cursor%NOTFOUND;
begin
l_sql := 'SELECT ' || l_high_value || ' FROM DUAL';
EXECUTE IMMEDIATE l_sql
INTO l_date;
--确定是否存在15天之前的分区
SELECT CASE
WHEN l_date < SYSDATE-15 THEN
1
ELSE
0
END
INTO l_flag
FROM dual;
if (l_flag = 1) then
l_sql1 := 'SELECT COUNT(1) FROM ' || l_owner || '.' || l_table_name ||
' partition(' || l_part_name || ')';
EXECUTE IMMEDIATE l_sql1
INTO l_orders_count;
l_temp_table_name := l_table_name ||'_TMP';
l_his_table_name := l_table_name ||'_HIS';
l_his_part_name := 'PART_' ||to_char(l_date, 'YYYYMMDD');
--1. 线上表与临时表交换分区（需要创建tmp表和分区表结构一直的普通表）
t_sql1 :='ALTER TABLE ' || l_owner || '.' || l_table_name ||
' exchange PARTITION ' || l_part_name ||
' WITH TABLE ' || l_owner || '.' ||
l_temp_table_name || ' including indexes';
EXECUTE IMMEDIATE t_sql1;
--2. 线上表删除分区
EXECUTE IMMEDIATE 'ALTER TABLE ' || l_owner || '.' || l_table_name ||
' drop PARTITION ' || l_part_name;
--3. 历史表增加分区---（先创建和主表结构一直的非interval的历史表）
t_sql2 :='ALTER TABLE ' || l_owner || '.' ||
l_his_table_name || ' add PARTITION ' ||
l_his_part_name || ' VALUES less than(' ||
l_high_value || ')';
EXECUTE IMMEDIATE t_sql2;
--4. 历史表与临时表交换分区
t_sql3 :=' ALTER TABLE ' || l_owner || '.' ||
l_his_table_name || ' exchange PARTITION ' ||
l_his_part_name || ' WITH TABLE ' || l_owner || '.' ||
l_temp_table_name|| ' including indexes';
EXECUTE IMMEDIATE t_sql3;
l_sql2 := 'SELECT COUNT(1) FROM ' || l_owner || '.' ||
l_his_table_name || ' partition(' || l_his_part_name || ')';
EXECUTE IMMEDIATE l_sql2
INTO l_orders_his_count;
IF (l_orders_count = l_orders_his_count) THEN
insert into test1.test1_exchange_log---------------创建记录日志表，记录交换信息；
(message, errordate)
values
('table1分区'||l_part_name||'与历史表交换成功', sysdate);
COMMIT;
ELSE
insert into test1.test1_exchange_log
(message, errordate)
values
('table1分区'||l_part_name||'与历史表交换未成功', sysdate);
COMMIT;
exit;
END IF;
ELSE
insert into test1.test1_exchange_log(message,errordate) values('table1没有15天之前的分区,交换未执行',sysdate);
commit;
END IF;
EXCEPTION
WHEN OTHERS
THEN
insert into test1.test1_exchange_log(message,errordate) values ('table1出现其它错误,请查看',sysdate);
COMMIT;
END;
END LOOP;
CLOSE orders_cursor;
END;
////////////////////////create table test1_exchange_log///////////////////////////////////////////////////////
create table test1.test1_exchange_log(
message varchar2(100),
errordate date
);
////////////////////////////////////////////createtable table1//////////////////////////////////////////////////////////////
create table test1.table1
(
ID NUMBER not null,
BANKID NUMBER,
BANKCHECKID NUMBER,
BALANCEDATE DATE not null,
BALANCETASKID NUMBER,
BALANCESTATUS NUMBER,
CREATEDATE DATE not null,
UPDATEDATE DATE not null
)
PARTITION BY RANGE (CREATEDATE) INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition P001 values less than (TO_DATE('2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14507784/viewspace-759226/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
multipath多路径存储配置
下一篇：
DATAGUARD异常处理
请登录后发表评论
登录
全部评论
