Has anyone used Oracle's FLASHBACK_TRANSACTION_QUERY to view previous db transactions? I am trying to use it to view transactions made to a schema within the past 24 hours. I've noticed that querying this view takes an extremely long time. I'm talking 2 hours or more. My undo tablespace size is about 1GB. The version of my database is 10.1.0.4. The query I have been testing with is show below.
SQL> select xid, to_char(START_TIMESTAMP,'dd-mon-yyyy hh24:mi:ss'), LOGON_USER, OPERATION, TABLE_NAME, TABLE_OWNER from flashback_transaction_query where table_owner='TRAVEL' AND start_timestamp>=TO_TIMESTAMP ('2006/06/01 13:00:00', 'YYYY/MM/DD HH24:MI:SS');
Does anyone know how to make this query more efficient?