DDL triggers

DDL triggers:
One of the partitioning features that Oracle introduced relatively recently was the “interval partition”, for example:

create table transactions (
 account_id  number(8) not null,
 transaction_date date  not null,
 transaction_type varchar2(2) not null,
 transaction_id  varchar2(10) not null,
 amount   number(10,2) not null,
 padding   varchar2(100)
)
partition by range (transaction_date)
interval (numtoyminterval(1,'MONTH'))
(
   partition p200801 values less than (to_date('01-FEB-2008','DD-MON-YYYY'))
)
;

When I insert data into this table for a partition that doesn’t yet exist, Oracle will work out which partition it should be and create it automatically before doing the insert. The benefit of this trick, of course, is that the DBAs and developers don’t have to write any code to add partitions in anticipation of time passing and new data appearing.
The last statement is, of course, not true. Although the table partion and any local index partitions will be created automatically there’s not guarantee that they will be created where you want them to go. This may not worry you if you expect to put all the partitions (current and historic) into one huge tablespace, but many sites try to follow some sort of information lifecycle management (ILM) regime that tends to group partitions for a given time period (weekly, monthly, or yearly) into a specific tablespace, and interval partitioning can easliy leave them with the partitions in “the wrong place”. And even if the partitions go into the right place (or the place doesn’t matter) plenty of sites have naming conventions that are supposed to make it easier to read things like space management reports.
The side effects of interval partitioning have resulted in people asking if there was some way of trapping the event when Oracle added a partition – to me there was an obvious answer to the question, but last week I discovered it didn’t work.
In the past I’ve used DDL triggers for various reasons – including tracking down an Oracle bug that appeared when adding partitions to a partitioned IOT (index organized table) – so obvious strategy (to me) was to create a DDL trigger to do something when Oracle added a partition to an interval partitioned table, for example:

create or replace trigger before_ddl
before ddl
on test_user.schema
begin
 if (
      (sys.dbms_standard.DICTIONARY_OBJ_OWNER = 'TEST_USER')
     and (sys.dbms_standard.dictionary_obj_type  = 'TABLE')
     and (sys.dbms_standard.DICTIONARY_OBJ_NAME  = 'TRANSACTIONS')
 ) 
 then
  raise_application_error(-20001,'Before trying to modify paritioned table');
  return;
 end if;
end;
/

create or replace trigger after_ddl
after ddl
on test_user.schema
begin
 if (
      (sys.dbms_standard.DICTIONARY_OBJ_OWNER = 'TEST_USER')
     and (sys.dbms_standard.dictionary_obj_type  = 'TABLE')
     and (sys.dbms_standard.DICTIONARY_OBJ_NAME  = 'TRANSACTIONS')
 ) 
 then
  raise_application_error(-20001,'After trying to modify paritioned table');
  return;
 end if;
end;
/

I’ve created both a BEFORE and AFTER DDL trigger so that when any DDL is applied to table test_user.transactions the triggers will fire. They don’t do anything useful since this was just an initial proof of concept, although if the BEFORE trigger fires I won’t be able to add a new partition to the table. Here’s a little demo of what happens if I try to drop a partition from the transactions table (my test code has added a few more after the initial create table statement):

SQL> select partition_name from user_tab_partitions
  2  where table_name = 'TRANSACTIONS';

PARTITION_NAME
----------------------
P200801
SYS_P8666
SYS_P8667
SYS_P8668

4 rows selected.

SQL> alter table transactions drop partition sys_p8668;
alter table transactions drop partition sys_p8668
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Before trying to modify paritioned table
ORA-06512: at line 8


SQL> select partition_name from user_tab_partitions
  2  where table_name = 'TRANSACTIONS';

PARTITION_NAME
----------------------
P200801
SYS_P8666
SYS_P8667
SYS_P8668

4 rows selected.

SQL>

So the before trigger has fired successfully, raised the error, and blocked the drop. Let’s now insert a row that should create a new partition:

SQL> insert into transactions values(99, '30-Aug-2012','X','X',99,'X');

1 row created.

SQL> select partition_name from user_tab_partitions
  2  where table_name = 'TRANSACTIONS';

PARTITION_NAME
----------------------
P200801
SYS_P8666
SYS_P8667
SYS_P8668
SYS_P8671

5 rows selected.

What a shame – adding a partition is not DDL if Oracle decides to do it internally, it’s only DDL if it’s an end-user statement. Back to the drawing board – but must remember to drop the DDL triggers first.
Footnote: the implicit addition of the partition resulted (at least in 11.2.0.3) in the following text appearing in the alert log – so maybe some code to trace the alert log (or the v$ equivalent) would be helpful:

TABLE TEST_USER.TRANSACTIONS: ADDED INTERVAL PARTITION SYS_P8671 (55) VALUES LESS THAN (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

DIGITAL JUICE