oracle - Execute multiple Oracle SQL statements simultaneously

Question

I have an Oracle database that has 20 very large tables, each with hundreds of partitions. I can compress a table but it takes over 2 hours. This would mean over 40 hours to complete all 20 tables. I would like to run the partition compression simultaneously (1 per table). Because the partitions are added daily I need a utility to generate the "alter table ..." syntax at the time of the run. So far, all I can think of is to create a SQL for each of the 20 tables and their partitions, then run them in 20 SQLPlus sessions.

Is there a better, more automated way to do this?

标签： oracleplsqloracle11gr2

Answer 1

You could submit several jobs, which will - in turn - run your code simultaneously. Here's an example:

Test tables - I want to modify ID's datatype to VARCHAR2(10)

SQL> create table t1 (id varchar2(2));

Table created.

SQL> create table t2 (id varchar2(2));

Table created.

SQL> create table t3 (id varchar2(2));

Table created.

A procedure which will utilize EXECUTE IMMEDIATE and is called from DBMS_JOB (see below):

SQL> create or replace procedure p_exe (par_what in varchar2) is
  2  begin
  3    execute immediate par_what;
  4  end;
  5  /

Procedure created.

Create jobs which will run the ALTER TABLE simultaneously

SQL> declare
  2    l_str varchar2(200);
  3    l_job number;
  4  begin
  5    for cur_r in (select 't1' table_name from dual union all
  6                  select 't2'            from dual union all
  7                  select 't3'            from dual)
  8    loop
  9      l_str := 'alter table ' || cur_r.table_name || ' modify id varchar2(10)';
 10      dbms_output.put_line(l_str);
 11      dbms_job.submit(l_job,
 12                      'p_exe(' || chr(39) || l_str || chr(39) ||');',
 13                      sysdate
 14                     );
 15      commit;
 16    end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.

Jobs are now running; in a few moments (in my case, as it is a simple one - you'd check that somewhat later), check what's being done:

SQL> desc t1;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               VARCHAR2(10)

SQL> desc t3;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               VARCHAR2(10)

SQL> desc t3;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               VARCHAR2(10)

SQL>