oracle分区表备份恢复操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
-- oracle分区表备份恢复 --1，测试数据： drop tablespace tbs2 including contents and datafiles; create tablespace tbs2 datafile '/ora01/app/oracle/oradata/prodc/tbs1.dbf' size 10M; create user loge1 identified by china default tablespace tbs2; grant connect,resource,dba to loge; drop tablespace tbs2 including contents and datafiles; create tablespace tbs2 datafile '/ora01/app/oracle/oradata/prodc/tbs1.dbf' size 10M; create user loge1 identified by china default tablespace tbs2; grant connect,resource,dba to loge1; -- 使用账号loge登陆执行 create table p_t1 (id int,datatime date) partition by range(datatime) (partition p1 values less than (to_date('2016-01-01','yyyy-mm-dd')), partition p2 values less than (to_date('2016-02-01','yyyy-mm-dd')), partition p3 values less than (to_date('2016-03-01','yyyy-mm-dd'))); insert into p_t1 values(1,to_date('2016-01-01','yyyy-mm-dd')); insert into p_t1 values(2,to_date('2016-02-01','yyyy-mm-dd')); insert into p_t1 values(3,to_date('2016-03-01','yyyy-mm-dd')); commit; -- 查看 select table_name,partition_name,high_value from user_tab_partitions where table_name='P_T1' select * from p_t1; select * from p_t1 partition(p1); select * from p_t1 partition(p2); -- 创建间隔分区 select table_name,partition_name,high_value from user_tab_partitions where table_name='P_T2' create table p_t2 (id int,datatime date) partition by range(datatime) interval(numtoyminterval(1,'MONTH')) (partition part0601 values less than (to_date('2016-02-01','yyyy-mm-dd'))); --不允许间隔分区添加分区 alter table p_t2 add partition part0602 values less than (to_date('2016-03-01','yyyy-mm-dd')); ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects -- 如果非间隔分区添加分区，比如大于最后一个分区，否则报错 alter table p_t1 add partition part0602 values less than (to_date('2016-03-01','yyyy-mm-dd')); ORA-14074: partition bound must collate higher than that of the last partition -- 拆分分区 alter table p_t1 split partition p3 at(to_date('2016-02-15','yyyy-mm-dd')) into (partition p31,partition p32); -- 合并分区 alter table p_t1 merge partitions p31,p32 into partition p3; -- 2，执行导出导入 -- exp导出 exp loge/china tables=p_t1 file=/home/oracle/bak/exp_table.dmp exp loge/china tables=p_t1:p1 file=/home/oracle/bak/exp_table_p1.dmp -- imp导入 imp loge1/china tables=p_t1 file=/home/oracle/bak/exp_table.dmp imp loge/china tables=p_t1:p2 file=/home/oracle/bak/exp_table_p1.dmp -- expdp导出 expdp loge/china directory=dump_dir dumpfile=dump_table.dmp tables=p_t1 -- expdp导入 impdp loge1/china directory=dump_dir dumpfile=dump_table.dmp tables=loge.p_t1 content=metadata_only remap_schema=loge:loge1 remap_tablespace=tbs1:tbs2 impdp loge1/china directory=dump_dir dumpfile=dump_table.dmp tables=loge.p_t1 content=data_only remap_schema=loge:loge1 remap_tablespace=tbs1:tbs2 表存在的几种处理：TABLE_EXISTS_ACTION APPEND, REPLACE, [SKIP] 和 TRUNCATE |
注意： 如果是自己创建的分区比如按照job创建的分区，注意建表的ddl，否则导入失败