Re: Change Data Capture for Processes and not other databases
Date: Tue, 12 Mar 2013 14:54:14 -0400
Message-ID: <CAE-dsOJ1MVMNfJu1BAWe9G7wApw9V2xxCsh=xXKGCkbt-ARGPw_at_mail.gmail.com>
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_at_gmail.com>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-lReceived on Tue Mar 12 2013 - 19:54:14 CET