It looks like I can use the flashback_transaction_query view to get row_ids from a specific table that have a commit_timestamp in a certain range. I do not see any examples using this feature for this purpose, so I am concerned this won't perform well under stress (200 inserts/second, 10,000 query/minute). I would have to test it extensively. However, is there another tool I can use? I could also copy records from flashback_transaction_query to a new table once/minute (since I only need this for 2 tables) and partition this new table to optimize my queries. Is there a better way to do this? L_LASTSYSTIMESTAMP = The systemtimestamp from the last time I ran this query. select a.*,systimestamp from mytesttable a where rowid in (select row_id from flashback_transaction_query where table_name = 'MYTESTTABLE' and table_owner = user and commit_timestamp >= L_LASTTIMESYSTIMESTAMP); On Tue, Mar 12, 2013 at 2:20 PM, Dba DBA <oracledbaquestions@xxxxxxxxx>wrote: > Thanks, but I don't think that is what I am looking for. > > I need to be query change vectors. In my case, I need to query only the > records that have been committed since the last time I queried them. I > can't relate on a datestamp since commits take place after the insert and > with multiple sessions, I will inevvitble run a query and then commit after > word because some commit is a split second slow. > Session 1 Session 2 Session 3 > Time 0 Insert Record > Time 1 Insert Record > Time 2 Commit > Time 3 run query > Time 4 commit > > The query from session 3 will see the record inserted in Session 2 and > will not see the record inserted in Session. However, if I go by an insert > timestamp, the my insert time in session 2 will be later than session 1. So > I can't rely on this. > > I think change data capture handles this or build my own queuing system > with dbms_aq. However, I need to retrieve records with queries. I don't > have available downstream DBs I can register with Streams or Change Data > Capture to do this. > I think there is a way to use change data capture with just queries. I > have been reading the docs and looking for examples, but the only examples > I see is generic stuff that is already in the docs. > > Anyone know a good way to do this? I will have many sessions inserted and > many sessions querying. There will be multiple subscribers to the same rule > set (this is based on a type field). > > > On Mon, Mar 11, 2013 at 10:39 AM, Kevin Jernigan < > kevin.jernigan@xxxxxxxxxx> wrote: > >> Have you looked at Flashback Data Archive (fka Total Recall)? See >> here<http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#ADFNS01011> >> . >> >> -KJ >> >> *Kevin Jernigan* (650) 607-0392 (o) *Senior Director Product >> Management* (415) 710-8828 (m) kevin.jernigan@xxxxxxxxxx *Advanced >> Compression - ACO*: *Information Lifecycle Management* - ILM Advanced >> Row Compression *Temporal database* (Total Recall etc) Advanced LOB >> Compression *SecureFiles* Advanced LOB Deduplication *Database File >> System* - DBFS RMAN Backup Compression *Direct NFS Client* - dNFS Data >> Pump Export Compression *CloneDB* Data Guard Redo Network Transport >> Compression *Database Resource Manager* - DBRM Flashback Data Archive >> History Table Optimization *Continuous Query Notification* - CQN *Hybrid >> Columnar Compression* - HCC *Index Organized Tables* - IOT *Database >> Smart Flash Cache* *OISP* >> On 3/11/13 7:27 AM, Dba DBA wrote: >> >> Oracle 11.2.0.3 >> Is it possible to set up change data capture for queries? I can register >> something manually that says 'get new records with this value in this field >> from this table'? Would need multiple subscribers for each type. >> Would need the ability to go re-query data in case there is an issue >> passing the data downstream. >> >> I would settle for capturing the ROW_IDs of new records by 'type' and then >> querying a view or table of some sort based on those ROW_IDs. >> >> >> --//www.freelists.org/webpage/oracle-l >> >> >> > -- //www.freelists.org/webpage/oracle-l