create or replace TRIGGER MyTrigger
FOR INSERT OR UPDATE ON MyTable 
COMPOUND TRIGGER

declare cSql varchar(200);
  
    before statement
    is
    begin

        cSql := 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
                  ID       NUMBER,
                  description  VARCHAR2(20)
                )
                ON COMMIT DROP DEFINITION';

        EXECUTE IMMEDIATE cSql;    
   end before statement;
...
END

-- Табличка
create table test (f1 NUMBER NOT NULL ENABLE);

-- Табличка лога
create table test1 (f1 NUMBER NOT NULL ENABLE, f2 NVARCHAR2);

-- Заполняем
insert into test (f1) values (1);
insert into test (f1) values (1);
insert into test (f1) values (2);
insert into test (f1) values (2);

-- Модифицируем
update test set f1 = f1

CREATE TABLE TEST.MY_TEST
(
  F1  NUMBER(38)  NOT NULL
);

CREATE TABLE TEST.MY_TEST1
(
  F1  NUMBER(38) NOT NULL,
  F2  VARCHAR2(100 BYTE)
);

CREATE OR REPLACE TRIGGER TEST.TRG_MY_TEST_UPD
AFTER UPDATE ON TEST.MY_TEST
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
    l_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO l_cnt FROM TEST.MY_TEST1 T1 WHERE T1.F1 = :Old.F1;
    
    IF l_cnt = 0 THEN
        INSERT INTO TEST.MY_TEST1 (F1) VALUES (:Old.F1);
    END IF;
END TRG_MY_TEST_UPD;
/

insert into TEST.MY_TEST (f1) values (1);  
insert into TEST.MY_TEST (f1) values (1);  
insert into TEST.MY_TEST (f1) values (2);  
insert into TEST.MY_TEST (f1) values (2);
  
update TEST.MY_TEST set f1 = f1;

GRANT EXECUTE ON OtherSch.Func TO MySch;

select OtherSch.Func() from MySch.MyTAble

-- Юзеры
CREATE USER "MYUSER" IDENTIFIED BY 123  ;
CREATE USER "OTHERUSER" IDENTIFIED BY 123 ;  

-- Права на вход
GRANT "CONNECT" TO "MYUSER" ;
-- не обязательно
GRANT "CONNECT" TO "OTHERUSER" ;

-- Ф-ия
create or replace function OTHERUSER.testfunc(f1 varchar2) 
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN f1;
  END;

-- Роль
create role MYROLE;
-- Права роли
grant execute on OTHERUSER.testfunc to MYROLE;
-- Добавляем в роль юзера
grant MYROLE to MYUSER;
ALTER USER MYUSER DEFAULT ROLE MYROLE, CONNECT;

-- Выполняем от имени MYUSER
select OTHERUSER.testfunc(1) as aa from dual;
-- Ошибка
-- ORA-00904: "OTHERUSER"."TESTFUNC": недопустимый идентификатор
-- 00904. 00000 -  "%s: invalid identifier"

-- Убираем у роли EXECUTE
revoke execute on OTHERUSER.testfunc from MYROLE;

-- Разрешаем для MYUSER выполнять ф-ию в другой схеме
grant execute on OTHERUSER.testfunc to MYUSER;

-- Выполняем от имени MYUSER
select OTHERUSER.testfunc(1) as aa from dual;
-- Получаем '1'
-- revoke execute on OTHERUSER.testfunc from MYUSER;