oracle修改表的owner,如何快速安全的更改表的owner

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

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