Oracle表分区

orders表分区

分区之前先通过下面expdp命令备份表

expdp mre/dcdt@orcl tables=orders DIRECTORY=dcdt_dump_dir dumpfile=orders20220920.dmp logfile=orders20220920.log   

然后给数据库原表重命名备份下，通过下面建表语句重新创建表

-- Create table
create table ORDERS
(
  record_id                VARCHAR2(64),
  visit_id                 VARCHAR2(32),
  patient_id               VARCHAR2(32),
  order_no                 VARCHAR2(32),
  order_sub_no             VARCHAR2(32),
  repeat_indicator         VARCHAR2(16),
  order_class_code         VARCHAR2(32),
  order_class_name         VARCHAR2(32),
  order_text               VARCHAR2(256),
  order_code               VARCHAR2(200),
  drug_name                VARCHAR2(64),
  dosage                   VARCHAR2(32),
  dosage_units_code        VARCHAR2(32),
  dosage_units_name        VARCHAR2(32),
  administration_code      VARCHAR2(32),
  administration_name      VARCHAR2(64),
  start_date_time          DATE,
  stop_date_time           DATE,
  duration                 VARCHAR2(32),
  duration_units_code      VARCHAR2(32),
  duration_units_name      VARCHAR2(64),
  frequency_code           VARCHAR2(32),
  frequency_name           VARCHAR2(64),
  freq_counter             VARCHAR2(50),
  freq_interval            VARCHAR2(8),
  freq_interval_unit_code  VARCHAR2(32),
  freq_interval_unit_name  VARCHAR2(64),
  freq_detail              VARCHAR2(128),
  perform_schedule         VARCHAR2(320),
  perform_result           VARCHAR2(64),
  ordering_dept_code       VARCHAR2(32),
  ordering_dept_name       VARCHAR2(64),
  doctor_code              VARCHAR2(32),
  doctor_name              VARCHAR2(32),
  stop_doctor_code         VARCHAR2(32),
  stop_doctor_name         VARCHAR2(32),
  nurse_code               VARCHAR2(32),
  nurse_name               VARCHAR2(32),
  stop_nurse_code          VARCHAR2(32),
  stop_nurse_name          VARCHAR2(32),
  enter_date_time          DATE,
  stop_order_date_time     DATE,
  order_status_code        VARCHAR2(32),
  order_status_name        VARCHAR2(64),
  drug_billing_attr        VARCHAR2(32),
  billing_attr             VARCHAR2(32),
  last_perform_date_time   DATE,
  last_accting_date_time   DATE,
  category_id              VARCHAR2(32),
  category_name            VARCHAR2(50),
  drug_spec                VARCHAR2(64),
  inp_flag                 NUMBER(1),
  drug_spell               VARCHAR2(64),
  anti_drug_level          NUMBER,
  group_id                 VARCHAR2(32),
  check_state              NUMBER(1) default 0,
  check_error_flag         NUMBER(1) default 0,
  id                       VARCHAR2(64) not null,
  machine_check_error_flag VARCHAR2(64) default 0,
  choose_flag              INTEGER default 0,
  manufacturer             VARCHAR2(500),
  is_prevention_drug       NUMBER,
  drug_classes             VARCHAR2(255),
  is_discharged            NUMBER(1),
  dispense_pharmacist_name VARCHAR2(50),
  check_pharmacist_name    VARCHAR2(50),
  is_consultation          NUMBER(1),
  is_etiology_send         NUMBER(1),
  is_critical              NUMBER(1),
  drug_use_days            NUMBER,
  drug_category0           VARCHAR2(32),
  drug_category1           VARCHAR2(32),
  drug_category2           VARCHAR2(32),
  drug_category3           VARCHAR2(32),
  drug_category4           VARCHAR2(32),
  drug_category5           VARCHAR2(32),
  drug_category6           VARCHAR2(32),
  drug_category7           VARCHAR2(32),
  dispensary_name          VARCHAR2(64),
  dispensary_code          VARCHAR2(32),
  performed_by_code        VARCHAR2(32),
  performed_by_name        VARCHAR2(32),
  ward_code                VARCHAR2(32),
  ward_name                VARCHAR2(32),
  hospital_id              VARCHAR2(32),
  branch_id                VARCHAR2(32),
  medical_group_code       VARCHAR2(32),
  medical_group_name       VARCHAR2(64),
  is_anti_tb               NUMBER(1) default 0 not null,
  is_basedrug              NUMBER(1),
  order_property           VARCHAR2(64),
  skin_test                VARCHAR2(8),
  skin_test_result         VARCHAR2(512),
  instruct_drug_speed      VARCHAR2(64),
  instruct_drug_speed_unit VARCHAR2(64),
  preventive_medication    VARCHAR2(512),
  munual_check             VARCHAR2(8),
  drug_remark              VARCHAR2(256)
)
tablespace MRE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 216
    next 8
    minextents 1
    maxextents unlimited
  )
   PARTITION BY RANGE (start_date_time) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) STORE IN  ("MRE") 
 (PARTITION "IBD201804"  VALUES LESS THAN (TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MRE" NOCOMPRESS );
-- Add comments to the columns 
comment on column ORDERS.record_id
  is '病历号';
comment on column ORDERS.visit_id
  is '病人本次住院标识';
comment on column ORDERS.patient_id
  is '病人标识号';
comment on column ORDERS.order_no
  is '医嘱序号';
comment on column ORDERS.order_sub_no
  is '医嘱子序号';
comment on column ORDERS.repeat_indicator
  is '长期医嘱标志';
comment on column ORDERS.order_class_code
  is '医嘱类别代码';
comment on column ORDERS.order_class_name
  is '医嘱类别名称';
comment on column ORDERS.order_text
  is '医嘱正文';
comment on column ORDERS.order_code
  is '医嘱代码';
comment on column ORDERS.drug_name
  is '药品名称';
comment on column ORDERS.dosage
  is '药品一次使用剂量';
comment on column ORDERS.dosage_units_code
  is '剂量单位代码';
comment on column ORDERS.dosage_units_name
  is '剂量单位名称';
comment on column ORDERS.administration_code
  is '给药途径代码';
comment on column ORDERS.administration_name
  is '给药途径名称';
comment on column ORDERS.start_date_time
  is '起始日期及时间';
comment on column ORDERS.stop_date_time
  is '停止日期及时间';
comment on column ORDERS.duration
  is '持续时间';
comment on column ORDERS.duration_units_code
  is '持续时间单位代码';
comment on column ORDERS.duration_units_name
  is '持续时间单位名称';
comment on column ORDERS.frequency_code
  is '执行频率代码';
comment on column ORDERS.frequency_name
  is '执行频率名称';
comment on column ORDERS.freq_counter
  is '执行频次';
comment on column ORDERS.freq_interval
  is '频率间隔';
comment on column ORDERS.freq_interval_unit_code
  is '频率间隔单位代码';
comment on column ORDERS.freq_interval_unit_name
  is '频率间隔单位名称';
comment on column ORDERS.freq_detail
  is '执行时间详细描述';
comment on column ORDERS.perform_schedule
  is '护士执行时间';
comment on column ORDERS.perform_result
  is '执行结果';
comment on column ORDERS.ordering_dept_code
  is '开医嘱科室代码';
comment on column ORDERS.ordering_dept_name
  is '开医嘱科室名称';
comment on column ORDERS.doctor_code
  is '开医嘱医生代码';
comment on column ORDERS.doctor_name
  is '开医嘱医生姓名';
comment on column ORDERS.stop_doctor_code
  is '停医嘱医生代码';
comment on column ORDERS.stop_doctor_name
  is '停医嘱医生姓名';
comment on column ORDERS.nurse_code
  is '开医嘱校对护士代码';
comment on column ORDERS.nurse_name
  is '开医嘱校对护士姓名';
comment on column ORDERS.stop_nurse_code
  is '停医嘱校对护士代码';
comment on column ORDERS.stop_nurse_name
  is '停医嘱校对护士姓名';
comment on column ORDERS.enter_date_time
  is '开医嘱录入日期及时间';
comment on column ORDERS.stop_order_date_time
  is '开医嘱录入日期及时间';
comment on column ORDERS.order_status_code
  is '医嘱状态代码';
comment on column ORDERS.order_status_name
  is '医嘱状态名称';
comment on column ORDERS.drug_billing_attr
  is '药品计价属性';
comment on column ORDERS.billing_attr
  is '计价属性';
comment on column ORDERS.last_perform_date_time
  is '最后一次执行日期及时间';
comment on column ORDERS.last_accting_date_time
  is '最后一次计价日期及时间';
comment on column ORDERS.category_id
  is '药品类别代码';
comment on column ORDERS.category_name
  is '药品类别名称';
comment on column ORDERS.drug_spec
  is '药品规格';
comment on column ORDERS.inp_flag
  is '在院标识 0：出院病人的医嘱；1：在院病人的医嘱';
comment on column ORDERS.drug_spell
  is '药品拼音缩写码';
comment on column ORDERS.anti_drug_level
  is '抗菌药等级';
comment on column ORDERS.group_id
  is '组号';
comment on column ORDERS.is_prevention_drug
  is '是否预防用药';
comment on column ORDERS.drug_classes
  is '自定义药理分类';
comment on column ORDERS.is_discharged
  is '是否出院带药 1：是；0：不是';
comment on column ORDERS.dispense_pharmacist_name
  is '核对药师';
comment on column ORDERS.check_pharmacist_name
  is '审核药师';
comment on column ORDERS.is_consultation
  is '感染性疾病科是否会诊 0否1是（针对特殊级抗菌药）';
comment on column ORDERS.is_etiology_send
  is '使用前是否微生物送检 0否1是（针对特殊级抗菌药）';
comment on column ORDERS.is_critical
  is '是否紧急情况下使用 0否1是（针对特殊级抗菌药）';
comment on column ORDERS.drug_use_days
  is '用药天数';
comment on column ORDERS.drug_category0
  is '药理分类0';
comment on column ORDERS.drug_category1
  is '药理分类1';
comment on column ORDERS.drug_category2
  is '药理分类2';
comment on column ORDERS.drug_category3
  is '药理分类3';
comment on column ORDERS.drug_category4
  is '药理分类4';
comment on column ORDERS.drug_category5
  is '药理分类5';
comment on column ORDERS.drug_category6
  is '药理分类6';
comment on column ORDERS.drug_category7
  is '药理分类7';
comment on column ORDERS.dispensary_name
  is '发药药房名称';
comment on column ORDERS.dispensary_code
  is '发药药房代码';
comment on column ORDERS.performed_by_code
  is '执行科室代码';
comment on column ORDERS.performed_by_name
  is '执行科室名称';
comment on column ORDERS.ward_code
  is '病区代码';
comment on column ORDERS.ward_name
  is '病区名称';
comment on column ORDERS.hospital_id
  is '医院ID';
comment on column ORDERS.branch_id
  is '院区ID';
comment on column ORDERS.medical_group_code
  is '医疗组编码';
comment on column ORDERS.medical_group_name
  is '医疗组名称';
comment on column ORDERS.is_anti_tb
  is '是否抗结核用药,0否1是';
comment on column ORDERS.is_basedrug
  is '基药标识，0否1是';
comment on column ORDERS.order_property
  is '二级医嘱类型，如嘱托临嘱、手术临嘱、处方取药等';
comment on column ORDERS.skin_test
  is '医生填写的是否需要皮试标记,1:是 0:否';
comment on column ORDERS.skin_test_result
  is '医生填写的皮试结果';
comment on column ORDERS.instruct_drug_speed
  is '滴注的给药速度';
comment on column ORDERS.instruct_drug_speed_unit
  is '滴注的给药速度单位';
comment on column ORDERS.preventive_medication
  is '抗菌药用药目的';
comment on column ORDERS.munual_check
  is '围手术期抗菌药是否需要走人工审核';
comment on column ORDERS.drug_remark
  is '药品备注（医生填写的说明，特殊情况等可以在这里注明）';
-- Create/Recreate indexes 
create index INP_FLAG_INDEX on ORDERS (INP_FLAG)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 3M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INP_ORDER_CODE_INDEX on ORDERS (ORDER_CODE)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_CLASS_CODE_INDEX on ORDERS (ORDER_CLASS_CODE)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 3M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_DRUG_NAME_INDEX on ORDERS (DRUG_NAME)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_ID_INDEX on ORDERS (ID)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_PID_VID_INDEX on ORDERS (PATIENT_ID, VISIT_ID)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_RECORD_ID_INDEX on ORDERS (RECORD_ID)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_REPEAT_INDEX on ORDERS (REPEAT_INDICATOR)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 3M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_START_DATE_INDEX on ORDERS (START_DATE_TIME)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 4M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index ORDERS_STOP_DATE_INDEX on ORDERS (STOP_DATE_TIME)
  tablespace MRE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ORDERS
  add constraint ORDERS_ID primary key (ID);

创建好之后通过下面语句导入表数据

impdp mre/password@orcl tables=orders  directory=dcdt_dump_dir  dumpfile=orders20220920.dmp  logfile=orders20220920.log  table_exists_action=append