oracle 大表复制 存储过程

cu_Cbear

oracle 大表复制 存储过程

oracle 大表复制 存储过程 一般我们采用复制表的方式主要是 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优化的关键 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/w46523173/archive/2009/12/05/4945701.aspx

寂寞冲咖啡

沙发