Ways to move a table to another schema

There are several common ways to do this:
1, EXPDP/IMPDP

2, CTAs + parallel + nologin

The second method is to note that the primary key is not created in the new table

NOTNULLconstraints that were implicitly created by Oracle Database on columns of the selected table (for example, for Prima ry Keys) is not carried over to the new table.

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

3. Exchange Partition

The following tests are performed for the third method:
Create big_table script from Oracle database 9I10G11G programming art in-depth Databases Architecture (2nd edition), Conversion mode: Normal table a.a-> partition Table a.a_temp-> Plain table b.b
1. Create a Test table:

[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          &nbsP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;OWNER,&NBSP;OBJECT_NAME,&NBSP;SUBOBJECT_NAME,&NBSP;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&nbSp;  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);P l/sql  Procedure successfully completed.

To create an intermediate table:

[Email protected]> CREATE TABLE big_table_temp 2 PARTITION by RANGE (ID) 3 (PARTITION id_1 VALUES less THAN (M Axvalue)) 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);

For pinfo user authorization:

[Email protected]> Grant all on big_table to "PINFO"; [Email protected]> Grant all on big_table_temp to "PINFO";

Log in to pinfo to create a table with the same name:

[Email protected]> conn pinfo/adminconnected. [Email protected]> CREATE TABLE pinfo.big_table 2 as 3 SELECT * 4 from info.big_table 5 WHERE rownu M <= 0;

Log in to info and swap big_table to big_table_temp:

[Email protected]> conn info/admin[email protected]> ALTER TABLE big_table_temp EXCHANGE PARTITION id_1 with Table B Ig_table excluding INDEXES without VALIDATION; Table altered. elapsed:00:00:00.02# The excludeing option is used here, otherwise it will be reported ora-14098:index mismatch for tables in ALTER TABLE Exchange PARTITION, which can be exchanged at the end of the  Create an index manually [email protected]> select COUNT (*) from big_table;  COUNT (*)----------0[email protected]> Select COUNT (*) from info.big_table_temp; COUNT (*)----------8000000

Log in to pinfo and swap big_table_temp to 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

The complete interchange is almost millisecond.

You can also switch back in reverse:

[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 B Ig_table excluding INDEXES without VALIDATION;

The following is from Asktom, conversion mode: Normal table a.a-> partition table b.b

Reference: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P 11_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

This article is from the "HUNT" blog, make sure to keep this source http://hunt1574.blog.51cto.com/1390776/1957158

Ways to move a table to another schema