可以有以下几种常用的办法:
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.创建测试表:
info@PROD> 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 info@PROD> alter table big_table nologging; Table altered. Elapsed: 00:00:00.01 info@PROD> 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: 8000000 old 3: l_rows number := &1; new 3: l_rows number := 8000000; Enter value for 1: 8000000 old 9: where rownum <= &1; new 9: where rownum <= 8000000; PL/SQL procedure successfully completed. Elapsed: 00:00:07.73 info@PROD> select count(*) from big_table; COUNT(*) ---------- 8000000 Elapsed: 00:00:01.86 info@PROD> alter table big_table add constraint big_table_pk primary key(id); Table altered. Elapsed: 00:00:38.63 info@PROD> info@PROD> exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1); PL/SQL procedure successfully completed.
创建中间表:
info@PROD> 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; info@PROD> alter table big_table_temp add constraint pk_big_table_temp_id primary key(id);
为pinfo用户授权:
info@PROD> grant ALL on big_table to "PINFO"; info@PROD> grant ALL on big_table_temp to "PINFO";
登录pinfo,创建info同名表:
info@PROD> conn pinfo/admin Connected. pinfo@PROD> CREATE TABLE pinfo.big_table 2 AS 3 SELECT * 4 FROM info.big_table 5 WHERE ROWNUM <= 0;
登录info,将big_table交换至big_table_temp:
pinfo@PROD> conn info/admin info@PROD> 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,可以在交换完成以后手动创建索引 info@PROD> select count(*) from big_table; COUNT(*) ---------- 0 info@PROD> select count(*) from info.big_table_temp; COUNT(*) ---------- 8000000
登录pinfo,将big_table_temp交换至big_table:
pinfo@PROD> 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 pinfo@PROD> select count(*) from big_table; COUNT(*) ---------- 8000000 Elapsed: 00:00:02.91 pinfo@PROD> select count(*) from info.big_table_temp; COUNT(*) ---------- 0
完成交换几乎是毫秒级的。
也可以反向交换回去:
pinfo@PROD> ALTER TABLE info.big_table_temp EXCHANGE PARTITION id_1 WITH TABLE pinfo.big_table EXCLUDING INDEXES WITHOUT VALIDATION; info@PROD> conn info/admin info@PROD> 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
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。