Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    13

    Unanswered: Can you tell when a table has last been modifed / accessed ?

    Just curious..

    Thanks in advance to all who reply.

  2. #2
    Join Date
    Sep 2004
    Posts
    17
    Last accessed :When select is performed on a table, then that time and date will become the last accessed for the table.

    Last modified
    : When commands like insert/update/delete are performed on a table, then that time and date will become the last modified for the table.

  3. #3
    Join Date
    Nov 2002
    Posts
    13
    Thanks ! Is that info stored in a table / view ? dba_tables ? I am a novice so please forgive me it these questions seem trivial !

  4. #4
    Join Date
    Sep 2004
    Posts
    17
    The Detail is available in System table : sys.dba_audit_object.

    One option is as follows:

    (1) Turn the auditing on: AUDIT_TRAIL = true in init.ora

    (2) Restart the instance if its running.

    (3) Audit the table:

    AUDIT INSERT,SELECT,DELETE,UPDATE on TableName
    by ACCESS WHENEVER SUCCESSFUL
    (4) Get the desired information using :

    SELECT OBJ_NAME,ACTION_NAME ,to_char(timestamp,'dd/mm/yyyy , HH:MMS')
    from sys.dba_audit_object.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No. You may want to look at Oracle's Auditing facilities

  6. #6
    Join Date
    Nov 2002
    Posts
    13
    Thanks for all your help ! Will use of the audit affect performance ?

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    Quote Originally Posted by BrutusBuckeye
    Thanks for all your help ! Will use of the audit affect performance ?
    Yes, for every audited action performed, the db will have to write to the sys.ADU$ table. However, under normal circumstances, you should not be able to notice it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •