Re: Change Data Capture for Processes and not other databases
Date: Wed, 13 Mar 2013 09:28:20 -0700
Message-ID: <5140A924.9080106_at_oracle.com>
Flashback Data Archive uses the commit time (SCN actually) for all the changes made in the transaction, so I think that would solve the problem you describe below: the SCN for the insert in Session 1 will be the SCN for Time 4, and the SCN for the insert in Session 2 will be the SCN for Time 2. However, it is still true that the insert from Session 1 won't be visible to any other session until after the commit by Session 1 at Time 4.
It should be relatively easy to use VERSIONS BETWEEN queries to see all the changes for a time range, assuming the semantics described above will meet your requirements.
The other feature worth considering, probably in combination with Flashback Data Archive, is Continuous Query Notification (CQN): see here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_cqn.htm#ADFNS018
CQN allows you to register queries that will notify you when conditions change in ways that meet the query criteria...
-KJ
*Kevin Jernigan* (650) 607-0392 (o) *Senior Director Product Management* (415) 710-8828 (m) kevin.jernigan_at_oracle.com <mailto: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/12/2013 11:20 AM, Dba DBA 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_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
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 13 2013 - 17:28:20 CET