分区交换 alter table exchange partition 在线表 历史表交换

创建表test_part_1 默认为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));创建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
–创建中间表create table test_part_3(a number, b number);create index idx_id3 on test_part_3(a);–默认表空间users
–test_part_1 与中间表交换alter table test_part_1 exchange partition p1 with table test_part_3 including indexes免费云主机域名 with validation; –目标表有数据不能交换,交换只能是分区和非分区表交换–验证select * from dba_ind_partitions where index_name=upper(‘idx_id’);–p1的表空间变成了users，并且状态为usable，不用rebuildselect * from dba_indexes where index_name=upper(‘idx_id3’);–表空间变成了TS_KSZIP_BASE.select rowid from test_part_3;–AAAlz4AAEAAFTUEAAA 跟查询1对比可见 只是改了数据字典–创建目标分区表test_part_2create 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; –目标表有数据不能交换,交换只能是分区非分区交换select * from dba_ind_partitions where index_name=upper(‘idx_id2’);–索引p1可用,表空间依然是TS_KSZIP_BASE(因为此时 idx_id3表空间为TS_KSZIP_BASE)select * from dba_indexes where index_name=upper(‘idx_id3’);–表空间为TS_KSZIP_BASE，状态也是usable

相关推荐: Oracle中设置一个表为只读

如果一个表包含主键，或者具有唯一索引，可以使用以下语句将表置为只读：alter table xxx modify primary key disable validate或者alter table xxx disabl免费云主机域名e validate con…