1 2 Previous Next 20 Replies Latest reply on May 2, 2013 1:39 PM by Nicolas.Gasparotto

    How to find Last modified/updated time of a particular table

    645653
      Hi All,

      Anyone knows the Oracle command tof ind Last modified/updated time of a particular table kindly reply to this post.

      Thanks.
        • 2. Re: How to find Last modified/updated time of a particular table
          639293
          cinohappdi01sec:/prj2/apps/prj2comn/admin/log/prj2_cinohappdi01sec $ uptime
          12:35am up 20 day(s), 8:16, 7 users, load average: 3.21, 3.07, 3.05
          • 3. Re: How to find Last modified/updated time of a particular table
            577207
            Dave M,

            I am not sure how that will help the user?

            $ uptime
            10:34PM up 2 days, 4:29, 3 users, load average: 1.33, 1.45, 1.50

            Adith
            • 4. Re: How to find Last modified/updated time of a particular table
              577207
              You might review the LAST_DDL_TIME column of ALL_OBJECTS.

              http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352

              Adith
              • 5. Re: How to find Last modified/updated time of a particular table
                489694
                You might review the LAST_DDL_TIME column of
                ALL_OBJECTS.

                http://download.oracle.com/docs/cd/B19306_01/server.10
                2/b14237/statviews_2005.htm#i1583352

                Adith
                This can only see the last ddl time.
                I think ORA_ROWSCN will satisfy the demand.

                SQL> select max(ora_rowscn) from employees;

                MAX(ORA_ROWSCN)
                ---------------
                537080

                SQL> select scn_to_timestamp(537080) from dual;

                SCN_TO_TIMESTAMP(537080)
                ---------------------------------------------------
                18-4月 -08 10.14.18.000000000 上午

                But there is something need attention with ora_rowscn,you can view SQL Reference of 10g
                • 6. Re: How to find Last modified/updated time of a particular table
                  639293
                  its shhowing like this

                  SQL> select max(ora_rowscn) from employees;
                  select max(ora_rowscn) from employees
                  *
                  ERROR at line 1:
                  ORA-00942: table or view does not exist
                  • 7. Re: How to find Last modified/updated time of a particular table
                    Nicolas.Gasparotto
                    I think ORA_ROWSCN will satisfy the demand.
                    Which is not 100% sure. The only one method to get the needed information is to have a dedicated column.

                    Nicolas.
                    • 8. Re: How to find Last modified/updated time of a particular table
                      Nicolas.Gasparotto
                      Dave, it's a 10g feature.

                      Nicolas.
                      • 9. Re: How to find Last modified/updated time of a particular table
                        489694
                        You are right, I have tell him to view the Oracle doc.

                        You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Please refer to CREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.

                        NOROWDEPENDENCIES | ROWDEPENDENCIES

                        This clause lets you specify whether table will use row-level dependency tracking. With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. You cannot change this setting after table is created


                        over
                        • 10. Re: How to find Last modified/updated time of a particular table
                          489694
                          its shhowing like this

                          SQL> select max(ora_rowscn) from employees;
                          select max(ora_rowscn) from employees
                          *
                          ERROR at line 1:
                          ORA-00942: table or view does not exist
                          ORA-00942: table or view does not exist
                          • 11. Re: How to find Last modified/updated time of a particular table
                            639293
                            yes its 10g feature.let me know please.
                            • 12. Re: How to find Last modified/updated time of a particular table
                              P.Forstmann
                              If modified/udpated means that an INSERT/UPDATE/DELETE statement has been run on the table,
                              you could try to use auditing feature as described in the following example:
                              bas001> 
                              bas001> create table t(x int);

                              Table created.

                              bas001> create synonym nt for t;

                              Synonym created.

                              bas001>
                              bas001> select * from v$version;

                              BANNER                                                                                                                             
                              ----------------------------------------------------------------                                                                   
                              Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod                                                                   
                              PL/SQL Release 10.2.0.2.0 - Production                                                                                             
                              CORE     10.2.0.2.0     Production                                                                                                         
                              TNS for 32-bit Windows: Version 10.2.0.2.0 - Production                                                                            
                              NLSRTL Version 10.2.0.2.0 - Production                                                                                             

                              bas001> show parameter audit_trail;

                              Parameter                      TYPE        Value                                                                                   
                              ------------------------------ ----------- -----------------------------------                                                     
                              audit_trail                    string      DB_EXTENDED                                                                             
                              bas001>
                              bas001> audit insert,update,delete on t by access;

                              Audit succeeded.

                              bas001> insert into t values(1);

                              1 row created.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> update t set x=2 where x=1;

                              1 row updated.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> delete t where x=2;

                              1 row deleted.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> commit;

                              Commit complete.

                              bas001> insert into nt values(10);

                              1 row created.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> update nt set x=20 where x=10;

                              1 row updated.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> delete nt where x=20;

                              1 row deleted.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> commit;

                              Commit complete.

                              bas001>
                              bas001> create or replace procedure p
                                2  is
                                3  begin
                                4   insert into t values(30);
                                5   commit;
                                6  end;
                                7  /

                              Procedure created.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> exec p;

                              PL/SQL procedure successfully completed.

                              bas001>
                              bas001> create view v as select x from t;

                              View created.

                              bas001> exec dbms_lock.sleep(1);

                              PL/SQL procedure successfully completed.

                              bas001> update v set x=40 where x=30;

                              1 row updated.

                              bas001> commit;

                              Commit complete.

                              bas001>
                              bas001> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

                              Session altered.

                              bas001> column timestamp format a20
                              bas001> column username format a10
                              bas001> column owner format a10
                              bas001> column obj_name format a10
                              bas001> column action_name format a10
                              bas001> column sql_text format a30
                              bas001> select timestamp, username, obj_name, action_name, sql_text from dba_audit_trail;

                              TIMESTAMP            USERNAME   OBJ_NAME   ACTION_NAM SQL_TEXT                                                                     
                              -------------------- ---------- ---------- ---------- ------------------------------                                               
                              18-JUN-2008 09:52:13 O          T          INSERT     insert into t values(1)                                                      
                              18-JUN-2008 09:52:14 O          T          UPDATE     update t set x=2 where x=1                                                   
                              18-JUN-2008 09:52:15 O          T          DELETE     delete t where x=2                                                           
                              18-JUN-2008 09:52:16 O          T          INSERT     insert into nt values(10)                                                    
                              18-JUN-2008 09:52:17 O          T          UPDATE     update nt set x=20 where x=10                                                
                              18-JUN-2008 09:52:18 O          T          DELETE     delete nt where x=20                                                         
                              18-JUN-2008 09:52:20 O          T          INSERT     INSERT INTO T VALUES(30)                                                     
                              18-JUN-2008 09:52:21 O          T          UPDATE     update v set x=40 where x=30                                                 

                              8 rows selected.

                              bas001>
                              • 13. Re: How to find Last modified/updated time of a particular table
                                knowledgespring
                                ..

                                Message was edited by:
                                user612078
                                • 14. Re: How to find Last modified/updated time of a particular table
                                  645653
                                  Thanks to everyone for your valuable reply.

                                  HI Pierre Forstmann,

                                  Actually my table content is populated through a Cron Job. so the contents are dynamic, Also since this is my production environment table i cant manually inser and delete the rows.

                                  I got 'no rows selected' as output by following ur method. i think reason is i havent made ant transaction recently.

                                  Without doing any manual transaction, is it possible to get the last modified time of a table.

                                  Thanks.
                                  1 2 Previous Next