ORACLE普通表转分区表__使用ROWID将表切片迁移数据

利用11G的新特性DBMS_PARALLEL_EXECUTE 按 ROWID 对表进行分块，具体参考文章 https://blog.csdn.net/u010692693/article/details/103048115

普通表

SQL> SELECT NUM_COL, COUNT(*) FROM TEST_TAB
  2  GROUP BY NUM_COL ORDER BY NUM_COL;

   NUM_COL   COUNT(*)
---------- ----------
        20     100000
        30     133333
        40     266667

创建分区表

CREATE TABLE test_tab_new
(
  REC_ID      NUMBER,
  DESCRIPTION VARCHAR2(50),
  NUM_COL     NUMBER
)
partition by list (NUM_COL)
 (
   PARTITION PART_20 VALUES ('20'), 
   PARTITION PART_30 VALUES ('30'), 
   PARTITION PART_40 VALUES ('40')
);

分块迁移

BEGIN
  DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/


DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  -- Create the TASK  创建任务
  DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
 
  -- Chunk the table by the ROWID  使用ROWID分块
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
  (
    TASK_NAME   => l_task,
    TABLE_OWNER => 'TESTZYL', /* Replace the TABLE_OWNER as appropriately */
    TABLE_NAME  => 'TEST_TAB',
    BY_ROW      => TRUE,
    CHUNK_SIZE  => 2500
  );
 
  -- DML to be execute in parallel  要并行执行的DML
  l_sql_stmt := 'insert into test_tab_new select * from test_tab WHERE rowid BETWEEN :start_id AND :end_id';
 
  -- Run the task   执行任务
  DBMS_PARALLEL_EXECUTE.RUN_TASK
  (
    TASK_NAME      => l_task,
    SQL_STMT       => l_sql_stmt,
    LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
    PARALLEL_LEVEL => 10
  );
 
  -- If there is error, RESUME it for at most 2 times. 
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
 
  WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  LOOP
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task);
    l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
  END LOOP;
 
  -- Done with processing; drop the task    完成处理，删除任务
  DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task);
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
END;
/

 

SQL> select count(*) from test_tab_new partition (PART_20);

  COUNT(*)
----------
    100000

SQL> select count(*) from test_tab_new partition (PART_30);

  COUNT(*)
----------
    133333

SQL> select count(*) from test_tab_new partition (PART_40);

  COUNT(*)
----------
    266667

SQL> alter table test_tab rename to test_tab_old##;

Table altered.

SQL> alter table test_tab_new rename to test_tab;

Table altered.