INSERT INTO T1.AUDIT_TEST
SELECT a.sql_id, a.sql_text, s.program,to_char(a.last_active_time,'dd-mon-yy hh24:mi'),s.username,s.terminal
FROM v$session s, v$sqlarea a, v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
AND s.PREV_SQL_ADDR = a.address
AND s.paddr = p.addr
AND s.program in ('plsqldev.exe','PLSQLDev.exe','TOAD.exe')
AND s.username='T1'
AND a.sql_id NOT IN (Select SQL_ID from T1.AUDIT_TEST
_________________________________________________________________________________
Collect data from above query and check if it exists in Mt then insert in dt if not exists in Mt then insert into Mt and Dt.
Create table Mt
(
sql_id varchar(20) primary key,
sql_text varchar2(50));
Create table dt
(
sqlid varchar(20) reference Mt(sql_id),
PROGRAM varchar2(25),
LAST_ACTIVE_TIME varchar2(20),
USERNAME varchar2(25),
TERMINAL varchar2(25)
);
declare
cnt number;
cursor FrM is
SELECT a.sql_id, a.sql_text, s.program,a.last_active_time,s.username,s.terminal
FROM v$session s, v$sqlarea a, v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
AND s.PREV_SQL_ADDR = a.address
AND s.paddr = p.addr
AND s.program in ('plsqldev.exe','PLSQLDev.exe','TOAD.exe')
AND s.username='T1';
begin
open FrM;
for Rec in FrM;
loop
select count(*) into cnt from dt where dt.sql_id=FrM.sql_id;
if cnt = 0 then
execute immediate 'insert into Mt values(:sql_id,:sql_text)' using FrM.sql_id,FrM.sql_text;
execute immediate 'insert into dt values(:sql_id,:program,:last_active_time,:username,:terminal)' using FrM.sql_id,FrM.program,FrM.last_active_time,FrM.username,FrM.terminal
else
execute immediate 'update dt set dt.program=:Program,dt.last_active_time=:last_active_time,dt.username=:username, dt.terminal=:terminal where dt.sql_id=:sql_id' using Frm.Program,FrM.last_active_time,FrM.username,Frm.terminal,FrM.sql_id;
end if
end loop;
close cursor;
SELECT a.sql_id, a.sql_text, s.program,to_char(a.last_active_time,'dd-mon-yy hh24:mi'),s.username,s.terminal
FROM v$session s, v$sqlarea a, v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
AND s.PREV_SQL_ADDR = a.address
AND s.paddr = p.addr
AND s.program in ('plsqldev.exe','PLSQLDev.exe','TOAD.exe')
AND s.username='T1'
AND a.sql_id NOT IN (Select SQL_ID from T1.AUDIT_TEST
_________________________________________________________________________________
Collect data from above query and check if it exists in Mt then insert in dt if not exists in Mt then insert into Mt and Dt.
Create table Mt
(
sql_id varchar(20) primary key,
sql_text varchar2(50));
Create table dt
(
sqlid varchar(20) reference Mt(sql_id),
PROGRAM varchar2(25),
LAST_ACTIVE_TIME varchar2(20),
USERNAME varchar2(25),
TERMINAL varchar2(25)
);
declare
cnt number;
cursor FrM is
SELECT a.sql_id, a.sql_text, s.program,a.last_active_time,s.username,s.terminal
FROM v$session s, v$sqlarea a, v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
AND s.PREV_SQL_ADDR = a.address
AND s.paddr = p.addr
AND s.program in ('plsqldev.exe','PLSQLDev.exe','TOAD.exe')
AND s.username='T1';
begin
open FrM;
for Rec in FrM;
loop
select count(*) into cnt from dt where dt.sql_id=FrM.sql_id;
if cnt = 0 then
execute immediate 'insert into Mt values(:sql_id,:sql_text)' using FrM.sql_id,FrM.sql_text;
execute immediate 'insert into dt values(:sql_id,:program,:last_active_time,:username,:terminal)' using FrM.sql_id,FrM.program,FrM.last_active_time,FrM.username,FrM.terminal
else
execute immediate 'update dt set dt.program=:Program,dt.last_active_time=:last_active_time,dt.username=:username, dt.terminal=:terminal where dt.sql_id=:sql_id' using Frm.Program,FrM.last_active_time,FrM.username,Frm.terminal,FrM.sql_id;
end if
end loop;
close cursor;
No comments:
Post a Comment