Re: Change Data Capture for Processes and not other databases

  • From: Dba DBA <oracledbaquestions@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 12 Mar 2013 14:54:14 -0400

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


Other related posts: