PLSQL Developer工具中存储过程测试方法:建立存储过程后,在存储过程名称上点击右键,选中”添加调试信息“,然后再次点击右键,选择“测试”,工具会自动打开新窗口,然后即可进入单步调试状态;
1、新建分区表及根据实际应用情况建立本地索引或者全局索引,分区后,查询语句条件要包含分区字段,否则导致每个分区都会扫描,消耗更大
-- Create table
create table EMS_LAYERFORMULADATA2
(
id NUMBER,
formulano NVARCHAR2(50) not null,
acquisitiontime NVARCHAR2(20) not null,
totalvalue NVARCHAR2(20),
duration NVARCHAR2(20),
acquisitiontimeend NVARCHAR2(20),
flagid NUMBER
)
partition by range (acquisitiontimeend)
(
partition EMS_LAYERFORMULADATA_201506 values less than ('2015-07') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201507 values less than ('2015-08') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201508 values less than ('2015-09') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201509 values less than ('2015-10') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201510 values less than ('2015-11') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201511 values less than ('2015-12') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201512 values less than ('2016-01') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201601 values less than ('2016-02') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201602 values less than ('2016-03') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201603 values less than ('2016-04') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201604 values less than ('2016-05') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201605 values less than ('2016-06') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201606 values less than ('2016-07') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201607 values less than ('2016-08') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201608 values less than ('2016-09') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201609 values less than ('2016-10') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201610 values less than ('2016-11') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201611 values less than ('2016-12') tablespace HTEMS,
partition EMS_LAYERFORMULADATA_201612 values less than ('2017-01') tablespace HTEMS
);
-- Create/Recreate indexes
create index INDEX_FORMULANO2 on EMS_LAYERFORMULADATA2 (FORMULANO)
2、向分区表中添加模拟数据
DECLARE
temp nvarchar2(19);
temp2 nvarchar2(19);
BEGIN
temp:= '2015-01-01 00:00:00';
temp2:= '2015-01-02 00:00:00';
WHILE temp<'2017-01' LOOP
insert into EMS_LAYERFORMULADATA2 values(null,'Test_001',temp,'100','',temp2,'');
DBMS_OUTPUT.PUT_LINE(temp);
temp:=to_char((to_date(temp,'yyyy-mm-dd hh24:mi:ss')+1),'yyyy-mm-dd hh24:mi:ss');
temp2:=to_char((to_date(temp2,'yyyy-mm-dd hh24:mi:ss')+1),'yyyy-mm-dd hh24:mi:ss');
END LOOP;
DBMS_OUTPUT.PUT_LINE(temp);
END;
declare
temp varchar2(20):='aaaa';
i number;
begin
for i in 1..100 loop
insert into e(a,b) values(i,temp);
end loop;
end;
查询分区数据:
select * from EMS_LAYERFORMULADATA2BK partition(EMS_LAYERFORMULADATA2BK_201606)
</pre><p></p><p>3、oracle利用切换分区处理分区数据的转移,切换时需要一个中间表,中间表表结构需要和源表、目的表相同,切换到的目的表的限制可以比较少,比如源表有主键,目的表可以没有主键,但是目的表有主键,源表一定要有主键:</p><p></p><pre name="code" class="sql">alter table EMS_LAYERFORMULADATA2 exchange partition EMS_LAYERFORMULADATA2_201606 with table EMS_LAYERFORMULADATA2EXCHANGE ;
alter table EMS_LAYERFORMULADATA2BK exchange partition EMS_LAYERFORMULADATA2BK_201606 with table EMS_LAYERFORMULADATA2EXCHANGE ;
4、全局分区索引每次分区删除、转移、切换后需要重建,分区切换时切换分区的本地索引也需要重建,删除分区不需要重建本地索引
alter index IX_EMS_DATAACQUISITION2BK_TIME rebuild partition EMS_DATAACQUISITION2BK_201606 nologging;
alter index PK_EMS_DATAACQUISITION22 rebuild nologging;
5、处理分区后查看索引是否有效安全
select index_name, partition_name, status
from user_ind_partitions
where status = 'UNUSABLE'
union all
select index_name, '' as partition_name, status
from user_indexes
where status = 'UNUSABLE';
6、自动增加、删除、切换分区的sql存储过程脚本
CREATE OR REPLACE PROCEDURE processPartition IS
nextPar nvarchar2(6);
sixMonthBeforePar nvarchar2(6);
twoYearBeforePar nvarchar2(6);
nextParMonth nvarchar2(7);
isExist number;
v_string varchar2(200); --字符串变量
BEGIN
select to_char(add_months(sysdate,1),'YYYYMM') into nextPar from dual;--201607
select to_char(add_months(sysdate,2),'YYYY-MM') into nextParMonth from dual;--2016-08
select to_char(add_months(sysdate,-6),'YYYYMM') into sixMonthBeforePar from dual;
select to_char(add_months(sysdate,-24),'YYYYMM') into twoYearBeforePar from dual;
--处理EMS_LAYERFORMULADATA分区情况
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATA' and PARTITION_NAME='EMS_LAYERFORMULADATA_'||nextPar;
if isExist=0 then
v_string:= 'alter table EMS_LAYERFORMULADATA add partition ' || 'EMS_LAYERFORMULADATA_' || nextPar ||' values less than ('''|| nextParMonth|| ''')';
EXECUTE IMMEDIATE v_string;
end if;
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATABK' and PARTITION_NAME='EMS_LAYERFORMULADATABK_'||nextPar;
if isExist=0 then
v_string:='alter table EMS_LAYERFORMULADATABK add partition ' || 'EMS_LAYERFORMULADATABK_' || nextPar ||' values less than ('''|| nextParMonth|| ''')';
EXECUTE IMMEDIATE v_string;
end if;
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATA' and PARTITION_NAME='EMS_LAYERFORMULADATA_'||sixMonthBeforePar;
if isExist=1 then
v_string:='alter table EMS_LAYERFORMULADATA exchange partition EMS_LAYERFORMULADATA_'||sixMonthBeforePar || ' with table EMS_LAYERFORMULADATAEXCHANGE';
EXECUTE IMMEDIATE v_string;
v_string:='alter index IDX_EMS_FORMULADATA_NO rebuild partition EMS_LAYERFORMULADATA_'||sixMonthBeforePar||' nologging ';
EXECUTE IMMEDIATE v_string;
v_string:='alter index IDX_EMS_FORMULADATA_TIME rebuild partition EMS_LAYERFORMULADATA_'||sixMonthBeforePar||' nologging ';
EXECUTE IMMEDIATE v_string;
v_string:='alter table EMS_LAYERFORMULADATABK exchange partition EMS_LAYERFORMULADATABK_'||sixMonthBeforePar || ' with table EMS_LAYERFORMULADATAEXCHANGE';
EXECUTE IMMEDIATE v_string;
v_string:='alter index IDX_EMS_FORMULADATABK_NO rebuild partition EMS_LAYERFORMULADATABK_'||sixMonthBeforePar||' nologging ';
EXECUTE IMMEDIATE v_string;
v_string:='alter index IDX_EMS_FORMULADATABK_TIME rebuild partition EMS_LAYERFORMULADATABK_'||sixMonthBeforePar||' nologging ';
EXECUTE IMMEDIATE v_string;
--EXECUTE IMMEDIATE 'alter table EMS_LAYERFORMULADATA drop partition EMS_LAYERFORMULADATA_'||sixMonthBeforeParMonth;
end if;
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATABK' and PARTITION_NAME='EMS_LAYERFORMULADATABK_'||twoYearBeforePar;
if isExist=1 then
v_string:='alter table EMS_LAYERFORMULADATABK drop partition EMS_LAYERFORMULADATABK_'||twoYearBeforePar;
EXECUTE IMMEDIATE v_string;
end if;
--处理EMS_DATAACQUISITION分区情况
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITION' and PARTITION_NAME='EMS_DATAACQUISITION_'||nextPar;
if isExist=0 then
v_string:= 'alter table EMS_DATAACQUISITION add partition ' || 'EMS_DATAACQUISITION_' || nextPar ||' values less than ('''|| nextParMonth|| ''')';
EXECUTE IMMEDIATE v_string;
end if;
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITIONBK' and PARTITION_NAME='EMS_DATAACQUISITIONBK_'||nextPar;
if isExist=0 then
v_string:='alter table EMS_DATAACQUISITIONBK add partition ' || 'EMS_DATAACQUISITIONBK_' || nextPar ||' values less than ('''|| nextParMonth|| ''')';
EXECUTE IMMEDIATE v_string;
end if;
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITION' and PARTITION_NAME='EMS_DATAACQUISITION_'||sixMonthBeforePar;
if isExist=1 then
v_string:='alter table EMS_DATAACQUISITION exchange partition EMS_DATAACQUISITION_'||sixMonthBeforePar || ' with table EMS_DATAACQUISITIONEXCHANGE';
EXECUTE IMMEDIATE v_string;
v_string:='alter index IDX_EMS_DATAACQUISITION_TIME rebuild partition EMS_DATAACQUISITION_'||sixMonthBeforePar||' nologging ';
EXECUTE IMMEDIATE v_string;
v_string:='alter index PK_EMS_DATAACQUISITION rebuild nologging ';
EXECUTE IMMEDIATE v_string;
v_string:='alter table EMS_DATAACQUISITIONBK exchange partition EMS_DATAACQUISITIONBK_'||sixMonthBeforePar || ' with table EMS_DATAACQUISITIONEXCHANGE';
EXECUTE IMMEDIATE v_string;
v_string:='alter index IDX_EMS_DATAACQUISITIONBK_TIME rebuild partition EMS_DATAACQUISITIONBK_'||sixMonthBeforePar||' nologging ';
EXECUTE IMMEDIATE v_string;
--EXECUTE IMMEDIATE 'alter table EMS_LAYERFORMULADATA drop partition EMS_LAYERFORMULADATA_'||sixMonthBeforeParMonth;
end if;
SELECT count(1) into isExist FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITIONBK' and PARTITION_NAME='EMS_DATAACQUISITIONBK_'||twoYearBeforePar;
if isExist=1 then
v_string:='alter table EMS_DATAACQUISITIONBK drop partition EMS_DATAACQUISITIONBK_'||twoYearBeforePar;
EXECUTE IMMEDIATE v_string;
end if;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback');
ROLLBACK;
END;
7、建立job,定时自动执行存储过程
DECLARE
v_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(v_job,'processacquisitionPartition;',TRUNC(sysdate)+1/24,'TRUNC(sysdate)+1+1/24'); --- 每天凌晨1点执行一次, 从今天开始
commit;
END;
8、学习过程使用到的资料:
http://www.2cto.com/database/201301/185425.html
http://www.blogjava.net/tjyhy590/archive/2014/05/10/413484.html
http://blog.itpub.net/7199859/viewspace-203970/
http://www.blogjava.net/tjyhy590/archive/2014/05/10/413484.html
http://www.2cto.com/database/201201/116414.html