daily

CREATE OR REPLACE NONEDITIONABLE TRIGGER logon_hang
after logon ON DATABASE

DECLARE
ip STRING(30);
USER STRING(30);
module STRING(50);
os_user STRING(30);
host STRING(50);
v_sec number;
v_flag varchar2(10);
v_inst number;

BEGIN
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') INTO USER FROM dual;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO ip FROM dual;
SELECT SYS_CONTEXT('USERENV', 'MODULE') INTO MODULE FROM dual;
SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO os_user FROM dual;
SELECT SYS_CONTEXT('USERENV', 'HOST') INTO host FROM dual;
SELECT SYS_CONTEXT('USERENV', 'INSTANCE') INTO v_inst FROM dual;

select vflag into v_flag from log_hang;
if v_flag='N' and USER in ('EFF_DB_DEV','MIDB01') and v_inst=1 then

dbms_lock.sleep(1800);
/*IF USER in ('YMSQUERY') THEN
select nvl(max(t.last_call_et),0) into v_sec
from gv$session t
where t.USERNAME='YMSQUERY'
and t.status='ACTIVE'
;
if v_sec>120 then
UPDATE log_hang A SET A.VFLAG='Y';

else
dbms_lock.sleep(120);
end if;
END IF;*/
END if;

END;
/

 

 

 

prompt PL/SQL Developer Export Tables for user SYS@TEST_FEFLTDB_1
prompt Created by E904294 on 2024年3月12日
set feedback off
set define off

prompt Creating LOG_HANG...
create table LOG_HANG
(
vflag VARCHAR2(100)
)
;
grant select, insert, update, delete on LOG_HANG to LARKIN;

prompt Disabling triggers for LOG_HANG...
alter table LOG_HANG disable all triggers;
prompt Loading LOG_HANG...
insert into LOG_HANG (vflag)
values ('N');
commit;
prompt 1 records loaded
prompt Enabling triggers for LOG_HANG...
alter table LOG_HANG enable all triggers;

set feedback on
set define on
prompt Done