假设有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