1、检查是否可以重定义表

begin
 dbms_redefinition.can_redef_table('YCCS3','PHA_COM_APPLYOUT',1);--重定义前检查* 
end;

2、创建中间分区表（模板）

-- Create table
create table YCCS3.PHA_COM_APPLYOUT_bak
(
  apply_number        NUMBER(12) not null,
  dept_code           VARCHAR2(4) not null,
  drug_dept_code      VARCHAR2(10),
  class3_meaning_code VARCHAR2(8),
  group_code          VARCHAR2(20),
  drug_code           VARCHAR2(12) not null,
  trade_name          VARCHAR2(50),
  batch_no            VARCHAR2(16),
  drug_type           VARCHAR2(2),
  drug_quality        VARCHAR2(2),
  specs               VARCHAR2(32),
  pack_unit           VARCHAR2(16),
  pack_qty            NUMBER(4),
  min_unit            VARCHAR2(25) not null,
  show_flag           VARCHAR2(1),
  show_unit           VARCHAR2(16),
  retail_price        NUMBER(12,4),
  wholesale_price     NUMBER(12,4),
  purchase_price      NUMBER(12,4),
  apply_billcode      VARCHAR2(18),
  apply_opercode      VARCHAR2(6),
  apply_date          DATE,
  apply_state         VARCHAR2(1),
  apply_num           NUMBER(14,4),
  days                NUMBER(5),
  preout_flag         VARCHAR2(1),
  charge_flag         VARCHAR2(1),
  patient_id          VARCHAR2(14),
  patient_dept        VARCHAR2(4),
  druged_bill         VARCHAR2(50),
  druged_dept         VARCHAR2(4),
  druged_empl         VARCHAR2(6),
  druged_date         DATE,
  druged_num          NUMBER(14,4),
  dose_once           NUMBER(10,4),
  dose_unit           VARCHAR2(25),
  usage_code          VARCHAR2(10),
  use_name            VARCHAR2(20),
  dfq_freq            VARCHAR2(6),
  dfq_cexp            VARCHAR2(30),
  dose_model_code     VARCHAR2(10),
  order_type          VARCHAR2(2),
  mo_order            VARCHAR2(16),
  comb_no             VARCHAR2(14),
  exec_sqn            VARCHAR2(16),
  recipe_no           VARCHAR2(14),
  sequence_no         NUMBER(3),
  send_type           VARCHAR2(1),
  billclass_code      VARCHAR2(12),
  print_state         VARCHAR2(1),
  relieve_flag        VARCHAR2(1) default '0',
  relieve_code        VARCHAR2(12),
  print_empl          VARCHAR2(6),
  print_date          DATE,
  out_bill_code       NUMBER(12),
  valid_state         VARCHAR2(1) default '1',
  mark                VARCHAR2(200),
  cancel_empl         VARCHAR2(6),
  cancel_date         DATE,
  place_code          VARCHAR2(12),
  recipe_dept         VARCHAR2(6),
  recipe_oper         VARCHAR2(6),
  baby_flag           VARCHAR2(1) default '0',
  ext_flag            VARCHAR2(10),
  ext_flag1           VARCHAR2(10),
  compound_group      VARCHAR2(25) default 'U',
  compound_flag       VARCHAR2(1) default '0',
  compound_exec       VARCHAR2(1) default '0',
  compound_oper       VARCHAR2(6),
  compound_date       DATE,
  back_reason         VARCHAR2(100),
  isdeptbase          VARCHAR2(2)
)
 partition by range(apply_date)
(
 partition DATE2015_1 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64M
    next 8K
    minextents 1
    maxextents unlimited
  ),
    partition DATE2019_1 values less than (TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64M
    next 8K
    minextents 1
    maxextents unlimited
  ),
   partition DATE2020_1 values less than (TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64M
    next 8K
    minextents 1
    maxextents unlimited
  ),
   partition DATE2021_1 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64M
    next 8K
    minextents 1
    maxextents unlimited
  )
)
  ;
-- Add comments to the table 
comment on table YCCS3.PHA_COM_APPLYOUT_bak
  is '出库申请表';
-- Add comments to the columns 
comment on column YCCS3.PHA_COM_APPLYOUT_bak.apply_number
  is '申请流水号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.dept_code
  is '申请部门编码（科室或者病区）';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.drug_dept_code
  is '发药部门编码';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.class3_meaning_code
  is '出库申请分类';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.group_code
  is '批次号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.drug_code
  is '药品编码';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.trade_name
  is '药品商品名';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.batch_no
  is '批号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.drug_type
  is '药品类别';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.drug_quality
  is '药品性质';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.specs
  is '规格';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.pack_unit
  is '包装单位';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.pack_qty
  is '包装数';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.min_unit
  is '最小单位';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.show_flag
  is '显示的单位标记(0最小单位,1包装单位)';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.show_unit
  is '显示的单位';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.retail_price
  is '零售价';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.wholesale_price
  is '批发价';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.purchase_price
  is '购入价';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.apply_billcode
  is '申请单号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.apply_opercode
  is '申请人编码';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.apply_date
  is '申请日期';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.apply_state
  is '申请状态 0申请，1（配药）打印，2核准（出库），3作废，4暂不摆药';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.apply_num
  is '申请出库量(每付的总数量)';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.days
  is '付数（草药）';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.preout_flag
  is '是否预扣库存1是0否';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.charge_flag
  is '收费状态：0未收费，1已收费';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.patient_id
  is '患者编号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.patient_dept
  is '患者科室';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.druged_bill
  is '摆药单号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.druged_dept
  is '摆药科室';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.druged_empl
  is '摆药人';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.druged_date
  is '摆药日期';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.druged_num
  is '摆药数量';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.dose_once
  is '每次剂量';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.dose_unit
  is '剂量单位';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.usage_code
  is '用法代码';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.use_name
  is '用法名称';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.dfq_freq
  is '频次代码';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.dfq_cexp
  is '频次名称';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.dose_model_code
  is '剂型代码';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.order_type
  is '医嘱类别';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.mo_order
  is '医嘱流水号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.comb_no
  is '组合序号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.exec_sqn
  is '执行单流水号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.recipe_no
  is '处方号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.sequence_no
  is '处方内项目流水号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.send_type
  is '医嘱发送类型2临时，1集中，0全部';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.billclass_code
  is '摆药单分类代码';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.print_state
  is '打印状态（0未打印，1已打印）';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.relieve_flag
  is '门诊调剂标记1是0否';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.relieve_code
  is '调剂单流水号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.print_empl
  is '操作员（打印人）';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.print_date
  is '操作日期（打印时间）';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.out_bill_code
  is '出库单流水号（退库申请时，保存申请退库记录的出库单流水号号）';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.valid_state
  is '有效标记（1有效，0无效，2不摆药）';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.mark
  is '备注';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.cancel_empl
  is '取消操作员';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.cancel_date
  is '取消日期';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.place_code
  is '货位号';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.recipe_dept
  is '开方科室';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.recipe_oper
  is '开方医生';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.baby_flag
  is '是否婴儿 1 是 0 否';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.ext_flag
  is '扩展字段';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.ext_flag1
  is '扩展字段1';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.compound_group
  is '批次流水号,根据医嘱执行时间及组合号赋值';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.compound_flag
  is '是否需配液 ‘1’ 是 0 否';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.compound_exec
  is '是否配液已执行 1 是 0 否';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.compound_oper
  is '配液执行人';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.compound_date
  is '配液时间';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.back_reason
  is '退费原因';
comment on column YCCS3.PHA_COM_APPLYOUT_bak.isdeptbase
  is '是否扣减基药库存';
-- Create/Recreate indexes 
create index YCCS3.IDX_PHA_COM_APPLYOUT1_bak on YCCS3.PHA_COM_APPLYOUT_bak (DRUGED_BILL)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index YCCS3.IDX_PHA_COM_APPLYOUT2_bak on YCCS3.PHA_COM_APPLYOUT_bak (PRINT_DATE, DRUG_DEPT_CODE, APPLY_STATE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index YCCS3.IDX_PHA_COM_APPLYOUT3_bak on YCCS3.PHA_COM_APPLYOUT_bak (PATIENT_ID, APPLY_STATE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index YCCS3.IDX_PHA_COM_APPLYOUT5_bak on YCCS3.PHA_COM_APPLYOUT_bak (APPLY_DATE, DRUG_DEPT_CODE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index YCCS3.IDX_PHA_COM_APPLYOUT6_bak on YCCS3.PHA_COM_APPLYOUT_bak (RECIPE_NO, SEQUENCE_NO)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index YCCS3.IDX_PHA_COM_APPLYOUT7_bak on YCCS3.PHA_COM_APPLYOUT_bak (DEPT_CODE, DRUG_DEPT_CODE, APPLY_STATE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index YCCS3.IDX_PHA_COM_APPLYOUT8_bak on YCCS3.PHA_COM_APPLYOUT_bak (EXEC_SQN)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index YCCS3.PK_PDA_EXEC_bak on YCCS3.PHA_COM_APPLYOUT_bak (DRUG_CODE, DRUGED_BILL, TRADE_NAME, SPECS, MIN_UNIT, CLASS3_MEANING_CODE, ISDEPTBASE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table YCCS3.PHA_COM_APPLYOUT_bak
  add constraint PK_PHA_COM_APPLYOUT_bak primary key (APPLY_NUMBER)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
alter index YCCS3.PK_PHA_COM_APPLYOUT_bak nologging;
grant select,insert,update on YCCS3.PHA_COM_APPLYOUT_bak to YCCS4;

3、执行在线同步

begin 
 DBMS_REDEFINITION.START_REDEF_TABLE('YCCS3','PHA_COM_APPLYOUT','PHA_COM_APPLYOUT_bak',NULL,2); --开始重定义*  
end;

4、结束在线同步

begin 
DBMS_REDEFINITION.FINISH_REDEF_TABLE('YCCS3','PHA_COM_APPLYOUT','PHA_COM_APPLYOUT_bak');  --结束重定义  
end;

 

5、查询表数据是否分区成功

SELECT * FROM yccs3.PHA_COM_APPLYOUT partition (DATE2020_1);