Oracle中创建默认时间戳的表,Oracle创建按时间戳分区表

1 建分区表

-- Create table

create table PARTITION_TABLE

(

field1 INTEGER,

field2 VARCHAR2(32),

field3 INTEGER,

)

partition by range(field3)

interval (86400)

(partition p1 values less than (1431964800) ) nologging

tablespace user;

);

2 增加存储过程对扩展的分区改名

CREATE OR REPLACE PROCEDURE PRO_RENAME_PARTITION AS

SQLSTR VARCHAR2(4000);

val2 int;

newpart varchar2(32);

BEGIN

for REC in (select table_name

from user_tables

where table_name like 'PARTITION_%') loop

for x in (select high_value, partition_name

from user_tab_partitions

where table_name = REC.TABLE_NAME

and partition_name not like 'PART_201%') loop

val2 := to_number(x.high_value);

newpart := 'PART_' ||

to_char(to_date('19700101080000', 'yyyymmddhh24miss') +

(val2 - 3600) / 86400,

'yyyymmdd');

sqlstr := 'alter table ' || REC.TABLE_NAME || ' rename partition ' ||

x.partition_name || ' to ' || newpart;

begin

execute immediate sqlStr;

exception

when others then

null;

end;

end loop;

end loop;

END PRO_RENAME_PARTITION;

3 增加job定时调用存储过程

/

begin

dbms_scheduler.create_job

(

job_name => 'job_pro_rename_partition',

job_type => 'PLSQL_BLOCK',

job_action => 'begin PRO_RENAME_PARTITION ; end;',

repeat_interval => 'FREQ=DAILY;BYHOUR=1;byminute=0',

enabled => true

);

end;

/