Hello world!

Hola mortales inferiores

Hola mortales inferiores

Hola mortales inferiores

Hola mortales inferiores

Hola mortales inferiores

-- Tabla Log
-- Trigger
-- SP
-- Job

CREATE TABLE HESTELA.LOG_02391_ERROR (
DB_USER VARCHAR2(64),
PROF_ACT VARCHAR2(64),
FEC_ACT DATE
);


CREATE OR REPLACE TRIGGER SYS.AFTER_02391_ERROR
AFTER SERVERERROR ON DATABASE
BEGIN
IF IS_SERVERERROR (2391) THEN
INSERT INTO HESTELA.LOG_02391_ERROR (DB_USER) VALUES (USER);
END IF;
END;
/


create or replace procedure SYS.ACTUALIZA_PROFILES
IS
begin
for c_usr in (with prof_no_act as (
select distinct db_user, (select profile from dba_users where username=r.db_user) profl
from HESTELA.LOG_02391_ERROR r
where prof_act is null
),
stmt_ejecutar as (
select db_user db_user1, 'alter profile '||profile||' limit SESSIONS_PER_USER '||increm as EJECUTAR, profile||': '||limit||'-->'||increm as INSERTAR
from (
select a.db_user,p.profile, p.limit, to_number(p.limit)+2 increm
from dba_profiles p join prof_no_act a on p.profile=a.profl
where p.RESOURCE_NAME='SESSIONS_PER_USER'
)
)
select *
from stmt_ejecutar)
loop
begin
execute immediate c_usr.EJECUTAR;
update HESTELA.LOG_02391_ERROR e set PROF_ACT=c_usr.INSERTAR, FEC_ACT=SYSDATE
where e.db_user=c_usr.db_user1 and FEC_ACT is null;
commit;
end;
end loop;
end;
/


BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.AFTER_02391_ERROR_JOB'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=SECONDLY; INTERVAL=3'
,end_date => NULL
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN SYS.ACTUALIZA_PROFILES; END;'
,comments => NULL
);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYS.AFTER_02391_ERROR_JOB');
END;
/

--- Otros objetos para Test
--create profile h_profile limit SESSIONS_PER_USER 5;
alter profile h_profile limit SESSIONS_PER_USER 3;

create user hrnn identified by hrnn789;
grant create session to hrnn;
alter user hrnn profile H_PROFILE;


--- Validacion:
select *
from dba_profiles where profile='H_PROFILE' and RESOURCE_NAME='SESSIONS_PER_USER';

select *
from HESTELA.LOG_02391_ERROR order by fec_act desc;




------- Historial de sesiones establecidas por usuario
CREATE TABLE HESTELA.SESS_X_USER (
DB_USER VARCHAR2(64),
MAX_SESS VARCHAR2(64),
FEC_ACT DATE
);


MERGE INTO HESTELA.SESS_X_USER a
USING (
SELECT username, count(9) as connections
FROM gv$session
WHERE USERNAME IS NOT NULL
GROUP BY username
) b ON a.DB_USER=b.USERNAME
WHEN MATCHED THEN
UPDATE SET a.MAX_SESS = b.connections, FEC_ACT=SYSDATE
where a.MAX_SESS < b.connections
WHEN NOT MATCHED THEN
INSERT (DB_USER, MAX_SESS)
VALUES (b.USERNAME, b.connections)
;


BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'CAPT_NUM_SESS_X_USER'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=SECONDLY; INTERVAL=30'
,end_date => NULL
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN MERGE INTO HESTELA.SESS_X_USER a
USING (
SELECT username, count(9) as connections
FROM gv$session
WHERE USERNAME IS NOT NULL
GROUP BY username
) b ON (a.DB_USER=b.USERNAME)
WHEN MATCHED THEN
UPDATE SET a.MAX_SESS = b.connections, FEC_ACT=SYSDATE
where a.MAX_SESS < b.connections
WHEN NOT MATCHED THEN
INSERT (DB_USER, MAX_SESS)
VALUES (b.USERNAME, b.connections)
;
COMMIT;
END;'
,comments => NULL
);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'CAPT_NUM_SESS_X_USER');
END;
/


SELECT DB_USER, TO_NUMBER(MAX_SESS) MAX_SESS1,FEC_ACT
FROM HESTELA.SESS_X_USER
ORDER BY 2 DESC;


select *
from dba_scheduler_job_run_details
where job_name like '%X_USER' order by log_date desc;





------------- CAPTURAR SESIONES POR MINUTO
CREATE TABLE HESTELA.SESS_X_MINUTO (
USERNAME VARCHAR2(128),
OSUSER VARCHAR2(128),
MACHINE VARCHAR2(128),
TERMINAL VARCHAR2(128),
PROGRAM VARCHAR2(128),
MODULE VARCHAR2(128),
SERVICE_NAME VARCHAR2(128)
) PCTFREE 0;


MERGE INTO HESTELA.SESS_X_MINUTO a
USING (select * from (
SELECT DISTINCT USERNAME usr,OSUSER osusr,MACHINE machn,TERMINAL trml,PROGRAM prgm,MODULE mdle, SERVICE_NAME srv
FROM GV$SESSION
WHERE TYPE='USER' AND USERNAME IS NOT NULL )
) b ON (USERNAME=usr and OSUSER=osusr and MACHINE=machn and TERMINAL=trml and PROGRAM=prgm and MODULE=mdle and SERVICE_NAME=srv)
WHEN NOT MATCHED THEN
INSERT (USERNAME,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,SERVICE_NAME)
VALUES (usr,osusr,machn,trml,prgm,mdle,srv)
;


BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'CAPT_SESS_X_MIN'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=MINUTELY; INTERVAL=1'
,end_date => NULL
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN MERGE INTO HESTELA.SESS_X_MINUTO a
USING (select * from (
SELECT DISTINCT USERNAME usr,OSUSER osusr,MACHINE machn,TERMINAL trml,PROGRAM prgm,MODULE mdle, SERVICE_NAME srv
FROM GV$SESSION
WHERE TYPE=''USER'' AND USERNAME IS NOT NULL )
) b ON (USERNAME=usr and OSUSER=osusr and MACHINE=machn and TERMINAL=trml and PROGRAM=prgm and MODULE=mdle and SERVICE_NAME=srv)
WHEN NOT MATCHED THEN
INSERT (USERNAME,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,SERVICE_NAME)
VALUES (usr,osusr,machn,trml,prgm,mdle,srv)
;
COMMIT;
END;'
,comments => NULL
);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'CAPT_SESS_X_MIN');
END;
/


select * from HESTELA.SESS_X_MINUTO;