Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Unable to get the sql command in Audit table
On Oct 17, 6:16 pm, DA Morgan <damor..._at_psoug.org> wrote:
> nitesh wrote:
> > Hi,
>
> > I need to create a trigger, which should record any ddl changes in a
> > schema. The output should be sent to an audit table in which it should
> > show the sqlstatement, user and program. So i created an audit table
> > and a trigger. In the audit table it is showing the user and program
> > but not the sqlstatement. Here i am giving my approach..
>
> > CREATE TABLE ddl_log (
>
> > operation VARCHAR2(30),
>
> > obj_owner VARCHAR2(30),
>
> > object_name VARCHAR2(30),
>
> > sql_text VARCHAR2(64),
>
> > attempt_by VARCHAR2(30),
>
> > attempt_dt DATE);
>
> > CREATE OR REPLACE TRIGGER ddl_trigger
>
> > BEFORE CREATE OR ALTER OR DROP
>
> > ON SCHEMA
>
> > DECLARE
>
> > oper ddl_log.operation%TYPE;
>
> > sql_text ora_name_list_t;
>
> > i PLS_INTEGER;
>
> > BEGIN
>
> > SELECT ora_sysevent
>
> > INTO oper
>
> > FROM dual;
>
> > i := sql_txt(sql_text);
>
> > IF oper IN ('CREATE', 'DROP') THEN
>
> > INSERT INTO ddl_log
>
> > SELECT ora_sysevent, ora_dict_obj_owner,
>
> > ora_dict_obj_name,NULL, USER, SYSDATE
>
> > FROM dual;
>
> > ELSIF oper = 'ALTER' THEN
>
> > INSERT INTO ddl_log
>
> > SELECT ora_sysevent, ora_dict_obj_owner,
>
> > ora_dict_obj_name, sql_text, USER, SYSDATE
>
> > FROM gv$sqltext
>
> > WHERE UPPER(sql_text) LIKE 'ALTER%'
>
> > AND UPPER(sql_text) LIKE '%NEW_TABLE%';
>
> > END IF;
>
> > END ddl_trigger;
>
> > /
>
> > please suggest.
>
> > Thanks in advance.
>
> A schema level trigger will not do what it appears you are trying to do.
> Schema level doesn't trap anyone touching a named schema ... rather it
> affects anyone logged in as that schema owner only.
>
> Look at the demos here:http://www.psoug.org/reference/ddl_trigger.html
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
Rainbow Tables for CAIN:
parameters are given below:
oracle_oracle#1-8_0_2400x40000000_system#000.rt oracle_oracle#1-8_0_2400x40000000_system#001.rt oracle_oracle#1-8_0_2400x40000000_system#002.rt ...........................
email for details, demo, and downloading url's!
(Only system account) oracle hashes challenges are welcome! please make the payment FIRST!
happy crackin' Received on Thu Oct 18 2007 - 07:07:53 CDT
![]() |
![]() |