Oracle Scratchpad

September 8, 2012

DDL triggers

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 5:14 pm GMT Sep 8,2012

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'))

14 Comments »

  1. Maybe the MOS ID; How to change the tablespace storage of the INTERVAL-PARTITIONED table [ID 874374.1] can be useful.
    BR,
    Wissem

    Comment by Wissem — September 8, 2012 @ 6:01 pm GMT Sep 8,2012 | Reply

    • Wissem,
      The note gives some idea about spreading partitions across tablespaces – but it doesn’t cover deal with the problems of “future” partitions arriving at random points in time and having to be relocated to a suitable tablespace asap.

      One detail that isn’t obvious from that note, by the way – if the code adds (say) the 99th partition when partitions 4 to 98 have not yet appeared, would that partition go into the next tablespace in the ring, or would it go into the tablespace that it would have gone into if all the other partitions had been added in order first ? (That’s only a rhetorical question – it’s easy enough to check – it was just a nice example of an Oracle note that doesn’t quite tell you what you might think it’s telling you.)

      Comment by Jonathan Lewis — September 11, 2012 @ 9:14 am GMT Sep 11,2012 | Reply

  2. Funny, I was about to try the same also. Thank you, Jonathan, for publishing this even though you did not (yet) succeed! Will save some others the time to do the very same, probably.
    Kind regards
    Uwe

    Comment by Uwe Hesse — September 9, 2012 @ 6:06 am GMT Sep 9,2012 | Reply

    • Uwe,

      I wonder if you saw the same question on OTN as I did and went through the same thought processes, as in: “all you have to do is …., oh, it doesn’t work.”

      Comment by Jonathan Lewis — September 11, 2012 @ 9:15 am GMT Sep 11,2012 | Reply

      • That happens from time to time also, but in this case, I was planning to investigate because I published postings about interval partitioning and wondered myself whether the tablespace could be specified in other ways apart from the ‘store in’ clause. It looks like you watch the OTN forum much closer resp. more often than I do :-)

        Comment by Uwe Hesse — September 11, 2012 @ 10:08 am GMT Sep 11,2012 | Reply

  3. Well, we initially thought of using Interval Partitioning to automatically create partitions but the system generated partition names restricted us from using them. As we have our own partition naming conventions.

    Comment by Asif Momen — September 9, 2012 @ 8:31 am GMT Sep 9,2012 | Reply

    • I believe you can rename the partitions created for interval partitioning. Would that not work for your case?

      Comment by Jared — September 11, 2012 @ 1:15 am GMT Sep 11,2012 | Reply

      • Jared,

        You can rename them, but only if you find out that they’ve been added in time to rename them (and possibly move them) before the other automatic jobs kick in; and this is where the problem of the (ineffictive) DDL trigger comes in.

        Comment by Jonathan Lewis — September 11, 2012 @ 9:16 am GMT Sep 11,2012 | Reply

  4. Jonathan,

    I also tried this technique some time back. I wanted to respond to the creation of interval partitions by immediately copying stats into them.

    Regards
    Adrian

    Comment by Adrian Billington — September 11, 2012 @ 5:24 pm GMT Sep 11,2012 | Reply

  5. When you trace the insert statement into transactions table with 10046 event, you can see that Oracle is actually running DML statements to create new interval partition: insert into seg$, insert into tabpart$ etc. So internally it is not a DDL. So I tried to add row level trigger (after insert) on sys.tabpart$ table and check :new.obj# and :new.bo# for the object I wanted and raise error if found. However, I got ORA-04089: cannot create triggers on objects owned by SYS. Oh well…

    Comment by guest — September 12, 2012 @ 3:34 pm GMT Sep 12,2012 | Reply

  6. Adding interval partition cannot be ordinary DDL because of „before DDL commit” .

    Comment by Mirek — November 11, 2012 @ 11:37 am GMT Nov 11,2012 | Reply

  7. It certainly would be nice to find a method to take some action when a new partition is added to an interval partitioned table.
    So far all efforts have been stymied.
    In my case I would like to set some statistics on the new partition, so as to avoid any possible chance of the optimizer deciding to use dynamic sampling due to missing statistics.
    On very large tables we have seen parses take up to 40 minutes due to CBO induced dynamic sampling, all the time holding the library cache lock.
    I had hoped that some other idea would occur to me while typing this, but so far nothing…

    Comment by Jared — November 14, 2012 @ 12:21 am GMT Nov 14,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,430 other followers