Thursday, October 26, 2017

Create parallel processing in standard oracle edition

DECLARE
   l_task       VARCHAR2 (30) := 'task_del_dfupricepatric';
   l_sql_stmt   VARCHAR2 (32767);
   l_try        NUMBER;
   l_status     NUMBER;
BEGIN
   BEGIN
      DBMS_PARALLEL_EXECUTE.drop_task (l_task);
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

   DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid (
      task_name     => l_task,
      table_owner   => 'SCOTT',
      table_name    => 'DFUPRICERIC',
      by_row        => FALSE,
      chunk_size    => 10000);
   l_sql_stmt :=
      'DELETE FROM scpomgr.dfupricepetric WHERE startdate < sysdate -671
                 and rowid BETWEEN :start_id AND :end_id';

   DBMS_PARALLEL_EXECUTE.run_task (task_name        => l_task,
                                   sql_stmt         => l_sql_stmt,
                                   language_flag    => DBMS_SQL.NATIVE,
                                   parallel_level   => 100);

   COMMIT;
   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;
END;
/

No comments:

Post a Comment