Month: August 2018

A user reported the below error:

Select * from users;
ORA-28374: typed master key not found in wallet

gv$encryption_wallet indicated that the wallet was open and backed up on both nodes. It turned out that we had two copies of the wallet files in different locations. In order to resolve the issue, we
– stopped the RAC
– ensured that the wallet files matched and were in the correct location on both nodes
– updated the sqlnet.ora to indicate the wallet location on both nodes
And restarted the RAC. After this, the user was able to access the USERS table.

A colleague of mine ran into the annoying mutating trigger issue and solved it as follows. The requirement was to update TABLE_A with data from TABLE_B. There is no relationship between the two tables other than they get inserted during the same unit of work.

Original trigger:

CREATE OR REPLACE TRIGGER TABLE_A_TRIGGER_01
before INSERT ON TABLE_A 
FOR EACH ROW 
begin 
update TABLE_A  set customer_id = 
(select customer_id from TABLE_B
where master_key = :new.master_key);
END; 
/

This resulted in error:

insert into TABLE_A select * from TABLE_A_TEST_DATA
            *
ERROR at line 1:
ORA-04091: table TABLE_A is mutating, trigger/function may not see it
ORA-06512: at "TABLE_A_TRIGGER_01", line 2
ORA-04088: error during execution of trigger 'TABLE_A_TRIGGER_01'

The workaround was to code it as follows:

CREATE OR REPLACE TRIGGER TABLE_A_TRIGGER_01
BEFORE INSERT ON TABLE_A
FOR EACH ROW
DECLARE
 V_CUSTOMER_ID number(9);
 V_MASTER_KEY varchar2(16);
begin
V_MASTER_KEY := :new.master_key;
select h.customer_id INTO V_CUSTOMER_ID from TABLE_B h
where h.master_key = V_MASTER_KEY;
:new.customer_id := V_CUSTOMER_ID;
END;
/