SQL> insert into test23 values(1);
1 row inserted

SQL> commit;
Commit complete

SQL> insert into test23 values(1);
insert into test23 values(1)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TR_TEST23", line 36
ORA-04088: error during execution of trigger 'TR_TEST23'

SQL> insert into test23 values(2);
1 row inserted

SQL> insert into test23 values(2);
insert into test23 values(2)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TR_TEST23", line 36
ORA-04088: error during execution of trigger 'TR_TEST23'

create table TEST23(  uq INTEGER ); -- это таблица с триггером, реализующим constraint
create table TEST_U(  id  INTEGER not null,  val INTEGER default 0 not null ); -- вспомогательная

-- вспомогательная функция, которая вставляет во вспомогательную таблицу ID и коммитит ее, 
-- чтобы показать всем сессиям. Функция должна быть атомарная (атомическая?), DBMS лок не показан
create or replace procedure CommitForAll(uq in integer) is
  PRAGMA AUTONOMOUS_TRANSACTION;
  n integer;
begin
  select id into n from test_u where id = uq;
exception
  when NO_DATA_FOUND then 
    insert into test_u values (uq,0);
    commit write nowait batch;
end CommitForAll;

-- собственно триггер. эмуляция поведения при вставлении дупликата реализована через update.
-- поскольку update после освобождения строчного лока не вылетает, а срабатывает,
-- приходится повторно проверять главную таблицу на предмет закоммиченных UQ
create or replace trigger tr_test23
  before insert on test23  
  for each row
begin
  if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL

  -- remember the posted id for other sessions
  CommitForAll( :new.uq );

  -- first, check among session-visible data (commited +posted in this session)
  for t in (select * from test23 where uq = :new.uq)
  loop
    raise DUP_VAL_ON_INDEX;
  end loop;

  -- try causing row-level lock in helper table of uncommitted inserts
  update test_u u
     set val = u.val+1
   where u.id = :new.uq;

  -- if updated, lock worked; see if uq now among committed
  if SQL%ROWCOUNT > 0 then
    for r in (select * from test23 where uq = :new.uq)
    loop
      raise DUP_VAL_ON_INDEX;
    end loop;
  end if;
end tr_test23;