oracle分区表备份恢复

2016-12-30 09:55:31来源:oschina作者:wi_wi人点击

-- 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,否则导入失败

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台