oracle 复制表方法,(转)oracle数据库之间 表复制方法一（extent 方法）

一般我们采用复制表的方式主要是

create table table_name

as select /*+ parallel (t 10)*/ * from t_name t ...

insert /*+ append */ into table_name

select /*+ parallel (t 10)*/ * from tname t ...

这两者方法都没有问题，但如果数据量到达一定程度，比如说10亿，大小400G，而且表上还存在业务，这样的话，很容易出现01555的问题。 我在运行了3个小时后遇到了恼人的01555，将undo_retention改到一个足够大的值还是不行，毕竟表太大，而且表上有业务在更新数据。

有一种方式可以避免01555，可以从物理备库恢复到某个时刻后，从备库表拖数据，这样上面的问题是没有了，不过有一点是需要考虑的，通过单dblink的话是有网络流量限制的，一般是20M/S，这样400G的表需要7个小时，还是太慢了。

最终考虑采用extent的方式，一块一块的拖，在我预想这样的速度应该会比直接在主库复制一个新表要慢一下的。

事实胜于雄辩，采用extent的方式，开12个进程，花了2个半小时完成了整个表的复制。不开12个并行直接复制表快了30%，而且采用extnt的方式比较灵活，可以在很多情况下继续上次为完成的工作，就是所谓的断点了,当然这种方式也是有一定代价的，负载会比前面的方法至少高一倍以上

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

create table MY_ROWID

(

ID        NUMBER,

ROWID_MIN VARCHAR2(100),

ROWID_MAX VARCHAR2(100),

HAS_DEAL NUMBER

);

insert into my_rowid(id,rowid_min,rowid_max,has_deal)

select rownum,

DBMS_ROWID.ROWID_CREATE(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID,0),

DBMS_ROWID.ROWID_CREATE(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1,10000),

0

from dba_extents e,dba_objects o

where e.segment_name=upper('base_table')

and e.owner='FBADMIN'

AND o.object_name = upper('base_table')

AND o.owner='FBADMIN';

commit;

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

CREATE OR REPLACE PROCEDURE SP_XF_COPY_TABLE(N NUMBER) IS

/*

复制评价表

2009-8-26

*/

V_SQLERRM VARCHAR2(200);

BEGIN

FOR C IN (SELECT ID, ROWID_MIN, ROWID_MAX

FROM MY_ROWID

WHERE HAS_DEAL = 0

AND MOD(ID, 12) = N) LOOP

INSERT INTO

SELECT /*+ rowid(t) */

*

FROM base_table t

WHERE ROWID >= CHARTOROWID(C.ROWID_MIN)

AND ROWID <= CHARTOROWID(C.ROWID_MAX);

UPDATE MY_ROWID SET HAS_DEAL = 1 WHERE ID = C.ID;

COMMIT;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

V_SQLERRM := SUBSTR(SQLERRM, 1, 200);

DBMS_OUTPUT.PUT_LINE(V_SQLERRM);

ROLLBACK;

END SP_XF_COPY_TABLE;

---------

尽量使用后台跑脚本

nohup $HOME/worksh/sp_xf_tmp00.sh >/tmp/sp_xf_tmp00.txt &

--check data

SELECT COUNT(*) from my_rowid t WHERE has_deal = 0;

~~~~~~~~~~~~~~~~~~~~~

很多情况下可以使用extent来处理大表的数据

--EOF--

insert /*+APPEND */into mytable(select 列名,列名,...... from i_mytable);这样估计要快很多

如果需要更快

需要用PLSQL

declare

i number(10);

begin

insert /*+APPEND */into mytable(select 列名,列名,...... from i_mytable);

i:=sql%rowcount;

if i%1000=0 then

commit;

end if;

end;

强制批量插入  并且 不要在大表使用*函数  是SQL优化的关键