7 Replies Latest reply: Jun 7, 2007 8:29 AM by Madrid RSS

    TRUNCATE SYS.AUD$

    babylon
      Hi
      I've created table system.aud_$$ (copy of sys.aud$). Than i created scheduled job, whitch once per day iserts data from sys.aud$ in to system.aud_$$ table.
      Than job execute truncate table sys.aud$.

      Code:
      ...
      begin
      insert into system.aud_$$
      (
      select system.seq.nextval, SESSIONID, ENTRYID, STATEMENT,
      TIMESTAMP#, USERID, USERHOST,
      TERMINAL, ACTION#, RETURNCODE,
      OBJ$CREATOR, OBJ$NAME, AUTH$PRIVILEGES,
      AUTH$GRANTEE, NEW$OWNER, NEW$NAME,
      SES$ACTIONS, SES$TID, LOGOFF$LREAD,
      LOGOFF$PREAD, LOGOFF$LWRITE, LOGOFF$DEAD,
      LOGOFF$TIME, COMMENT$TEXT, CLIENTID,
      SPARE1, SPARE2, OBJ$LABEL,
      SES$LABEL, PRIV$USED, SESSIONCPU,
      NTIMESTAMP#, PROXY$SID, USER$GUID,
      INSTANCE#, PROCESS#, XID,
      AUDITID, SCN, DBID,
      SQLBIND, SQLTEXT, sysdate
      from sys.aud$);
      begin
      execute immediate 'truncate table sys.aud$';
      end;
      commit;
      end;
      ...

      Do i have all records on system.aid_$$, or is it possible to miss some records when job is inserting record to table and after that execute truncate sys.aud$?

      If exist better solution for automatic copy sys.aud$ to differnt schema and truncate sys.aud$ pleas let me know.
      Thanks
      Stojc
        • 1. Re: TRUNCATE SYS.AUD$
          ViragSharma
          I've created table system.aud_$$ (copy of sys.aud$).
          Than i created scheduled job, whitch once per day
          iserts data from sys.aud$ in to system.aud_$$ table.
          Why you you doing above job , what is a purpose
          Do i have all records on system.aid_$$, or is it
          possible to miss some records when job is inserting
          record to table and after that execute truncate
          sys.aud$?
          There is possibility that you may miss some records
          • 2. Re: TRUNCATE SYS.AUD$
            babylon
            I'd like to keep audit record from sys.aud$ for long time, but not on dictionary table, and copy record automaticity and to set free space on system tablespace.
            I'm dba novice, and this is now best what i can do. So, if you have beter solution, please share it.
            Thanks
            • 3. Re: TRUNCATE SYS.AUD$
              ViragSharma
              There are lots of doc/discussion available on metalink, check Note:72460.1 and Note:1019377.6
              • 4. Re: TRUNCATE SYS.AUD$
                babylon
                Hello again
                Yes i saw that doc, but i don't wont to move aud$ table out of system tablespace. I want to move only rows to different tablespace in different storage. And my boss also dont't want to use delete table sys.aud$. How can i prevent to miss some records between insert and truncate...
                • 5. Re: TRUNCATE SYS.AUD$
                  ajallen
                  You cannot. Truncate will blow away everything in the table. To make sure you do not loose rows from AUD$, you have to delete only those rows you have archived. To use truncate and loose as few rows as possible, you will have to lock all login accounts (or take database to restricted mode) and terminate all existing sessions, archive and truncate aud$, and then open up to logins again.
                  • 6. Re: TRUNCATE SYS.AUD$
                    babylon
                    I agree with you ajallen, thanks for your co-operation and help.
                    • 7. Re: TRUNCATE SYS.AUD$
                      Madrid
                      This approach is not convenient if you want to perform auditing tasks, as the current information cannot be retreived from your created table. DBA_AUDIT_TRAIL as well as the other related AUDIT views are based on the SYS.AUD$. It is useless to move data so immediately. Your approach is not far from another good practice, though.

                      I suggest you to redefine your audit record retention policy. Keep records at the SYS.AUD$ for a predefined amount of time, let's say for two months for example, then schedule a procedure to be launched daily, this procedure will insert/delete to your table and delete from the sys.aud$ table.

                      This way you ensure all SYS.AUD$ records will be there by the audit views when required according to your audit policies, and you keep your audit log table trimmed.


                      ~ Madrid.