Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137

    Unanswered: How to write to the alert.log

    Anyone know how to write to the alert.log. What I'm trying to do is write a trigger for our production databases to log when someone deletes any tables (and take a backup copy of the table) and I'd like it to write the info the alert log as I've already got a perl script to monitor it and to email me any errors.

    Thanks

    Alan

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Delete logging

    Hi,
    Why dont u write the same using triggers.
    I am not quite sure how you do the same, but triggers could be used on the table itself, so that when u r deleting the table, it creates a backup copy of the table using some naming convention, and then logging that information or even sending out an email.
    Regards
    Aruneesh

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Sorry I shoudl have said drop instead of delete. I have already done the system trigger which triggers on any drop table for the production schema but I need to get it to write to the alert log as then I will get an email when anyone tries to delete a table (as has just happened to me : ))

    CREATE OR REPLACE TRIGGER SYSTEM.DROPTABLE1
    BEFORE DROP
    ON X.SCHEMA
    DECLARE
    /************************************************** ****************************
    NAME:
    PURPOSE:
    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 11/07/2003 1. Created this trigger.
    NOTES:
    ************************************************** ****************************/
    v_msg VARCHAR2(1000) :=
    'Contact DBA, Drop table NOT allowed on ' ||
    ora_dict_obj_owner || '.' || ora_dict_obj_name || ' by ' || ORA_LOGIN_USER;
    v_numrows number;
    BEGIN
    IF ora_dict_obj_type = 'TABLE' THEN
    IF ora_dict_obj_name NOT LIKE 'STG$'
    AND ora_dict_obj_name NOT LIKE 'BKP%'
    AND lower(ora_dict_obj_name) NOT LIKE 'backup%'
    AND ora_dict_obj_name NOT LIKE 'TEMP%'
    THEN
    SELECT num_rows into v_numrows from sys.dba_tables where owner=ora_dict_obj_owner and table_name=ora_dict_obj_type;
    IF v_numrows>100000 THEN
    EXECUTE IMMEDIATE 'create table drop_'||ora_dict_obj_name||' as select * from '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
    ELSE
    RAISE_APPLICATION_ERROR (-20905, v_msg);
    END IF;
    END IF;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END ;
    /

  4. #4
    Join Date
    Jul 2003
    Location
    Kansas, USA
    Posts
    12
    Like so..

    sys.dbms_system.ksdwrt(2,'Message');

    Where..

    * 1 - Write to trace file.
    * 2 - Write to alertlog.
    * 3 - Write to both.

    This is not a supported procedure, so use at your own risk.

    -Adam

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Thanks Adam

    That was just what was needed.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •