Bloqueio de um user

Viva, cá está mais uma maneira de bloquear user's de acederem a ambientes de PRD e acederem a réplicas/clones (DataGards). Geralmente estes diferenciados tem de existir em todos ambientes. Então uma forma é criar uma um trigger para esse user ...

CREATE OR REPLACE TRIGGER SYS.NOME_DO_TRIGGER
after LOGON on USER_A_BLOQUEAR.schema
DECLARE
l_sid NUMBER(09);
l_serial NUMBER(09);
p_error VARCHAR2(20);
w_ora_login VARCHAR2(30);
w_type VARCHAR2(10);
w_os_login VARCHAR2(30);
w_count NUMBER(02);
l_audsid NUMBER(09);
l_machine VARCHAR2(150);
l_terminal VARCHAR2(100);
w_program VARCHAR2(100);
l_module VARCHAR2(100);
l_logon_time date;
l_username VARCHAR2(100);
v_host VARCHAR2(100);
BEGIN
SELECT UPPER(NVL(username,schemaname)), UPPER(TYPE), UPPER(osuser), program, machine, module, logon_time, username
INTO w_ora_login, w_type, w_os_login, w_program ,l_machine, l_module, l_logon_time ,l_username FROM V$SESSION
WHERE sid IN
(SELECT DISTINCT(sid) FROM v$mystat);
SELECT host_name into v_host FROM V$instance;
IF lower(v_host) !='nome_host_onde_pode_aceder' THEN
insert into owner.nome_tabela_gestao_acessos (osuser,userbd,application,module,from_m, login_date)
values (w_os_login, w_ora_login, w_program, l_module , l_machine , l_logon_time );
commit;
RAISE_APPLICATION_ERROR (-20002, 'User apenas da REPLICA_XPTO - : Acesso nao autorizado do user ' || w_ora_login || '/' || w_os_login || ' com o programa: '|| w_program);
END IF;
END;
/

LOCKS - Blocking

SELECT SUBSTR (s1.username, 1, 12) "WAITING USER",
SUBSTR (s1.osuser, 1, 8) "OS User",
SUBSTR (TO_CHAR (w.session_id), 1, 5) "Sid", s1.serial# "SERIAL1",
p1.spid "PID", SUBSTR (s2.username, 1, 12) "HOLDING User",
SUBSTR (s2.osuser, 1, 8) "OS User",
SUBSTR (TO_CHAR (h.session_id), 1, 5) "Sid", s2.serial# "SERIAL2",
p2.spid "PID",
'alter system kill session '''
SUBSTR (TO_CHAR (h.session_id), 1, 5)
','
s2.serial#
''';' to_kill
FROM SYS.v_$process p1,
SYS.v_$process p2,
SYS.v_$session s1,
SYS.v_$session s2,
dba_locks w,
dba_locks h
WHERE h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2(+) = h.lock_id2
AND w.session_id = s1.SID(+)
AND h.session_id = s2.SID(+)
AND s1.paddr = p1.addr(+)
AND s2.paddr = p2.addr(+);

CPU - Utilization

SELECT cpu.val "Host CPU Utilization (%)" FROM (SELECT metric_name, ROUND (VALUE) AS val FROM SYS.gv_$sysmetric WHERE intsize_csec = (SELECT MAX (intsize_csec)
FROM SYS.gv_$sysmetric WHERE inst_id = 1) AND metric_name = 'Host CPU Utilization (%)'
AND inst_id = 1) cpu;