Re: Change Data Capture for Processes and not other databases

You may consider ORA_ROWSCN and check it against some arbitrary scn Your app can keep as a last scn checked, but You need to carefully check if it suits Your needs - by default (at least in 10g) it was changed for block (and not row) change and there were some situations when it was not changed exactly like one may expect to.

Regards
Remigiusz

On 12.03.2013 19:54, Dba DBA wrote:
> 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
>>>
>>>
>>>
>>

-- 
Pole na kazi

----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
pos   : Senior DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A st, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia, 
wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem: 0000021828, 
dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku, 
VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego, 
o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych, 
NIP: 586-000-78-20, REGON: 190024711--
http://www.freelists.org/webpage/oracle-l