{"id":1420,"date":"2018-04-23T09:54:10","date_gmt":"2018-04-23T01:54:10","guid":{"rendered":"http:\/\/www.821121.com\/?p=1420"},"modified":"2021-03-02T10:51:29","modified_gmt":"2021-03-02T02:51:29","slug":"%e9%97%aa%e5%9b%9e%e4%ba%8b%e5%8a%a1%e6%9f%a5%e8%af%a2%ef%bc%88flashback-transcation-query%ef%bc%89%e6%a8%a1%e6%8b%9f%e6%b5%8b%e8%af%95","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1420","title":{"rendered":"\u95ea\u56de\u4e8b\u52a1\u67e5\u8be2\uff08Flashback transcation query\uff09\u6a21\u62df\u6d4b\u8bd5"},"content":{"rendered":"
Flashback transcation query \u80fd\u591f\u67e5\u8be2\u4e8b\u52a1\u5bf9\u8868\u6240\u505a\u7684\u64cd\u4f5c\uff0c\u901a\u8fc7UNDO SQL\u53ef\u4ee5\u53d6\u6d88\u5bf9\u4e8b\u52a1\u6240\u505a\u7684\u4fee\u6539\u3002\u67e5\u8be2FLASHBACK_TRANSACTION_QUERY\u8fd9\u4e2a\u6570\u636e\u5b57\u5178\u8868\u6765\u83b7\u53d6\u5b57\u5178\u4e8b\u52a1ID\u7684\u4fe1\u606f.\nFlashback Transaction Query\u5b9e\u9645\u4e0a\u662f\u67e5\u8be2\u7684\u6570\u636e\u5b57\u5178flashback_transaction_query\u3002\u53ef\u4ee5\u6839\u636eflashback_transaction_query \u7684undo_sql\u5217\u503c\u8fd4\u56de\u6570\u636e\u4ee5\u524d\u7248\u672c<\/code><\/pre>\n
flashback_transaction_query \u5217\u8bf4\u660e\uff1a\n\nSQL> desc flashback_transaction_query\n Name                                      Null?    Type\n ----------------------------------------- -------- ----------------------------\n XID                                                       RAW(8)                 --\u4e8b\u52a1ID\n START_SCN                                          NUMBER                --\u4e8b\u52a1\u8d77\u59cbSCN\uff0c\u5373\u7b2c\u4e00\u4e2adml\u7684SCN\n START_TIMESTAMP                             DATE                      --\u4e8b\u52a1\u5176\u5b9e\u65f6\u95f4\u6233,\u5373\u7b2c\u4e00\u4e2adm\u7684\u65f6\u95f4\u6233\n COMMIT_SCN                                      NUMBER                --\u63d0\u4ea4\u4e8b\u52a1\u65f6\u7684SCN\n COMMIT_TIMESTAMP                         DATE                      -- \u63d0\u4ea4\u4e8b\u52a1\u65f6\u7684\u65f6\u95f4\u6233\n LOGON_USER                                      VARCHAR2(30)       --\u672c\u6b21\u4e8b\u52a1\u7684\u7528\u6237\n UNDO_CHANGE#                                NUMBER                --\u64a4\u9500SCN\n OPERATION                                         VARCHAR2(32)       --\u6267\u884c\u7684dml\u64cd\u4f5c\uff1aDELETE,INSERT,UPDATE,BEGIN,UNKNOWN\n TABLE_NAME                                       VARCHAR2(256)     --dml\u66f4\u6539\u7684\u8868\n TABLE_OWNER                                     VARCHAR2(32)      --\u8868\u7684\u6240\u6709\u8005\n ROW_ID                                                VARCHAR2(19)       --\u4fee\u6539\u884c\u7684ROWID\n UNDO_SQL                                           VARCHAR2(4000)   -?-\u64a4\u9500dml\u7684sql\u8bed\u53e5\n\n--\u4f7f\u7528\u95ea\u56de\u4e8b\u52a1\u67e5\u8be2\u524d\uff0c\u5fc5\u987b\u542f\u7528\u91cd\u505a\u65e5\u5fd7\u6d41\u7684\u5176\u4ed6\u65e5\u5fd7\u8bb0\u5f55\uff0c\u91cd\u505a\u65e5\u5fd7\u6d41\u4e0eLog Miner\u4f7f\u7528\u7684\u6570\u636e\u76f8\u540c\uff0c\u53ea\u662f\u63a5\u53e3\u4e0d\u540c\u3002\n<\/code><\/pre>\n
\u4f8b\u5b50\uff1a\nSQL> sqlplus \/ as sysdba\n\nSQL> alter database add supplemental log data;\n\nDatabase altered.\n\nSQL> alter database add supplemental log data (primary key) columns;\n\nDatabase altered.\n\nSQL> grant select any transaction to scott;\n\nGrant succeeded.\n\nSQL> update xyc_t1 set sal=9999 where empno=7902;     --\u66f4\u6539\u503csal=9999\n\n1 row updated.\n\nSQL> commit;\n\nCommit complete.\n\nSQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;\n\nTO_DATE(SYSDATE,'YY\n-------------------\n20210121 17:29:46\n\nSQL> update xyc_t1 set sal=99999 where empno=7902;  --\u66f4\u6539\u503csal=99999\n\n1 row updated.\n\nSQL> commit;\n\nCommit complete.\n\nSQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;\n\nTO_DATE(SYSDATE,'YY\n-------------------\n20210121 17:30:17\n\nselect versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,empno\nfrom xyc_t1 versions between timestamp to_timestamp('20210121 17:29:46','YYYY-MM-DD HH24:MI:SS') and to_timestamp('20210121 17:30:17','YYYY-MM-DD HH24:MI:SS');\n\nVERSIONS_STARTSCN VERSIONS_STARTTIME                                                          VERSIONS_ENDSCN VERSIONS_ENDTIME    VERSIONS_XID      V      EMPNO\n----------------- --------------------------------------------------------------------------- --------------- --------------------------------------------------------------------------- ---------------- - ----------\n          2170077 21-JAN-21 05.30.10 PM                                                                                           0400130058040000 U        7902\n                                                                                                      2170077 21-JAN-21 05.30.10 PM                                                                                 7902\n                                                                                                                                                            7788\n                                                                                                                                                           7698\n\n--\u67e5\u8be2\u4e8b\u52a1id\n\nselect start_scn,commit_scn,logon_user,operation,table_name,undo_sql\nfrom flashback_transaction_query\nwhere xid=hextoraw('0400130058040000');\n\n--\u67e5\u51faundo sql\n\nSQL> update "SYS"."XYC_T1" set "SAL" = '9999' where ROWID = 'AAAVpSAABAAAW9hAAA';\n\n1 row updated.\n\nSQL> select * from xyc_t1;\nselect * from xyc_t1;\n\n     EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO\n---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------\n      7698 BLAKE                MANAGER                  7839 1981-05-01 00:00:00       2850                    30\n      7902 FORD                 ANALYST                    7566 1981-12-03 00:00:00       9999                    20\n\n--\u8fd4\u56de\u5230\u4e0a\u4e00\u7248\u672c\uff0c\u5373sal\u503c\u4e3a9999\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Flashback transcation que<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-1420","post","type-post","status-publish","format-standard","hentry","category-oracle-11g"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1420","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1420"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1420\/revisions"}],"predecessor-version":[{"id":1421,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1420\/revisions\/1421"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1420"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}