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)


  • 1
    Oracle 9i is no longer supported. The first thing you should do is to plan an upgrade to a supported version.
    – user1822
    Commented Aug 27, 2013 at 14:02
  • AFAIK there is simply no way to stop logging specific objects. That's just not done. What information are you tracing? There might be other ways of doing that.
    – Mat
    Commented Aug 27, 2013 at 19:25
  • @a, Hi, this trace is some way to evaluate the utilization of our databases to plan the migration to 11i. @Mat, the information we need is the data what able to identify who,where,when, things like machine, application, time/date of logon/logoff, username, real username...
    – ceinmart
    Commented Aug 28, 2013 at 2:24
  • In general SQL databases log all DML so that ACID can be enforced. There's probably not going to be a way around it.
    – JNK
    Commented Sep 27, 2013 at 16:50
  • Hi JNK, I understand. Only for knowledge , at IBM Informix we have RAW tables what allow DML without logging and transaction control; MySQL I know have the sql_log_bin variable to [de]active dynamically the log control ; DB2 have the "not logged" option by table too ; PostgreSQL have UNLOGGED tables... Oracle and SqlServer... as far I research are the only one how don't have the resource :(
    – ceinmart
    Commented Sep 27, 2013 at 23:11

2 Answers 2


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.


As far as I know - it is impossible. Oracle is very strict about ACID. In general to reduce size of redo logs you can use:

  1. NOLOGGING. But as you noted not loged are only specific operations. So you have to plan and program around it.
  2. TEMPORARY TABLE. But in this case each session can only see and modify its own data.
  3. COMMIT WRITE BATCH NOWAIT. This feature was introduced in 10g and it affects redo size indirectly. Redo size is reduced because redo log buffer is not flushed immediately and less space is wasted in redo blocks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.