--创建一个分区表
CREATE TABLE GBRP_REPORT_PROC_EXECUTE_T(
execute_index NUMBER(4),
procedure_name VARCHAR2(100),
old_table_name VARCHAR2(100),
new_table_name VARCHAR2(100),
table_desc VARCHAR2(100),
in_para VARCHAR2(100),
out_para VARCHAR2(100),
proc_type NUMBER(2),
if_exe NUMBER(2),
report_type NUMBER(2),
exe_time DATE,
if_valid NUMBER(2) default 0
tablespace users
partition by range(exe_time)
(
partition p201712 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201801 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201802 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201803 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201804 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201805 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201806 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition pmax values less than (maxvalue) tablespace users
);
comment on column GBRP_REPORT_PROC_EXECUTE_T.execute_index is '执行顺序';
comment on column GBRP_REPORT_PROC_EXECUTE_T.procedure_name is '过程名';
comment on column GBRP_REPORT_PROC_EXECUTE_T.old_table_name is '老表名';
comment on column GBRP_REPORT_PROC_EXECUTE_T.new_table_name is '新表名';
comment on column GBRP_REPORT_PROC_EXECUTE_T.table_desc is '表名说明';
comment on column GBRP_REPORT_PROC_EXECUTE_T.in_para is '过程入参';
comment on column GBRP_REPORT_PROC_EXECUTE_T.out_para is '过程出参';
comment on column GBRP_REPORT_PROC_EXECUTE_T.proc_type is '过程类型:1:定时执行;2:执行一次';
comment on column GBRP_REPORT_PROC_EXECUTE_T.if_exe is '是否已经执行:1:已执行;0:未执行';
comment on column GBRP_REPORT_PROC_EXECUTE_T.report_type is '报表类型:1:日报;2:周报;3:月 报;4:季报;5:年报';
comment on column GBRP_REPORT_PROC_EXECUTE_T.exe_time is '定时执行过程的时间';
comment on column GBRP_REPORT_PROC_EXECUTE_T.if_valid is '是否有效 0:有效 1:无效';
)
CREATE TABLE GBRP_REPORT_PROC_EXECUTE_T(
execute_index NUMBER(4),
procedure_name VARCHAR2(100),
old_table_name VARCHAR2(100),
new_table_name VARCHAR2(100),
table_desc VARCHAR2(100),
in_para VARCHAR2(100),
out_para VARCHAR2(100),
proc_type NUMBER(2),
if_exe NUMBER(2),
report_type NUMBER(2),
exe_time DATE,
if_valid NUMBER(2) default 0
tablespace users
partition by range(exe_time)
(
partition p201712 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201801 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201802 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201803 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201804 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201805 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition p201806 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace users,
partition pmax values less than (maxvalue) tablespace users
);
comment on column GBRP_REPORT_PROC_EXECUTE_T.execute_index is '执行顺序';
comment on column GBRP_REPORT_PROC_EXECUTE_T.procedure_name is '过程名';
comment on column GBRP_REPORT_PROC_EXECUTE_T.old_table_name is '老表名';
comment on column GBRP_REPORT_PROC_EXECUTE_T.new_table_name is '新表名';
comment on column GBRP_REPORT_PROC_EXECUTE_T.table_desc is '表名说明';
comment on column GBRP_REPORT_PROC_EXECUTE_T.in_para is '过程入参';
comment on column GBRP_REPORT_PROC_EXECUTE_T.out_para is '过程出参';
comment on column GBRP_REPORT_PROC_EXECUTE_T.proc_type is '过程类型:1:定时执行;2:执行一次';
comment on column GBRP_REPORT_PROC_EXECUTE_T.if_exe is '是否已经执行:1:已执行;0:未执行';
comment on column GBRP_REPORT_PROC_EXECUTE_T.report_type is '报表类型:1:日报;2:周报;3:月 报;4:季报;5:年报';
comment on column GBRP_REPORT_PROC_EXECUTE_T.exe_time is '定时执行过程的时间';
comment on column GBRP_REPORT_PROC_EXECUTE_T.if_valid is '是否有效 0:有效 1:无效';
)