Re: A modification time question
the answer is it depends. since you can't modify the table, why not put a trigger to put a last updated, etc time into another table. joe Andrey Bronfin wrote: Dear gurus ! Is there a way to know the time of insert/update for each row in a table ? Or , which rows in a table were inserted/updated since a certain time. ( Sort of find ... -mtime ... for UNIX files) . I CAN NOT modify the structure of the table , i.e. i CAN NOT add a timestamp or other column , just need to deal with what is present. Thanks a lot for your help !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing) -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) IM: n8xcthome or joen8xct -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A modification time question
Hi Aviv ! Thanks for your reply ! Yes , i thought of this snapshot log option . 2 problems : 1) performance overhead - for each DML on my table , i have a DML on the MLOG$_ (snapshot log) table , carried out by a trigger. 2) For each access to the rows in my original table , i need to join with the MLOG$_ table (in order to filter out the neccessary timestamps) - another performance overhead. 3) I'm wondering whether this modification time is stored somewhere in the data dictionary. Thanks a lot ! -Original Message- Sent: Thursday, August 16, 2001 1:17 PM To: 'Andrey Bronfin' create snapshot log Aviv Cohen DBA mailto:[EMAIL PROTECTED] 972-97761395 -Original Message- Sent: Thursday, August 16, 2001 12:14 PM To: LazyDBA.com Discussion Dear gurus ! Is there a way to know the time of insert/update for each row in a table ? Or , which rows in a table were inserted/updated since a certain time. ( Sort of find ... -mtime ... for UNIX files) . I CAN NOT modify the structure of the table , i.e. i CAN NOT add a timestamp or other column , just need to deal with what is present. Thanks a lot for your help !!! Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A modification time question
If you can't modify the structure to add a timestamp column or add a trigger to insert a timestamp into another table, there's no way I know of to track it. Oracle doesn't track this sort of thing on its own; you need some sort of timestamp field. Jon Walthour -Original Message- Bronfin Sent: Thursday, August 16, 2001 7:16 AM To: Multiple recipients of list ORACLE-L Dear gurus ! Is there a way to know the time of insert/update for each row in a table ? Or , which rows in a table were inserted/updated since a certain time. ( Sort of find ... -mtime ... for UNIX files) . I CAN NOT modify the structure of the table , i.e. i CAN NOT add a timestamp or other column , just need to deal with what is present. Thanks a lot for your help !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A modification time question
Create a trigger on insert/update of zour table and let the trigger write tuple in other table with datetime of modification of ROWID of your table. Jan Pruner Dne èt 16. srpen 2001 13:15 jste napsal(a): Dear gurus ! Is there a way to know the time of insert/update for each row in a table ? Or , which rows in a table were inserted/updated since a certain time. ( Sort of find ... -mtime ... for UNIX files) . I CAN NOT modify the structure of the table , i.e. i CAN NOT add a timestamp or other column , just need to deal with what is present. Thanks a lot for your help !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A modification time question
Okay, can you add a trigger? At a prior job, management wanted us not only to track last update/insert but WHAT was done. so I created a second table, duplicate of the first with addition fields of trans_date,user and action then I added a trigger (insert/update/delete) on the original table. if the action was an insert, I inserted a row into the second table, trans_date=sysdate, user=user and action=I if a delete, I inserted a row into the second table, trans_date=sysdate, user=user and action=D if an update, I inserted two roww into the second table, the first had the original row, trans_date=sysdate, user=user and action=O (old values) the second had the new row, trans_date=sysdate, user=user and action=N(new values) the secondary table could be archived/purged as necessary From: Andrey Bronfin [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: A modification time question Date: Thu, 16 Aug 2001 03:15:49 -0800 Dear gurus ! Is there a way to know the time of insert/update for each row in a table ? Or , which rows in a table were inserted/updated since a certain time. ( Sort of find ... -mtime ... for UNIX files) . I CAN NOT modify the structure of the table , i.e. i CAN NOT add a timestamp or other column , just need to deal with what is present. Thanks a lot for your help !!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).