知道何时在Oracle中更新桌子，没有进行全面扫描

Question

我正在构建一个从几个非常大的表中读取数据的Oracle连接器，有些则分为分区。

我试图弄清楚从上次阅读以避免不必要的查询时，从上次阅读中更新了哪些表。我有最后一个ora_rowscn或updated_at，我发现的唯一方法需要完整的表扫描才能查看表中是否有新的或更新的行。

有没有办法知道在没有完整扫描的情况下插入或更新了一排行？

Answer 1

几个想法：1.创建一个表以通过table_name存储最后一个DML，然后在表上创建一个简单的触发器以更新元表。2.在表上创建材料的视图日志，并使用日志中的数据确定更改。

Answer 2

如果搜索期间有存档日志。您可以使用实用程序logminer。例如：

insert into "ASOUP"."US"("KEY_COLUMN","COD_ROAD","COD_COMPUTER","COD_STATION_OPER","NUMB_TRAIN","STAT_CREAT","NUMB_SOSTAVA","STAT_APPOINT","COD_OPER","DIRECT_1","DIRECT_2","DATE_OPER","PARK","PATH","LOCOMOT","LATE","CAUSE_LATE","COD_CONNECT","CATEGORY","TIME") values ('42018740','988','0','9200','2624','8642','75','9802','1','8891','0',TO_DATE('18-Dec-2018', 'DD-Mon-RRRR'),'0','0','0','0','0','0',NULL,TO_DATE('18-Dec-2018', 'DD-Mon-RRRR'));
     select name, first_time,  next_time
     from v$archived_log
     where first_time >sysdate -3/24
/oracle/app/oracle/product/11.2/redolog/edcu/1_48060_769799469.dbf  18-дек-2018 09:03:06    18-дек-2018 10:22:00   
/oracle/app/oracle/product/11.2/redolog/edcu/1_48061_769799469.dbf  18-дек-2018 10:22:00    18-дек-2018 10:30:02   
/oracle/app/oracle/product/11.2/redolog/edcu/1_48062_769799469.dbf  18-дек-2018 10:30:02    18-дек-2018 10:56:07

运行LogMiner实用程序。

EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_48060_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW); 
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_48061_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile); 
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_48062_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile); 
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
SELECT scn,ROW_ID,to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),
table_name,seg_name,operation,  sql_redo,sql_undo
 FROM v$logmnr_contents
where seg_owner='ASOUP' and table_name='US'

SCN                ROW_ID           TIMESTAMP      TABLE_NAME    SEG_NAME OPERATION                              SQL_REDO  SQL_UNDO                                            

1398405575908  AAA3q2AAoAACFweABi   18-12-2018 09:03:15 US  US,ADCU201902   INSERT  insert into "ASOUP"."US"("KEY_COLUMN","COD_ROAD","COD_COMPUTER","COD_STATION_OPER","NUMB_TRAIN","STAT_CREAT","NUMB_SOSTAVA","STAT_APPOINT","COD_OPER","DIRECT_1","DIRECT_2","DATE_OPER","PARK","PATH","LOCOMOT","LATE","CAUSE_LATE","COD_CONNECT","CATEGORY","TIME") values ('42018727','988','0','8800','4404','1','895','8800','1','8838','0',TO_DATE('18-Dec-2018', 'DD-Mon-RRRR'),'4','2','0','0','0','0',NULL,TO_DATE('18-Dec-2018', 'DD-Mon-RRRR'));  delete from "ASOUP"."US" where "KEY_COLUMN" = '42018727' and "COD_ROAD" = '988' and "COD_COMPUTER" = '0' and "COD_STATION_OPER" = '8800' and "NUMB_TRAIN" = '4404' and "STAT_CREAT" = '1' and "NUMB_SOSTAVA" = '895' and "STAT_APPOINT" = '8800' and "COD_OPER" = '1' and "DIRECT_1" = '8838' and "DIRECT_2" = '0' and "DATE_OPER" = TO_DATE('18-Dec-2018', 'DD-Mon-RRRR') and "PARK" = '4' and "PATH" = '2' and "LOCOMOT" = '0' and "LATE" = '0' and "CAUSE_LATE" = '0' and "COD_CONNECT" = '0' and "CATEGORY" IS NULL and "TIME" = TO_DATE('18-Dec-2018', 'DD-Mon-RRRR') and ROWID = 'AAA3q2AAoAACFweABi';

您可以看到无需完整扫描即可插入的行：

select * from asoup.us where ROWID = 'AAA3q2AAoAACFweABi';