We using Oracle 9i .
I'm still learn about admin Oracle (I'm DBA but other vendor).
We implement manually at our databases a trigger over LOGON and LOGOFF to save some information about our users sessions.
We execute an insert at LOGON and an update at LOGOFF over our table.
This table was created as NOLOGGING.
Our problem is the amount of archive generated by day, growth a lot.
After check with log miner I detect is the updates over this table the reason.
I research little and found the information the NOLOGGING work only for very specific situations and all UPDATEs still logging.
I don't need keep safe this table or backup it. We extract the information all days… if I loose some days I can live with it. But I can't keep this overhead .
Is there no way to force a table to not logging for all DMLs in Oracle?
Any alternative solution? (where I keep the triggers and my own table)
Regards
Cesar
Best Answer
If you are so concerned about the redo logging volume, you can consider writing the logon/logoff audit records to a file instead, using UTL_FILE for example.
As an aside, I'd be hard pressed to believe that updates of the audit table generate more logs than other activities in the database. This would mean that your users mostly log on to the system and then immediately log off, without doing much else.