12 Replies Latest reply: Oct 8, 2004 8:57 AM by John Spencer RSS

    DateTime Field

    422388
      I'm trying to use a DateTime field to record the time a certain record/row has been updated/inserted. I would like for this field to reflect the last time this record has been inserted of updated. I would also like this field to be updated without using triggers or update statements that explicitly update this field. It there a way to make this automatic?
        • 1. Re: DateTime Field
          JustinCave
          The only "automatic" way to populate a column is to use a trigger. What is the objection to using a trigger?

          What version of Oracle are you using? 10g has some possibilities for tracking modification dates, but not insert dates.

          Justin
          Distributed Database Consulting, Inc.
          http://www.ddbcinc.com/askDDBC
          • 2. Re: DateTime Field
            Laurent Schneider
            to be updated automatically without using triggers ?

            how then? you should rather code your application to update this field "manually".
            • 3. Re: DateTime Field
              422388
              10g has this feature? So it can't be done without triggers? I think that is the way to go then. Thanks.
              • 4. Re: DateTime Field
                JustinCave
                In 10g, there is a pseudocolumn ORA_ROWSCN which provides a conservative upper bound on the last SCN in which a row was modified and which can be converted, approximately, into the conservative upper bound on the last time a row was modified. You cannot track the time a row was inserted originally, and the ORA_ROWSCN may be later than the actual last modification timestamp.

                If all that is acceptable, you can go for it and upgrade to 10g.

                Justin
                Distributed Database Consulting, Inc.
                http://www.ddbcinc.com/askDDBC
                • 5. Re: DateTime Field
                  425103
                  You could easily use a simple trigger like:

                  CREATE OR REPLACE TRIGGER T_updt_emp_date
                       before insert or update on emp
                       for each row
                       
                  begin
                       :new.REC_UPDT_DATE := SYSDATE;
                                      
                  end;
                  /

                  Just create a column called REC_UPDT_DATE, datatype of DATE. Then install the trigger.
                  • 6. Re: DateTime Field
                    Laurent Schneider
                    We all missed the easiest solution !!!
                    SQL> create table u (t timestamp default current_timestamp, n number);
                    
                    Table created.
                    
                    SQL> insert into u(n) values (1);
                    
                    1 row created.
                    
                    SQL> select * from u;
                    
                    T                                 N
                    --------------------------------- ------
                    14-SEP-04 10.04.29.074443 AM      1
                    • 7. Re: DateTime Field
                      Laurent Schneider
                      hmmm... it will not work for updates of course :-(
                      • 8. Re: DateTime Field
                        13162
                        In 10g you can access a more precise SCN with ORA_ROWSCN by creating the table with the new ROWDEPENDENCIES attribute:
                        LINUX> create table test2 (c1 number) rowdependencies;

                        Table created.

                        [inserts to load table]

                        LINUX> select c1, ora_rowscn from test2;

                        C1 ORA_ROWSCN
                        ---------- ----------
                        2 1460370
                        10 1466659
                        100 1466659

                        LINUX> update test2 set c1 = 100000 where c1 = 100;

                        1 row updated.

                        LINUX> commit;

                        Commit complete.

                        LINUX> select c1, ora_rowscn from test2;

                        C1 ORA_ROWSCN
                        ---------- ----------
                        2 1460370
                        10 1466659
                        100000 1466691
                        • 9. Re: DateTime Field
                          John Spencer
                          Justin:

                          I don't have 10g yet, so can't play with this myself, but I'm curious. How long will ORA_ROWSCN be valid for? That is, if I update a table today and get the ORA_ROWSCN, then six months from now need to know when the row was last modified, will the ORA_ROWSCN map to a meaningful value, or is there a time limit on how long the mapping between SCN and actual date/time is kept?

                          John
                          • 10. Re: DateTime Field
                            JustinCave
                            What I haven't seen, though, is any documentation that indicates that ORA_ROWSCN on a table with row dependencies enabled is "correct", rather than "less incorrect" than ORA_ROWSCN on a table without row dependencies. Plus, enabling row dependencies adds 6 bytes to the size of each row, which goes against the goal of shorter rows.

                            Extending Laurent's point, you can also have the create_user automatically specified
                            SQL> create table t15 ( usr VARCHAR2(30) DEFAULT USER, col1 NUMBER );
                            
                            Table created.
                            
                            SQL> insert into t15( col1 ) values (14);
                            
                            1 row created.
                            
                            SQL> select * from t15;
                            
                            USR                                  COL1
                            ------------------------------ ----------
                            SCOTT                                  14
                            Justin
                            Distributed Database Consulting, Inc.
                            http://www.ddbcinc.com/askDDBC
                            • 11. Re: DateTime Field
                              JustinCave
                              John,

                              My very, very basic testing seems to indicate the same 5-day limit (of database uptime) as in 9i.
                              SCOTT @ JCAVE10G Local> select timestamp_to_scn(current_timestamp) from dual;
                              
                              TIMESTAMP_TO_SCN(CURRENT_TIMESTAMP)
                              -----------------------------------
                                                          3220505
                              
                              SCOTT @ JCAVE10G Local> select scn_to_timestamp(3220505-100000) from dual;
                              
                              SCN_TO_TIMESTAMP(3220505-100000)
                              ---------------------------------------------------------------------------
                              05-OCT-04 11.11.09.000000000 AM
                              
                              SCOTT @ JCAVE10G Local> ed
                              Wrote file afiedt.buf
                              
                                1* select scn_to_timestamp(3220505-200000) from dual
                              SCOTT @ JCAVE10G Local> /
                              
                              SCN_TO_TIMESTAMP(3220505-200000)
                              ---------------------------------------------------------------------------
                              03-OCT-04 06.01.37.000000000 AM
                              
                              SCOTT @ JCAVE10G Local> ed
                              Wrote file afiedt.buf
                              
                                1* select scn_to_timestamp(3220505-400000) from dual
                              SCOTT @ JCAVE10G Local> /
                              select scn_to_timestamp(3220505-400000) from dual
                                     *
                              ERROR at line 1:
                              ORA-08181: specified number is not a valid system change number
                              ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
                              ORA-06512: at line 1
                              
                              
                              SCOTT @ JCAVE10G Local> ed
                              Wrote file afiedt.buf
                              
                                1* select scn_to_timestamp(3220505-300000) from dual
                              SCOTT @ JCAVE10G Local> /
                              select scn_to_timestamp(3220505-300000) from dual
                                     *
                              ERROR at line 1:
                              ORA-08181: specified number is not a valid system change number
                              ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
                              ORA-06512: at line 1
                              
                              
                              SCOTT @ JCAVE10G Local> ed
                              Wrote file afiedt.buf
                              
                                1* select scn_to_timestamp(3220505-250000) from dual
                              SCOTT @ JCAVE10G Local> /
                              
                              SCN_TO_TIMESTAMP(3220505-250000)
                              ---------------------------------------------------------------------------
                              02-OCT-04 02.04.53.000000000 AM
                              
                              SCOTT @ JCAVE10G Local> ed
                              Wrote file afiedt.buf
                              
                                1* select scn_to_timestamp(3220505-275000) from dual
                              SCOTT @ JCAVE10G Local> /
                              
                              SCN_TO_TIMESTAMP(3220505-275000)
                              ---------------------------------------------------------------------------
                              01-OCT-04 12.02.07.000000000 AM
                              Justin
                              Distributed Database Consulting, Inc.
                              http://www.ddbcinc.com/askDDBC
                              • 12. Re: DateTime Field
                                John Spencer
                                Justin:

                                Thanks for the test. It appears that ROWSCN as a longer term audit record is not very usefull. If you want to track create and/or last change date, then additional fields and a trigger, or using Oracle's auditing functions are the only options.

                                Thanks
                                John