前两天有人问我这个问题，我说你先自己去搜索下答案吧。
后来他说没有找到合适的方法，我搜索了一下，竟然发现没有人提到exchange partition的方法。
exchange partition的方法是最安全，也是最快速的方法。
所以这里写出这种方法供大家参考。
假设有A,B两个用户，我们想把TEST表从A用户移到B用户。
我们以非分区表作为例子:
SQL> conn a/a
Connected.
SQL> create table test(x int);
Table created.
SQL> create index test_idx on test(x);
Index created.
SQL> insert into test select rownum from dual connect by level <10000;
9999 rows created.
SQL> commit;
Commit complete.
SQL> conn b/b
Connected.
SQL> create table temp(x int) partition by range (x)
2 (partition part0 values less than (-1),
3 partition part1 values less than (maxvalue));
Table created.
SQL> create table test(x int);
Table created.
SQL> create index temp_idx on temp(x) local;
Index created.
SQL> create index test_idx on test(x);
Index created.
SQL> alter table temp exchange partition part1 with table a.test including indexes without validation;
Table altered.
SQL> alter table temp exchange partition part1 with table test including indexes without validation;
Table altered.
SQL> select count(*) from a.test;
COUNT(*)
----------
0
SQL> select count(*) from b.test;
COUNT(*)
----------
9999
如果是分区表，操作过程如下：
SQL> conn a/a
Connected.
SQL> create table test(x int) partition by range (x)
2 (partition part0 values less than (100),
3 partition part1 values less than (maxvalue));
Table created.
SQL> create index test_idx on test(x) local;
Index created.
SQL> insert into test select rownum from dual connect by level <1000;
999 rows created.
SQL> commit;
Commit complete.
SQL> conn b/b
Connected.
SQL> create table temp(x int);
Table created.
SQL> create index temp_idx on temp(x);
Index created.
SQL> create table test(x int) partition by range (x)
2 (partition part0 values less than (100),
3 partition part1 values less than (maxvalue));
Table created.
SQL> create index test_idx on test(x) local;
Index created.
SQL> select count(*) from a.test;
COUNT(*)
----------
999
SQL> select count(*) from b.test;
COUNT(*)
----------
0
SQL> alter table a.test exchange partition part0 with table temp including indexes without validation;
Table altered.
SQL> alter table test exchange partition part0 with table temp including indexes without validation;
Table altered.
SQL> select count(*) from a.test;
COUNT(*)
----------
900
SQL> select count(*) from b.test;
COUNT(*)
----------
99
SQL> alter table a.test exchange partition part1 with table temp including indexes without validation;
Table altered.
SQL> alter table test exchange partition part1 with table temp including indexes without validation;
Table altered.
SQL> select count(*) from a.test;
COUNT(*)
----------
0
SQL> select count(*) from b.test;
COUNT(*)
----------
999
复合分区表的情况大同小异，大家可以自己试验一下。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-2 9:27:46 |
赞(
操作
