Re: Unable to get the sql command in Audit table

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org