[Oracle-l] Find which table's data has been changed in last five hours.

Which Oracle version? It depends on the undo you have ... What is the undo
retention setting?

Version 10.2.0.3. Undo retention should be 24 hours I think, let me
double check.



Thanks,

Lu



From: Roger Xu
Sent: Monday, August 03, 2009 2:28 PM
To: Jiang, Lu
Cc: ORACLE-L
Subject: Re: Find which table's data has been changed in last five
hours.



Which Oracle version? It depends on the undo you have ... What is the
undo retention setting?

On Mon, Aug 3, 2009 at 1:15 PM, Jiang, Lu wrote:

Hi DBA gurus,



I need to flashback our EBS database to 10 hours ago, also hope the last
five hours data can be recovered with export/import. Is there a way to
find which table's/schema's data has been changed in last five hours, so
that I could export the schemas/tables before flashback and import them
after flashback?



Thanks,

Lu

Thanks Julio.

dba_tab_modifications returns no rows with today's timestamp :)





From: Julio Aguilar-Chang
Sent: Monday, August 03, 2009 2:39 PM
To: Jiang, Lu
Cc: ORACLE-L
Subject: Re: Find which table's data has been changed in last five
hours.



Try dba_tab_modifications. It has columns such as the table_owner,
table_name, number of inserts/updates/deletes, and the timestamp.

Jiang, Lu wrote:

Hi DBA gurus,



I need to flashback our EBS database to 10 hours ago, also hope the last
five hours data can be recovered with export/import. Is there a way to
find which table's/schema's data has been changed in last five hours, so
that I could export the schemas/tables before flashback and import them
after flashback?



Thanks,

Lu

DBA_TAB_MODIFICATIONS describes modifications to all tables in the database
that have been modified since the last time statistics were gathered on the
tables. Try flashback_transaction_query ...

Thanks Roger. Run query against flashback_transaction_query takes a
while, it returns over 12 hours transaction.

Our problem is caused by an Oracle apps patch, now Oracle ask us to
apply another patch to fix it. If this will not work then I would do the
recovery.

Thanks again for the help.





From: Roger Xu
Sent: Monday, August 03, 2009 2:56 PM
To: Jiang, Lu
Cc: Julio Aguilar-Chang; ORACLE-L
Subject: Re: Find which table's data has been changed in last five
hours.



DBA_TAB_MODIFICATIONS describes modifications to all tables in the
database that have been modified since the last time statistics were
gathered on the tables. Try flashback_transaction_query ...

On Mon, Aug 3, 2009 at 1:52 PM, Jiang, Lu wrote:

Thanks Julio.

dba_tab_modifications returns no rows with today's timestamp :)





From: Julio Aguilar-Chang
Sent: Monday, August 03, 2009 2:39 PM
To: Jiang, Lu
Cc: ORACLE-L
Subject: Re: Find which table's data has been changed in last five
hours.



Try dba_tab_modifications. It has columns such as the table_owner,
table_name, number of inserts/updates/deletes, and the timestamp.

Jiang, Lu wrote:

Hi DBA gurus,



I need to flashback our EBS database to 10 hours ago, also hope the last
five hours data can be recovered with export/import. Is there a way to
find which table's/schema's data has been changed in last five hours, so
that I could export the schemas/tables before flashback and import them
after flashback?



Thanks,

Lu

If the table is being monitored (ALTER TABLE .... MONITORING, which
the default statistics job sets on your behalf) and one executes
dbms_stats.flush_database_monitoring_info, then all the tables which
appear in the
*_TAB_MODIFICATIONS view have had DML or been truncated since the last
time statistics were gathered. If one doesn't run
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO, the information in the
*_TAB_MODIFICATIONS views can be stale.

Thanks Adam for the info. I will look into this. It seems that by default system flush table monitoring info everything 15 minutes automatically, but I cannot remember it clearly.

Fortunately our issue has been fixed by a new patch, but it is always good to be well prepared for the worst case :)

DBA_TAB_MODIFICATIONS describes modifications to all tables in the database
that have been modified since the last time statistics were gathered on the
tables. Try flashback_transaction_query ...