Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: Oracle logging system

    Hi there all,

    I would like to create a logging system on my Oracle instances. The one system is Oracle 8i and the other is Oracle 7.3. What kind of a right approach to the problem I could have?
    I know how to log the ?login? and ?logout? processes, but how can I log the user activities. With my colleagues we elected the following:

    Select * from sys.v_$sqlarea
    And INSERT INTO log_table ??
    All from column sys.v_$sqlarea .SQL_TEXT like (INSERT, DELETE or UPDATE).
    But there is a problem. The trigger which will execute this INSERT INTO will log and itself etc., etc. because this trigger have to assign to the after insert event on sys.v_$sqlarea . This will be a very good loop if it possibly assign a trigger to view!

    Any suggestions?

    Best regards
    Detelin Nedev

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    Have you considered Oracle's own auditing? Though I'm not sure how much that can do for you in 7.3. What you want sounds like overkill to me, but anyway...

    Creating triggers on tables owned by SYS sounds like a very bad idea. I wouldn't be surprised if it caused all sorts of problems (e.g. performance), and invalidates any support contract you may have. But regarding the specific problem of the infinite loop, you could avoid that by checking whether the statement to be audited contains "INSERT INTO sys.v_$sqlarea", I suppose.

Posting Permissions

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