Re: Change Data Capture for Processes and not other databases

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 12 Mar 2013 14:20:26 -0400
Message-ID: <CAE-dsOLzFYTJBTnk0f1S25KPXoYB1zCQTLEeHhfbBCa7Xy1mGA_at_mail.gmail.com>



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_at_oracle.com>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_at_oracle.com *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.
>
>
> --http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 12 2013 - 19:20:26 CET

Original text of this message