This discussion is archived
9 Replies Latest reply: Aug 6, 2012 5:44 AM by Prabodh RSS

Track DBDATE/SYSDATE manipulation

Tarun.Oracle Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    can you give me the link of PL/SQL forum?
  • 6. Re: Track DBDATE/SYSDATE manipulation
    Tarun.Oracle Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points