finding the partition for a newly inserted row, from inside a trigger on the table

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 08 Jan 2004 18:29:25 -0800
Message-ID: <F001.005DC2F9.20040108182925@fatcity.com>

This is probably old hat for some of you and not very useful to most of you, but maybe there's that one person who is struggling with this question who will be happy to read the post.

You have a partitioned table and you want to find out in which partition a newly inserted or updated row will be placed, because for example you want to prevent changes in that partition for certain users or whatever. Here's one way to do it. If there's a better way I'd be glad to hear about it.
(I realize that for list or range partitions one could compare the relevant columns to the partitioning values but you would have to modify the trigger every time you add/remove partitions.)

create table t (n number, d date)
  partition by hash (n)
   (partition tp1, partition tp2) ;
create trigger t_afi
after insert on t
for each row
declare

   rid_type number ;
   objid number ;
   rfno number ;
   bno number ;
   rno number ;
   objname sys.obj$.subname%type ;
begin

   dbms_rowid.rowid_info (rowid_in => :new.rowid,
                          rowid_type => rid_type,
                          object_number => objid,
                          relative_fno => rfno,
                          block_number => bno,
                          row_number => rno) ;

   select subname
    into objname
    from sys.obj$
    where dataobj# = objid ;
   dbms_output.put_line ('Row was placed in partition ' || objname) ; end ;
/

Proof of concept:

SQL> set serveroutput on

SQL> insert into t (n, d) values (1, sysdate) ; Row was placed in partition TP2
1 ligne créée.

SQL> insert into t (n, d) values (2, sysdate) ; Row was placed in partition TP1
1 ligne créée.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Jan 08 2004 - 20:29:25 CST