Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Oracle 9i is no longer supported. The first thing you should do is to plan an upgrade to a supported version. –  a_horse_with_no_name Aug 27 '13 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 Aug 27 '13 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 Aug 28 '13 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 Sep 27 '13 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 Sep 27 '13 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.

share|improve this answer

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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