1 2 Previous Next 20 Replies Latest reply: Mar 8, 2013 8:27 AM by Nicolas.Gasparotto RSS

    Find when was the table last updated ?

    626981
      Is there any way that we can find in oracle 10g R2 when a particular table was last updated or modified or so ?
      Also is it possible to check which user has last updated or accessed the table?

      I just dont have any clue about this...
        • 1. Re: Find when was the table last updated ?
          ABD - DBA
          It depends on whether you have auditing enabled.

          Is there any data in the dba_audit_session or dba_audit_trail views?
          • 2. Re: Find when was the table last updated ?
            stevencallan
            You need a better distinction between "last updated or modified." From your understanding, what does "modified" mean to you? Data was modified, or something about the structure of the table was modified? One of those is yes, the other is no in terms of knowing right away - without adding any additional steps or changes to code.
            • 3. Re: Find when was the table last updated ?
              626981
              i queried both dba_audit_session and dba_audit_trial..
              both are empty
              • 4. Re: Find when was the table last updated ?
                626981
                Actually let me explain the scenario....

                I have around 5000 tables in the database. Now need to discard tables which are not being used from long...
                So i need to know when was the last time when a particular table was accessed by any user....
                • 5. Re: Find when was the table last updated ?
                  stevencallan
                  As already mentioned then, auditing.
                  • 6. Re: Find when was the table last updated ?
                    626981
                    But as both the audit tables are empty .. what should i do..
                    I mean unable auditing or so??? and how do i do this thing...
                    • 7. Re: Find when was the table last updated ?
                      stevencallan
                      Pretty easy to do. After auditing is enabled on the objects (more than one way), it becomes a matter of checking an audit table.
                      audit insert, update, delete, select on xxx.TABLE1;
                      audit insert, update, delete, select on xxx.TABLE2;
                      
                      WHEN               USERID       OBJ$NAME             SES$ACTIONS
                      ------------------ ------------ -------------------- ------------------
                      25-OCT-07 08:19:44 XXX          TABLE1               ---------S------
                      25-OCT-07 08:20:23 XXX          TABLE1               ---------S------
                      • 8. Re: Find when was the table last updated ?
                        626981
                        Thanks Steven
                        • 9. Re: Find when was the table last updated ?
                          JustinCave
                          Since you're on 10.2, there are a couple of other possibilities.

                          - You could use the ORA_ROWSCN pseudocolumn and the SCN_TO_TIMESTAMP function to get the upper bound on the last time a row was modified, i.e.
                          SELECT SCN_TO_TIMESTAMP( ORA_ROWSCN )
                            FROM my_table
                          Unless you built your table with rowdependencies enabled, though, the ORA_ROWSCN is going to be tracked at the block level rather than at the row level. And the SCN_TO_TIMESTAMP conversion isn't exact, but it should be reasonably close. Given what you're trying to accomplish, that's probably more than close enough.

                          - You might also look into segment-level statistics. If you are seeing no reads and no writes against all the segments in a table over long periods of time, it's a good bet that it's extraneous. One of the concerns with auditing is that (barring FGA), you're not auditing select statements, and most systems have static (or mostly static) lookup tables that are still very much in use, despite never being modified.

                          Justin
                          • 10. Re: Find when was the table last updated ?
                            ABD - DBA
                            Justin Cave is "The Oracle". :) The guy is just a fountain of information.
                            • 11. Re: Find when was the table last updated ?
                              523455
                              Justine you are always a front runner.
                              Thanks for such a new things to know

                              In addition
                              We can also use the dba_tab_modifications to know the deletes,updates,inserts happened in atable.(But before statistics gathering)

                              :-)
                              • 12. Re: Find when was the table last updated ?
                                748068
                                hi

                                i have one query and the query is that i have one table ABCD_daily which is updated every time we run a procedure but some how the procedure run for 3 times for the same date and in the table ABCD_daily we get 3 entries for one date also all the entries are separated by commas ex: 01-14,01-15,01-16,01-16,01-16 like this and now the problem "i want to delete the last 2 entries from the table ABCD_daily so how to do this?
                                • 13. Re: Find when was the table last updated ?
                                  ramarun
                                  Hi All,

                                  My requirment is to get the last time when a table was either selected/inserted/updated/deleted. I have tried dba_tab_modifications but that didnt yield better results.

                                  I read through this article and tried the following query and when i am executing this query in SQLPLUS

                                  *"select scn_to_timestamp(ora_rowscn) from emp;"*

                                  I am getting the following error

                                  ERROR at line 1:
                                  ORA-08181: specified number is not a valid system change number
                                  ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

                                  What might be the reason please help me.

                                  Thanks in advance
                                  • 14. Re: Find when was the table last updated ?
                                    PrafullaNath
                                    SQL> desc dba_tab_modifications
                                    Name Null? Type
                                    ----------------------------------------- -------- ----------------------------
                                    TABLE_OWNER VARCHAR2(30)
                                    TABLE_NAME VARCHAR2(30)
                                    PARTITION_NAME VARCHAR2(30)
                                    SUBPARTITION_NAME VARCHAR2(30)
                                    INSERTS NUMBER
                                    UPDATES NUMBER
                                    DELETES NUMBER
                                    TIMESTAMP DATE
                                    TRUNCATED VARCHAR2(3)
                                    DROP_SEGMENTS NUMBER

                                    from here you can find when the last insert,update,delete was happened on the table

                                    Edited by: PrafullaNath on May 18, 2010 8:54 PM
                                    1 2 Previous Next