Using Materialized View to Achieve Uniqueness across Tables

Recently I have been working with materialized view (MV) a lot. Some rules I have learned:

- On a fast refresh MV with UNION ALL joins, the MV logs need to be created with ROWID/PRIMARY KEY. And the MV needs to SELECT the ROWID/PRIMARY KEY, and also a UMARKER

- In order to call functions in the MV SELECT clause, the function must be created as DETERMINISTIC

We use an MV with unique index to achieve uniqueness across tables. This appears fine. However we have an issue to perform unit of work commit. The logic is when a product is renamed, its old name is moved to the product_hist table, which has to be an unit of work. However the MV refresh couldn't handle the order of the updates correctly, thus the unique constraint fires to prevent such as an unit of work.

We have to update the product table then commit first; then update the product_hist table, which defeats the application logic. So if anybody has a workaround, please share.

Let me demonstrate here:
-----------------------------------------------
create table product (id number primary key, name varchar2(30));
create table product_hist (hist_id number primary key, id number, name varchar2(30));
create materialized view log on product with rowid, primary key;
create materialized view log on product_hist with rowid, primary key;

create materialized view product_mv build immediate refresh fast on commit as
select rowid rid, id pk_id, name, 'P' umarker from product
union all select rowid rid, hist_id pk_id, name, 'PH' umarker from product_hist;

create unique index product_mv_uq on product_mv(name);

insert into product values(1,'product one');
insert into product_hist values(1,1,'product one old');
commit;
select name from product_mv;

NAME
------------------------------
product one old
product one

update product set name='product one new' where id=1;
insert into product_hist values(2,1,'product one');
commit;

commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (ABC.PRODUCT_MV_UQ) violated

Unix/Linux Process Dump - OC4J Process Hanged during Deployment

Our OC4J server process hanged during application deployment. Using 'kill -3 ' we were able to get the process dump into the OC4J log under $OH/opmn/logs. By inspecting the log we were able to find the root cause.

Using Bind Variables in SQL*Plus

A quick note:

To define the bind variable
SQL> var :b1 number;

To assign values to the bind variable
SQL > exec :b1 := 8888;