如何快速安全的更改表的owner

eagle_fan

前两天有人问我这个问题，我说你先自己去搜索下答案吧。

后来他说没有找到合适的方法，我搜索了一下，竟然发现没有人提到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

复合分区表的情况大同小异，大家可以自己试验一下。


更多文章请看：http://www.dbafan.com :)

fengjin821

先顶后看

fengjin821

有意思，O(∩_∩)O~

wangkxxe

好贴！

gvora

tom书上分区那一章有讲到

〇〇

分区表不能直接与分区表交换所以
如果对换分区表，用普通表过渡
如果对换普通表，用分区表过渡

brucewoo_cu

学习一下.

ZALBB

dbms_redefinition 包里带了对象属主，估计用这个包效率也不错．

yanggq

Good

fanpig

顶一下