oracle scheduler to rename interval partitions

Question

I get some strange behaviour. I have a procedure which changes in the names of partitions in a table. I created a job which runs this procedure every 2 mins for testing. First run goes very smooth and without any error so far. However, from 2nd run I get the following error

"ORA-00054: resource busy and acquire with NOWAIT  specified or timeout expired
       ORA-06512: at "CDS_USER.RENAMING_PARTITIONS", line 17"

Yes, My table is live. But when I run my procedure explicitly withoug the scheduler, it runs perfectly fine. No issues with that. And every first run by the scheduler is also perfect.

Here is the code for scheduler

    begin
    bms_Scheduler.create_job(
    job_name => 'drop_user1' ,
    job_action => 'RENAMING_PARTITIONS' 
    job_action => 'DROP_PARTITIONS' 
    ,start_date => SYSDATE ,
     repeat_interval => 'freq=hourly;INTERVAL=7',bysecond=0;' ,
     enabled => TRUE ,
     comments => 'schduling drop job.');
     END;

Here is the code for procedure

     create or replace PROCEDURE RENAMING_PARTITIONS
   AS
   hv varchar2(9);
   max_part VARCHAR2(9);
   begin
   select  max(partition_position)  into max_part from user_tab_partitions where  table_name='DEMO'; 
  for x in (select partition_name, high_value, partition_position
          from user_tab_partitions
          where table_name = 'DEMO' and partition_name  like 'SYS%')
 loop
 if x.partition_position  <> max_part THEN

       execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from dual' into hv;
partition '||x.partition_name
                   --||' to DATE_'||hv);
       execute immediate('alter table DEMO rename partition '||x.partition_name
                  ||' to DATE_'||hv);   
end if;
end loop;   
 end;

How can I fix this?

My guess is that the alter table DEMO ... cannot acquire a lock on the table because it is being updated or otherwise "in use". — a_horse_with_no_name
Why are you comparing the max partition of the table T_FDC_TOOLCONTEXT to the partition position of the table DEMO? — Vincent Malgrat
@tbone: Yes, I am trying to rename the interval partitions generated on my table — user1947949
@vincent: because that is the partition, which is active I can not rename that partition. My query will not at all run if I try to rename that as the data is coming into that partition. — user1947949

tbone tbone · Accepted Answer · 2013-02-14T15:01:01

Don't do this. I know its inconvenient that Oracle won't follow a predefined partition naming schema for system created interval partitions (probably an enhancement to come in future), but altering the table every 2 min is not a good alternative by any stretch.

Luckily, you really only need to know the partition name in advance when specifying it in queries. The typical query would be something like:

select * from my_table partition (blah);

In 11g, you can use the "partition for" clause to workaround the system generated names, as follows (example for interval DATE partitions):

SQL> set display on
SQL> set linesize 200
SQL> drop table test_data
Table dropped.
SQL> create table test_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER,
   qty_sold          NUMBER
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('20130101','YYYYMMDD'))
)
Table created.
SQL> insert into test_data values (to_date('20121231', 'YYYYMMDD'), 1, 2, 1)
1 row created.
SQL> commit
Commit complete.
SQL> SELECT table_name, partition_name, high_value
FROM   user_tab_partitions
WHERE table_name = 'TEST_DATA'
ORDER BY table_name, partition_name

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                        
------------------------------ ------------------------------ --------------------------------------------------
TEST_DATA                      PART_01                        TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                              MI:SS', 'NLS_CALENDAR=GREGORIAN')                 

1 row selected.
SQL> insert into test_data values (to_date('20130101', 'YYYYMMDD'), 1, 5, 8)
1 row created.
SQL> insert into test_data values (to_date('20130115', 'YYYYMMDD'), 2, 4, 5)
1 row created.
SQL> insert into test_data values (sysdate, 2, 3, 2)
1 row created.
SQL> commit
Commit complete.
SQL> SELECT table_name, partition_name, high_value
FROM   user_tab_partitions
WHERE table_name = 'TEST_DATA'
ORDER BY table_name, partition_name

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                        
------------------------------ ------------------------------ --------------------------------------------------
TEST_DATA                      PART_01                        TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                              MI:SS', 'NLS_CALENDAR=GREGORIAN')                 

TEST_DATA                      SYS_P67                        TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                              MI:SS', 'NLS_CALENDAR=GREGORIAN')                 

TEST_DATA                      SYS_P68                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                              MI:SS', 'NLS_CALENDAR=GREGORIAN')                 


3 rows selected.
SQL> -- get data for January partition only
SQL> select * from test_data partition for (to_date('20130101', 'YYYYMMDD'))

START_DATE    STORE_ID INVENTORY_ID   QTY_SOLD
----------- ---------- ------------ ----------
01-JAN-2013          1            5          8
15-JAN-2013          2            4          5

2 rows selected.

oracle scheduler to rename interval partitions

1 Answers