DDL triggers

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

rem
rem     Script:         ddl_trigger.sql
rem     Dated:          Sept 2012
rem     Author:         Jonathan Lewis
rem     Purpose:
rem
rem     Last tested
rem             19.3.0.0
rem             12.1.0.2
rem             11.2.0.3
rem             11.1.0.7
rem

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 partition and any local index partitions will be created automatically there’s no 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 groups partitions for a given time period (weekly, monthly, or yearly) into a specific tablespace, and interval partitioning can easliy leave them with 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 the obvious (to me) strategy 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 partitions 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. (Interestingly this fact is reflected in the behaviour of dbms_metadata.get_ddl() which will not, by default, generate the DDL for the generated interval partitions – but see MOS note 1491820.1).  Back to the drawing board – but I 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'))

Update (Some time in 2020)

Things haven’t changed in any version up to 19.3 – but at least it’s now very easy to read the alert log from SQL (if you’re connected as sys) by referencing x$diag_alert_ext, e.g:

select  indx, trim(message_text) message
from    x$diag_alert_ext
where   trim(message_text) like '%ADDED INTERVAL%'
and     originating_timestamp >= trunc(sysdate-1)
/

Warning (June 2021)

I’ve seen the suggestion to set parameter “enable_ddl_logging” to true to help track down problems due to unexpected DDL. Don’t do this unless you have licensed the (extra cost) Lifecycle Management pack.