9 Replies Latest reply: Aug 6, 2012 7:44 AM by Prabodh RSS

    Track DBDATE/SYSDATE manipulation

    Tarun.Oracle
      Hello is there any to find out that system date(DBDATE/SYSDATE) has been modified.
      Any trigger or any other process to track what is the :old value and :new value if the DBDATE/SYSDATE has been changed by any way.

      Regards,

      Tarun
        • 1. Re: Track DBDATE/SYSDATE manipulation
          O.Developer
          Do you want to know before changing to new value ?
          Or

          You want to who and when changed the Database system date?

          Or

          You want to track date column on your application /user created table ?

          Please clarify with more information/situations
          • 2. Re: Track DBDATE/SYSDATE manipulation
            Tarun.Oracle
            Assume the current DBDATE is 04/08/2012 18:40:32 (DD/MM/YYYY HH24:MI:SS format)
            now some one change it as 05/08/2013 06:25:00

            as soon as it happen i want to insert those values in a table like one given below.

            CREATE TABLE AUDIT_DATE (
            old_date date,
            new_date date);

            If the OS user who has changed it can be traced then that will be more helpful.

            Regards,
            Tarun
            • 3. Re: Track DBDATE/SYSDATE manipulation
              Venkadesh Raja
              Tarun.Oracle wrote:
              Assume the current DBDATE is 04/08/2012 18:40:32 (DD/MM/YYYY HH24:MI:SS format)
              now some one change it as 05/08/2013 06:25:00

              as soon as it happen i want to insert those values in a table like one given below.

              CREATE TABLE AUDIT_DATE (
              old_date date,
              new_date date);

              If the OS user who has changed it can be traced then that will be more helpful.

              Regards,
              Tarun
              Ok let me explain with simple example. i hope you understand this :)
                                       
                                       
                                                                                                                         create table tb(col date);
              
                                         insert into tb values('04-AUG-2012');
              
                                         select * from tb;
              
                                            COL                       
                                         ------------------------- 
                                           04-AUG-12                 
              
                                          1 rows selected
              
                                         create table tb1(o date,n date);
              
                                         select * from tb1;
              
                                           O                         N                         
                                         ------------------------- ------------------------- 
              
                                         0 rows selected
              
              
              
                                         create or replace trigger usr_test
                                         after insert or update or delete of col on tb 
                                         for each row 
                                         begin
                                         insert into tb1(o,n)values(:old.col,:new.col);
                                         end;
              
              
                                         update tb set col='05-AUG-2012';
              
                                         1 rows updated
              
                                         select * from tb1;
              
                                         O                         N                         
                                         ------------------------- ------------------------- 
                                         04-AUG-12                 05-AUG-12                 
              
                                         1 rows selected
              
                                    
                                  
              • 4. Re: Track DBDATE/SYSDATE manipulation
                Venkadesh Raja
                Tarun.Oracle wrote:
                Hello is there any to find out that system date(DBDATE/SYSDATE) has been modified.
                Any trigger or any other process to track what is the :old value and :new value if the DBDATE/SYSDATE has been changed by any way.

                Regards,

                Tarun
                Pls post these kind of a questions in SQL & PL SQL Forum.
                • 5. Re: Track DBDATE/SYSDATE manipulation
                  Tarun.Oracle
                  can you give me the link of PL/SQL forum?
                  • 6. Re: Track DBDATE/SYSDATE manipulation
                    Tarun.Oracle
                    Things is not so easy.
                    I am looking for a SYSTEM(DBDATE / The OS level date change in the server that is hosting Oracle instance) date change and you are talking about a DML trigger tracking a change in a record. So funny.


                    Tarun
                    • 7. Re: Track DBDATE/SYSDATE manipulation
                      Prabodh
                      Very funny indeed ! You marked the thread {thread:id=2417672} as answered , which implies that you understood what was said there !!

                      There is no trigger of any type in Oracle RDBMS that keeps track of sysdate changes as the DB time comes from the system clock which is set/changed in the OS.

                      In the above thread you understood how to get the remote server's time in a central DB that you use. Now what prevents you from recording these different times that you get per server and then having an exception report , or for that matter trigger any other action of a sensible type (i.e .not trying to change the remote systems time !) in your central database?


                      Cheers,
                      • 8. Re: Track DBDATE/SYSDATE manipulation
                        Tarun.Oracle
                        Because i have implemented that mechanism with a timer which fired in every 10 minutes and i can adjust the time as per my requirement.

                        So it is giving me a *"near Perfect"* report( but not perfect report )and i have produced it to concern people.

                        But if a method actually exists which can allow me to call my procedure as soon as such event occurs (sysdate change)
                        then it can be possible get a Perfect report and also to reduce some extra call to my own DB SERVER.
                        Because, being a individual it is not possible for me to keep my own DB SERVER on for 24x7.

                        Hope i have explained.
                        And thank you for linking it with the original one.

                        Tarun
                        • 9. Re: Track DBDATE/SYSDATE manipulation
                          Prabodh
                          Try posting your query on {forum:id=75} and link back to this thread.
                          Your chances of getting a conclusive reply are much higher on the above forum.

                          Cheers,