搜索
oracle按日其定时创建分区实例1创建一张分区表，使用本地索引--CreatetablecreatetableTEST_DAY_INTERVAL_PARTITION(idNUMBERnotnull,time_colDATEnotnull)partitionbyrange(TIME_COL)(partitionPDEFAULTvalueslessthan(MAXVALUE)tablespaceTS_TESTpctfree10initrans1maxtrans255storage(initial64Knext1Mminextents1maxextentsunlimited));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableTEST_DAY_INTERVAL_PARTITIONaddconstraintPK_TEST_DAY_INTERVAL_PARTprimarykey(ID,TIME_COL)usingindexlocal;--创建本地分区索引2创建分区表存储过程CREATEORREPLACEPROCEDUREp_test_create_partition/*ver1.0*/(p_errnoOUTNUMBER,--返回错误编号，0=成功，-1=失败p_errmsgOUTVARCHAR2--返回错误信息)ASPRAGMAAUTONOMOUS_TRANSACTION;v_exp_appEXCEPTION;v_idNUMBER;v_tablenameVARCHAR2(64);v_create_startVARCHAR2(1024);v_create_date_startdate;v_sqlstrVARCHAR2(1024);v_max_partitoinVARCHAR2(100);v_partition_nameVARCHAR2(64);BEGINv_tablename:='test_day_interval_partition';SELECT--获取目标表最大分区信息substr(MAX(partition_name),2)INTOv_max_partitoinFROMuser_tab_partitionsWHEREtable_name=upper('test_day_interval_partition')ANDpartition_name'PDEFAULT';v_create_start:=nvl(v_max_partitoin,to_char(trunc(SYSDATE-1),'yyyymmdd'));v_create_date_start:=to_date(v_create_start,'yyyymmdd');FORiIN1..10LOOPBEGINv_partition_name:='P'||to_number(to_char(v_create_date_start+i,'YYYYMMDD'));v_sqlstr:='ALTERTABLE'||v_tablename||'SPLITPARTITIONPDEFAULTat(to_date('''||to_char(v_create_date_start+i+1,'yyyymmdd')||''',''yyyymmdd''))INTO(PARTITION'||v_partition_name||',PARTITIONPDEFAULT)';EXECUTEIMMEDIATEv_sqlstr;EXCEPTIONWHENOTHERSTHENROLLBACK;p_errmsg:='执行失败!'||SQLERRM||v_sqlstr;COMMIT;END;ENDLOOP;p_errmsg:='';p_errno:=0;EXCEPTIONWHENOTHERSTHENIFSQLCODEIN(-14623,-14313)THEN--如果是对象已存在，不处理异常，按成功返回p_errno:=0;ELSIFSQLCODE=-54THEN--解决并发DDL时的resourcebusy错误dbms_lock.sleep(dbms_random.value(0,2));EXECUTEIMMEDIATEp_errmsg;p_errno:=0;ELSEp_errno:=-1;p_errmsg:='表'||v_tablename||'创建分区P'||v_id||'分区失败！'||p_errmsg||SQLERRM;ENDIF;END;3分区创建定时任务declarev_job_idnumber;beginFORjob_id_tabIN(SELECTjobFROMuser_jobsWHEREwhatLIKE'%p_test_create_partition%')LOOPdbms_job.remove(job_id_tab.job);ENDLOOP;dbms_job.submit(v_job_id,'p_test_create_partition;',sysdate,'sysdate+5');dbms_job.run(v_job_id);end;/