更改表的owner

最新推荐文章于 2023-12-09 15:51:31 发布

gguxxing008

最新推荐文章于 2023-12-09 15:51:31 发布

阅读量1.3k

点赞数

分类专栏： ORACLE常用知识文章标签： table sql exchange validation less insert

本文链接：https://blog.csdn.net/gguxxing008/article/details/7243122

版权

ORACLE常用知识专栏收录该内容

44 篇文章 0 订阅

订阅专栏

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; --temp表做过度
    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
复合分区表的情况大同小异，大家可以自己试验一下。
分区表不能直接与分区表交换，所以如果对换分区表，用普通表过渡，如果对换普通表，用分区表过渡！