This content has been marked as final. Show 10 replies
So you want to check if the data is coming without consuming lots of computer resources?
If the table in question has created_date or updated_date column that is automatically populated, and that column is indexed, then you can run
Another way is to find out what is the insert statement issued by the Streams for populating the table.
select count(*) from <tab> where updated_date > sysdate – 1/24
Then you can see how many times that statement was executed.
If the statement is constantly called, the number of executions should go up.
SELECT executions from v$sql where sql_id = <sql_id>
Is there a way to see the table row count is getting increased?Exact answer on your question is SELECT COUNT(*)
However if there are columns with a growing value in the table, like id, or timestamp, and they are indexed SELECT MAX(<col>) will be fast.
Also you can monitor Streams Apply itself http://docs.oracle.com/cd/B19306_01/server.102/b14229/strms_apmon.htm
Also, as it is said, you can monitor V$SQL for executions of Insert statement. But it may show lower values for bulk inserts.
Also, you can monitor DBA_TAB_MODOFICATIONS. http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4149.htm
Edited by: user11181920 on Dec 6, 2012 12:12 PM
user13364377 wrote:Check dba_tab_modifications for inserts, updates and deletes. You will probably need to make a call to dbms_stats.flush_database_monitoring_info before your query the view.
We are using streams to replicate table data from one database to other databases.
How can we find out that the destination tables are getting updated. Select count(*) cannot be used as the destination table has more than 50 million rows.
Is there a way to see the table row count is getting increased?
Alternatively you could look at v$segment_statistics (or v$segstat, if you want the cheaper option) for your object and the statistic_name of "db block changes", as this is a fairly good indicator of data change.