1.创建分区表

  1. create TABLESPACE job_part1 datafile size 10m;
  2. create TABLESPACE job_part2 datafile size 10m;
  3. create TABLESPACE job_part3 datafile size 10m;
  4. create TABLESPACE job_part4 datafile size 10m;
  7. CREATE TABLE fixf.MOCKDATA(
  8. ID INT,JobTitle VARCHAR(500),
  9. EmailAddress VARCHAR(500),
  10. FirstNameLastName VARCHAR(500),
  11. induct  DATE)
  12. partition by range(induct)
  13. (
  14. partition  induction2016 values less than (to_date('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace job_part1 ,
  15. partition  induction2017 values less than (to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace job_part2,
  16. partition  induction2018 values less than (to_date('2018-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace job_part3,
  17. partition  induction2019 values less than (to_date('2019-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace job_part4
  18. );
  21. select  *  from fixf.MOCKDATA  PARTITION(induction2016);
  22. select  *  from fixf.MOCKDATA  PARTITION(induction2017);
  23. select  *  from fixf.MOCKDATA  PARTITION(induction2018);
  24. select  *  from fixf.MOCKDATA  PARTITION(induction2019);
  27. insert  into  fixf.MOCKDATA  values(1,'engine','123@163.com','ued',to_date('2015-02-01 03:00:00','yyyy-mm-dd hh24:mi:ss'));
  28. insert  into  fixf.MOCKDATA  values(1,'engine','123@163.com','ued',to_date('2016-02-01 03:00:00','yyyy-mm-dd hh24:mi:ss'));
  29. insert  into  fixf.MOCKDATA  values(1,'engine','123@163.com','ued',to_date('2017-02-01 03:00:00','yyyy-mm-dd hh24:mi:ss'));
  30. insert  into  fixf.MOCKDATA  values(1,'engine','123@163.com','ued',to_date('2018-02-01 03:00:00','yyyy-mm-dd hh24:mi:ss'));
  31. commit;

2.导出分区数据

  1. 导出指定表
  2. expdp  fixf/12345   DIRECTORY=expdb  file=fixf.dmp log=fixf.log compress=y feedback=20  tables=MOCKDATA;
  3. 导出分区
  4. expdp  fixf/12345   DIRECTORY=expdb  file=fixf.dmp log=fixf.log compress=y feedback=20  tables=MOCKDATA:induction2017;

3.删除分区1数据并重新插入新的数据

  1. delete fixf.MOCKDATA  where induct  BETWEEN to_date('2015-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
  2. and    to_date('2015-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
  3. commit;
  5. insert  into  fixf.MOCKDATA  values(3,'cany','cany@qq.com','cany@jim',to_date('2015-04-01 05:00:00','yyyy-mm-dd hh24:mi:ss'));
  6. commit;
  7. 此时induction2016分区只有1条刚刚插入的数据，之前的数据被删除

4.创建directory

  1. create directory  expdb  as '/home/oracle/expdp';
  2. grant read, write  on directory expdb to fixf;

5.重新导入之前被删除的数据

  3. impdp fixf/12345 file=fixf.dmp  DIRECTORY=expdb    buffer=1000000    TABLE_EXISTS_ACTION=append   tables=fixf.MOCKDATA:induction2016;

异常处理： ORA-39171 ,ORA-01688

因为表空间不足导致导入的时候job 被挂起
ORA-39171: Job is experiencing a resumable wait. Resumable error: ORA-01688: unable to extend table FIXF.MOCKDATA partition INDUCTION2016 by 1024 in tablespace JOB_PART1
Resumable stmt: INSERT /+ APPEND ENABLE_PARALLEL_DML PARALLEL(“MOCKDATA”,1)+/ INTO RELATIONAL(“FIXF”.”MOCKDATA” NOT XMLTYPE) (“ID”, “JOBTITLE”, “EMAILADDRESS”, “FIRSTNAMELASTNAME”, “INDUCT”) SELECT “ID”, “JOBTITLE”, “EMAILADDRESS”, “FIRSTNAMELASTNAME”, “INDUCT” FROM “FIXF”.”ET$0190FD7F0001” KU$
Resumable stmt status: SUSPENDED
Resumable stmt start: 08/01/21 21:23:51 stmt suspend: 08/01/21 21:23:51