oracle 使用触发器监控 package 修改历史记录

oracle 使用触发器监控 package 修改历史记录


一.先创建监控表和序列:

-- Create table
create table MES_AUDIT_DDL
(
audit_id NUMBER,
session_id NUMBER,
os_user VARCHAR2(200),
ip_address VARCHAR2(200),
terminal VARCHAR2(200),
host VARCHAR2(200),
machine VARCHAR2(200),
program VARCHAR2(200),
user_name VARCHAR2(30),
ddl_type VARCHAR2(30),
object_type VARCHAR2(18),
owner VARCHAR2(30),
object_name VARCHAR2(128),
sql_text VARCHAR2(4000),
ddl_time DATE,
sql_full_text CLOB
);

-- Create sequence
create sequence SEQ_MES_AUDIT_DDL
minvalue 1
maxvalue 99999999999999999999
start with 1
increment by 1
cache 5;

 

 

二.创建触发器:

CREATE OR REPLACE TRIGGER trg_mes_ddl
AFTER ddl ON mes.schema
DECLARE
sql_text ora_name_list_t;
i INTEGER;
state_sql VARCHAR2(4000);

sql_full_text clob;
v_sid VARCHAR2(100);
BEGIN

FOR i IN 1 .. ora_sql_txt(sql_text) LOOP
BEGIN

sql_full_text := sql_full_text || sql_text(i);
END;
END LOOP;

FOR i IN 1 .. ora_sql_txt(sql_text) LOOP
BEGIN
EXIT WHEN(lengthb(state_sql) + lengthb(sql_text(i))) >= 4000;
state_sql := state_sql || sql_text(i);
END;
END LOOP;
state_sql := substrb(state_sql, 1, 4000);
INSERT INTO mes_audit_ddl
(audit_id,
ddl_time,
session_id,
os_user,
ip_address,
terminal,
host,
user_name,
ddl_type,
object_type,
owner,
object_name,
sql_text,
program,
machine,
sql_full_text)
SELECT seq_mes_audit_ddl.nextval
,SYSDATE
,sys_context('USERENV', 'SESSIONID')
,sys_context('USERENV', 'OS_USER')
,sys_context('USERENV', 'IP_ADDRESS')
,sys_context('USERENV', 'TERMINAL')
,sys_context('USERENV', 'HOST')
,ora_login_user
,ora_sysevent
,ora_dict_obj_type
,ora_dict_obj_owner
,ora_dict_obj_name
,state_sql
,v.program
,v.machine
,sql_full_text
FROM v$session v
WHERE v.audsid = sys_context('USERENV', 'SESSIONID');

SELECT v.sid
INTO v_sid
FROM v$session v
WHERE v.audsid = sys_context('USERENV', 'SESSIONID');
-- dbms_output.put_line(v_sid);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
v_sid := SQLERRM;
INSERT INTO tbltest
(column01, column02)
VALUES
('BUG', substr(v_sid, 1, 60));
END;
 

三.大功告成!

博文最后更新时间:


评论

  • Merziuz

    F87qGt http://pills2sale.com/ viagra online

  • Clifford

    In a meeting <a href="http://adena.ro/v2/norethindrone-acetate-and-ethinyl-estradiol-cost-u34t">ethinyl estradiol-norethindrone acetate-iron</a> During a question and answer session with investors, he praised Netflix Chief Executive Officer Reed Hastings and launched into an analysis of Netflix's business model, saying it was big enough to buy exclusive national content at good prices, something the cable industry has struggled with.

  • Jacob

    I read a lot <a href="http://www.wepsolutions.co.in/tamsulosin-harnal-side-effects-9uyl">para que es tamsulosina clorhidrato</a> "I just explained to him that I loved him," Tuff told ABC News in an exclusive interview Tuesday night. "I didn't know much about him. I didn't know his name but I did love him and it was scary because I knew at that moment he was ready to take my life along with his, and if I didn't say the right thing, then we all would be dead."

  • Stephen

    How do you know each other? <a href="https://bomexperts.com/rosuvastatin-calcium-tablets-5mg-qehf">rosuvastatin sandoz 10 mg</a> Between rowdy neighborhood kids pulling Halloween pranks and having to sit through awkward Thanksgiving dinner with the in-laws, who can blame you for wanting to throw back a few brewskies this fall?  With so many seasonal craft beers to choose from, not sure where to begin?  Here are six to get you started.

  • Russell

    I'm interested in <a href="https://unef.edu.br/site/xatral-sr-10-rgt0">alfuzosin (uroxatral) side effects</a> The company was ranked as the eighth most valued brand this year, according to a report released this week by brand consultancy Interbrand.( The company saw its brand value go up 20 percent to $39.6 billion, as the brand continues to strengthen its position globally, spending more than $4 billion in marketing last year, and launching innovative products such as the Galaxy S4 and Galaxy Note II, according to Interbrand. Rival Apple was ranked number one in brand value, outstripping Coca-Cola for the first time.

  • Vernon

    What university do you go to? <a href="http://bouw.scouts61.be/depakote-price-uk-25ge">depakote dosage forms</a> McDonnell is finishing his one term as governor. Virginia law does not allow an incumbent governor to run for re-election. In the past McDonnell had frequently been mentioned as a potential 2012 vice presidential pick for Mitt Romney and is a potential 2016 Republican presidential candidate.

发表评论

博客统计

访问量:5096569

博文总数:750 评论总数:909178

原创126 翻译20 转载604