Oracle 不同用户之间 大量数据转移的方法测试

上个星期，一个朋友问我，同一个实例上，将某张表从A用户下移动到B用户。 表有1亿+条记录。 表很大。 当时说的方法的是用create table name as select * from B .tbname.

因为记录比较多，也不知道用这种方法的效率怎么样。 之前本本上有2个dump的测试库。 上次一不小心删了一个dump文件。 现在就剩一个。 刚才导入了一下。最大的分区表记录1200w. 数据量没有朋友的那个生产库大。 不过可以测试一下。

这里测试的同一个实例下的。 如果是不同实例间，可以采用数据泵+并行， 这样速度也会很快。 具体参考：

Oracle 插入大量数据

http://blog.csdn.net/tianlesoftware/archive/2009/10/29/4745144.aspx

exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项

http://blog.csdn.net/tianlesoftware/archive/2010/12/23/6093973.aspx

先看表的信息：

SQL> select sum(bytes)/1024/1024/1024 "GB" from dba_extents where SEGMENT_NAME='TAGENTOPRINFO' and owner='ICD';

GB

----------

1.03125

--单张分区表1G

SQL> select count(*) from TAGENTOPRINFO;

COUNT(*)

----------

12172500

-- 表中记录1200w

方法一： create table as select * from

SQL> set timing on

SQL> create table ta as select * from TAGENTOPRINFO;

表已创建。

已用时间: 00: 01: 04.01

SQL> select count(*) from ta;

COUNT(*)

----------

12172500

已用时间: 00: 00: 21.11

SQL>

注意： 新建的表没有原来表的索引和默认值, 只有非空(not null)的约束素条件可以继承过来,其它的约束条件或索引需要重新建立. 如果原始表是分区表，采用这种方式创建的是非分区表。

方法二：insert /*+APPEND */ into tbname select * from

--先创建表结构

SQL> create table tb as select * from TAGENTOPRINFO where 1=0;

表已创建。

已用时间: 00: 00: 00.05

SQL> select count(*) from tb;

COUNT(*)

----------

0

已用时间: 00: 00: 00.00

--insert data

SQL> insert /*+APPEND */ into tb select * from TAGENTOPRINFO;

已创建12172500行。

已用时间: 00: 00: 55.43

SQL> commit;

提交完成。

已用时间: 00: 00: 06.57

在这里再尝试用nologing模式，看看这次需要多长时间：

SQL> alter table tb nologging;

表已更改。

已用时间: 00: 00: 00.14

SQL> insert /*+APPEND */ into tb select * from TAGENTOPRINFO;

已创建12172500行。

已用时间: 00: 01: 13.78

SQL> commit;

提交完成。

已用时间: 00: 00: 06.57

SQL> alter table tb logging;

表已更改。

已用时间: 00: 00: 00.58

注意：

用INSERT /*+ APPEND */ 的方法会对目标表（这里的TB）产生级别为6的独占锁，如果运行此命令时还有对TB的DML操作会排队在它后面。

通过上面2个测试，时间都差不多。我还是在我的本本上测试的。 硬盘的读写能力比服务器硬盘还要差很多。 所以，如果在服务器上进行这种操作，还应该会快一点。

------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

网上资源： http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满)

DBA3 群：62697850 DBA 超级群：63306533;

聊天 群：40132017

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请