您好,登錄后才能下訂單哦!
創(chuàng)建表test_part_1 默認(rèn)為users表空間:
create table test_part_1(a number, b number)
partition by range(a)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40)
);
創(chuàng)建test_part_1 本地索引
create index idx_id on test_part_1(a) local tablespace TS_KSZIP_BASE;
--插入記錄
insert into test_part_1 values(1,2);
insert into test_part_1 values(11,2);
insert into test_part_1 values(21,2);
insert into test_part_1 values(31,2);
commit;
--查看記錄
select rowid from test_part_1 where a=1;--AAAlz4AAEAAFTUEAAA 查詢1
--創(chuàng)建中間表
create table test_part_3(a number, b number);
create index idx_id3 on test_part_3(a);--默認(rèn)表空間users
--test_part_1 與中間表交換
alter table test_part_1 exchange partition p1 with table test_part_3 including indexes with validation; --目標(biāo)表有數(shù)據(jù)不能交換,交換只能是分區(qū)和非分區(qū)表交換
--驗(yàn)證
select * from dba_ind_partitions where index_name=upper('idx_id');--p1的表空間變成了users,并且狀態(tài)為usable,不用rebuild
select * from dba_indexes where index_name=upper('idx_id3');--表空間變成了TS_KSZIP_BASE.
select rowid from test_part_3;--AAAlz4AAEAAFTUEAAA 跟查詢1對(duì)比可見(jiàn) 只是改了數(shù)據(jù)字典
--創(chuàng)建 目標(biāo)分區(qū)表test_part_2
create table test_part_2(a number, b number)
partition by range(a)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40),
partition p5 values less than (50)
);
create index idx_id2 on test_part_2(a) local tablespace TS_KSZIP_BASE;
alter table test_part_2 exchange partition p1 with table test_part_3 including indexes with validation; --目標(biāo)表有數(shù)據(jù)不能交換,交換只能是分區(qū)非分區(qū)交換
select * from dba_ind_partitions where index_name=upper('idx_id2');--索引p1可用,表空間依然是TS_KSZIP_BASE(因?yàn)榇藭r(shí) idx_id3表空間為T(mén)S_KSZIP_BASE)
select * from dba_indexes where index_name=upper('idx_id3');--表空間為T(mén)S_KSZIP_BASE,狀態(tài)也是usable
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。