oracle - 基于触发器的分区创建

Question

我在外键上有一个列表分区表。因此，如果我想插入一个新实体，缺少的分区会在插入时引发异常。我以为我是个很酷的公爵，并使用触发器创建新分区 :-) 但该分区在执行过程中不会变得可用。如果您稍等一下，一切正常(但 dbms_lock.sleep 不起作用)。

所以这是我的触发器(和需要的程序)-注意接近末尾的“检查分区部分”

 CREATE OR REPLACE PROCEDURE Execute_DDL
   (i_sql IN VARCHAR2)
AS
  pragma autonomous_transaction;
BEGIN
  EXECUTE IMMEDIATE (i_sql);
  commit;
END;
/

.

CREATE OR REPLACE TRIGGER Q_FLD_NEW_PART_TRG
AFTER INSERT
ON Q_FLD 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  l_cnt number;
  l_wait_cnt number := 0;
  l_alter varchar2(1000);
  l_job_stmt varchar2(1000);
  l_job_nr number;
  l_job dba_jobs_running%rowtype;
BEGIN
  SELECT count(*) INTO l_cnt from user_tables 
     where table_name = 'QUOTE' and partitioned = 'YES';

   if l_cnt <= 0 then return; end if;

   l_alter := 'ALTER TABLE QUOTE ADD PARTITION QUOTE_PART_' || :new.name ||  ' VALUES (' || :new.id || ')';
   l_job_stmt := 'begin Execute_DDL (''' || l_alter || '''); end;'; 
   DBMS_JOB.SUBMIT (job => l_job_nr, what => l_job_stmt);

   if l_job_nr is null then 
     raise_application_error(-20000, 'Partition Job Creation failed!', true);   
   end if;

   -- wait for job to complete
   while l_job_nr is not null loop
     l_wait_cnt := l_wait_cnt +1;
     if l_wait_cnt > 30 then raise_application_error(-20000, 'pratition creation timed out!'); end if;

     begin            
       select * into l_job from dba_jobs_running where job = l_job_nr;
       if l_job.failures >0 then
         raise_application_error(-20000, l_job_stmt, true);
       end if;

       sys.dbms_lock.sleep(2);

       exception when no_data_found then
         l_job_nr := null;     -- job completed
     end;
   end loop;

   -- check partition available
   /* this will lead into a "no data found" exception. 
      so i can not use the new partition immediatly. why??
   sys.dbms_lock.sleep(2);
   select count(*) into l_cnt
     from  user_objects 
     where object_type = 'TABLE PARTITION'
     and   subobject_name = 'QUOTE_PART_' || upper(:new.name);

   if l_cnt <= 0 then
     raise_application_error(-20000, 'Partition creation falied/timed out: ' || 'QUOTE_PART_' || :new.name, true); 
   end if;
   */ 
   exception when others then
     raise_application_error(-20000, l_job_stmt, true);
END q_fld_new_part_trg;
/

任何人都有解决这个问题的想法？我在 Linux 上使用 11gR2 64 位

Answer 1

由于您使用的是 Oracle 11.2，是否有理由不使用 interval partitioning这里？假设 ID是一个数字列，这样的事情会告诉 Oracle 在每次插入新的 ID 时创建一个新分区值(value)。

SQL> create table interval_table(
  2    id number,
  3    value varchar2(10)
  4  )
  5  partition by range(id)
  6  interval( 1 )
  7  (
  8    partition initial_partition values less than (2)
  9  );

Table created.

SQL> insert into interval_table( id, value )
  2    values( 1, 'Initial' );

1 row created.

SQL> insert into interval_table( id, value )
  2    values( 10, 'New' );

1 row created.