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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
