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!
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.