移動一個表到另一個schema的方法

標籤：exchange

可以有以下幾種常用的辦法：
1、expdp/impdp

2、ctas + parallel + nologin

     第二種方法要注意主鍵在新表是沒有建立的

NOT NULL constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm

3、exchange partition

以下針對第三種方法進行測試：
建立big_table指令碼來自Oracle Database 9i10g11g編程藝術深入資料庫體繫結構(第2版)，轉換方式：普通表A.A->分區表A.A_TEMP->普通表B.B
1.建立測試表：

[email protected]> create table big_table  2  as  3  select rownum id, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.O  3    3  select rownum id, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.OBJECT_ID, a.DATA_OBJECT_ID  4    from all_objects a  5   where 1=0  6  /Table created.Elapsed: 00:00:00.09[email protected]> alter table big_table nologging;Table altered.Elapsed: 00:00:00.01[email protected]> declare  2      l_cnt number;  3      l_rows number := &1;  4  begin  5      insert /*+ append */  6      into big_table  7      select rownum, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.OBJECT_ID, a.DATA_OBJECT_ID  8        from all_objects a  9   where rownum <= &1; 10   11      l_cnt := sql%rowcount; 12   13      commit; 14   15      while (l_cnt < l_rows) 16      loop 17          insert /*+ APPEND */ into big_table 18          select rownum+l_cnt,  19                 OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID 20            from big_table 21           where rownum <= l_rows-l_cnt; 22          l_cnt := l_cnt + sql%rowcount; 23          commit; 24      end loop; 25  end; 26  /Enter value for 1: 8000000old   3:     l_rows number := &1;new   3:     l_rows number := 8000000;Enter value for 1: 8000000old   9:  where rownum <= &1;new   9:  where rownum <= 8000000;PL/SQL procedure successfully completed.Elapsed: 00:00:07.73[email protected]> select count(*) from big_table;  COUNT(*)----------   8000000Elapsed: 00:00:01.86[email protected]> alter table big_table add constraint big_table_pk primary key(id);Table altered.Elapsed: 00:00:38.63[email protected]> [email protected]> exec dbms_stats.gather_table_stats( user, ‘BIG_TABLE‘, estimate_percent=> 1);PL/SQL procedure successfully completed.

建立中間表：

[email protected]> CREATE TABLE big_table_temp   2    PARTITION BY RANGE (id)   3   (PARTITION id_1 VALUES LESS THAN (MAXVALUE))   4    AS   5     SELECT *   6       FROM big_table   7      WHERE ROWNUM <= 0;[email protected]> alter table big_table_temp add constraint pk_big_table_temp_id primary key(id);

為pinfo使用者授權：

[email protected]>  grant ALL on big_table to "PINFO";[email protected]>  grant ALL on big_table_temp to "PINFO";

登入pinfo，建立info同名表：

[email protected]> conn pinfo/adminConnected.[email protected]> CREATE TABLE pinfo.big_table  2  AS  3     SELECT *  4       FROM info.big_table  5      WHERE ROWNUM <= 0;

登入info，將big_table交換至big_table_temp:

[email protected]> conn info/admin[email protected]> ALTER TABLE big_table_temp EXCHANGE PARTITION id_1 WITH TABLE big_table EXCLUDING INDEXES WITHOUT VALIDATION;Table altered.Elapsed: 00:00:00.02#此處使用了excludeing選項，否則會報 ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION，可以在交換完成以後手動建立索引[email protected]> select count(*) from big_table;  COUNT(*)----------   0[email protected]> select count(*) from  info.big_table_temp;  COUNT(*)----------  8000000

登入pinfo，將big_table_temp交換至big_table:

[email protected]> ALTER TABLE info.big_table_temp EXCHANGE PARTITION id_1 WITH TABLE pinfo.big_table EXCLUDING INDEXES WITHOUT VALIDATION;Table altered.Elapsed: 00:00:00.01[email protected]> select count(*) from big_table;  COUNT(*)----------   8000000Elapsed: 00:00:02.91[email protected]> select count(*) from  info.big_table_temp;  COUNT(*)----------         0

完成交換幾乎是毫秒級的。

也可以反向交換回去：

[email protected]> ALTER TABLE info.big_table_temp EXCHANGE PARTITION id_1 WITH TABLE pinfo.big_table EXCLUDING INDEXES WITHOUT VALIDATION;[email protected]> conn info/admin[email protected]> ALTER TABLE big_table_temp EXCHANGE PARTITION id_1 WITH TABLE big_table exCLUDING INDEXES WITHOUT VALIDATION;

以下內容來自asktom，轉換方式：普通表A.A->分區表B.B

參考：https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:752030266230

To quickly move big tables between schemas  use EXCHANGE PARTITION feature of Oracle 8i.for example:SQL> connect as user "A"SQL> create table large_table     (       a number,       b char,       c date     )-- just for this example only. :)SQL> grant ALL on large_table to "B";SQL> connect as user "B"SQL> create table large_table      (       a number,         b char,           c date     )     partition by range (a)     (       partition dummy values less than (maxvalue)     )Then you can use the following command to quickly move "A.large_table" to "B.large_table"SQL> connect as user "B";SQL> alter table large_table exchange partition dummy     with table A.large_table;And return it back to schema A:SQL> alter table large_table exchange partition dummy     with table A.large_table;-- of course, it is the same SQL command

本文出自 “HUNT” 部落格，請務必保留此出處http://hunt1574.blog.51cto.com/1390776/1957158

移動一個表到另一個schema的方法