表数据量大或者lob大表，根据rowid数据拆分
1）分片存储过程

 CREATE OR REPLACE PROCEDURE "SYS"."PROC_SPLIT_ROWID" (v_owner varchar2,v_segment_name varchar2,n_rangs number)
as
/*
CREATE TABLE SYS.TAB_SPLIT_ROWID
(
  OWNER        VARCHAR2(30 BYTE),
  TABLE_NAME   VARCHAR2(30 BYTE),
  ROWID_RANGE  VARCHAR2(100 BYTE)
)
TABLESPACE SYSTEM
*/
/*针对非分区表*/
begin
delete from tab_split_rowid ;--where owner=upper(v_owner) and table_name=upper(v_segment_name);
commit;
dbms_output.put_line(upper(v_owner));
dbms_output.put_line(upper(v_segment_name));
dbms_output.put_line(n_rangs);
insert into tab_split_rowid(owner,table_name,rowid_range)
select upper(v_owner),upper(v_segment_name),'where rowid between ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||''' and ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' ||';'
  from (select distinct b.rn,
                        first_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,
                        last_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,
                        first_value(decode(sign(range2 - range1),
                                           1,
                                           a.bid +
                                           ((b.rn - a.range1) * a.chunks1),
                                           a.bid)) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,
                        last_value(decode(sign(range2 - range1),
                                          1,
                                          a.bid +
                                          ((b.rn - a.range1 + 1) * a.chunks1) - 1,
                                          (a.bid + a.blocks - 1))) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2
          from (select fid,
                       bid,
                       blocks,
                       chunks1,
                       trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,
                       trunc((sum2 - 0.1) / chunks1) range2
                  from (select /*+ rule */
                         relative_fno fid,
                         block_id bid,
                         blocks,
                         sum(blocks) over() sum1,
                         trunc((sum(blocks) over()) / n_rangs) chunks1,
                         sum(blocks) over(order by relative_fno, block_id) sum2
                          from dba_extents
                         where segment_name = upper(v_segment_name)
                           and owner = upper(v_owner))
                 where sum1 > n_rangs) a,
               (select rownum - 1 rn
                  from dual
                connect by level <= n_rangs) b
         where b.rn between a.range1 and a.range2) c,
       (select max(data_object_id) oid
          from dba_objects
         where object_name = upper(v_segment_name)
           and owner = upper(v_owner)
           and data_object_id is not null) d;
           commit;
end;

2）使用例子

begin proc_split_rowid('scwy','TH_MESSAGEQUEUE',20);end;
/

select 'echo "'||'spool '||table_name||'_'||rownum||'.log'||chr(10)
||'INSERT INTO '||owner||'.'||table_name||' SELECT * FROM '||owner||'.'||table_name||'@DL_TO_OLD '
||rowid_range||chr(10)||'COMMIT;'||chr(10)||'spool off'||chr(10)||'quit;">'||table_name||'_'||rownum||'.sql' ||chr(10)
from tab_split_rowid  
union all
select 'echo "">'||table_name||'.sh' from tab_split_rowid  where   rownum=1
union all
select 'echo "nohup sqlplus system/Sm#2dbca@NEW_WBSDB @/home/oracle/scripts/mig/lob_tab/'||table_name||'_'||rownum||'.sql &">>'
||table_name||'.sh' ||chr(10)
from tab_split_rowid 
/

3）LOB对象ENABLE IN ROW

ALTER TABLE SCWY.TF_CARDRECHARGEITEAM 
  MOVE LOB (RESPONSEXML)
  STORE AS BASICFILE  (
    CHUNK  32768
    ENABLE STORAGE IN ROW
    TABLESPACE BUS
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ));