/*
|| Oracle 11g Flashback Transaction Backout Listing
||
|| Demonstrates Oracle Database 11g's LogMiner and Flashback Transaction Backout 
|| (FTB) features, including:
|| - Creation of sample objects
|| - Creation of sample transactions
|| - Queries against LogMiner and FTB metadata
|
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Flashback Transaction Backout features, and they should be carefully proofread
|| before being executed against any existing Oracle database to avoid potential 
|| damage!
*/

/* 
|| Listing 1:
|| Preparing for FTB use:
|| 1.) Create objects for FTB target operations
|| 2.) Enable supplemental logging on primary key columns 
|| 3.) Grant appropriate privileges to qualified user accounts
*/

DROP USER ap CASCADE;
CREATE USER ap
    IDENTIFIED BY ap
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    PROFILE DEFAULT
    QUOTA UNLIMITED ON example 
;

GRANT CONNECT, RESOURCE TO ap;
GRANT CREATE PROCEDURE TO ap;
GRANT CREATE PUBLIC SYNONYM TO ap;
GRANT CREATE SEQUENCE TO ap;
GRANT CREATE SESSION TO ap;
GRANT CREATE SYNONYM TO ap;
GRANT CREATE TABLE TO ap;
GRANT DROP PUBLIC SYNONYM TO ap;
GRANT EXECUTE ANY PROCEDURE TO ap;

GRANT ap_read_only TO ap;
GRANT ap_read_only TO ap_secured; 

DROP TABLE ap.vendors CASCADE CONSTRAINTS PURGE;

/*
|| Create AP.VENDORS and related objects
*/

-- Create table
CREATE TABLE ap.vendors (
     vendor_id       NUMBER         NOT NULL
    ,active_ind      CHAR(1)        DEFAULT 'Y' NOT NULL
    ,name            VARCHAR2(128)  NOT NULL
    ,address_line_1  VARCHAR2(40)   DEFAULT 'UNDEFINED' NOT NULL
    ,address_line_2  VARCHAR2(40)   
    ,address_line_3  VARCHAR2(40)   
    ,city            VARCHAR2(40)   NOT NULL
    ,state           CHAR(2)
    ,country         VARCHAR2(30)   NOT NULL
    ,credit_card     VARCHAR2(16)   NOT NULL
    ,credit_limit    NUMBER         NOT NULL
)
    TABLESPACE example;

-- Create indexes and constraints
ALTER TABLE ap.vendors
    ADD CONSTRAINT vendors_pk 
    PRIMARY KEY (vendor_id)
    USING INDEX ( 
        CREATE INDEX ap.vendors_pk_idx
            ON ap.vendors (vendor_id)
            TABLESPACE example
        );

ALTER TABLE ap.vendors
    ADD CONSTRAINT vendors_active_ck 
    CHECK (active_ind IN ('Y','N'));

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

GRANT EXECUTE ON DBMS_FLASHBACK TO ap;
GRANT EXECUTE ON DBMS_LOCK TO ap;
GRANT SELECT ANY TRANSACTION TO ap;

/* 
|| Listing 2:
|| Setting up transactions for FTB evaluation
*/

-- Create transactions for later evaluation with LogMiner and FTB

-----
-- Load test data into AP.VENDORS
-----
TRUNCATE TABLE ap.vendors;

INSERT INTO ap.vendors 
VALUES(
     101
    ,'Y'
    ,'FacesRUs, Inc.'
    ,'1313 Mockingbird Lane'
    ,NULL
    ,NULL
    ,'Seattle'
    ,'WA'
    ,'USA'
    ,3428179310003762
    ,100000.00
);
    
INSERT INTO ap.vendors 
VALUES(
     102
    ,'Y'
    ,'Scared Silly Makeup Gmbh'
    ,'1221 Frightful Plaza'
    ,'Suite 221'
    ,NULL
    ,'Redmond'
    ,'CA'
    ,'USA'
    ,5968100071234567
    ,75000.00
);
    
INSERT INTO ap.vendors 
VALUES(
     103
    ,'Y'
    ,'Blood Imitation Products, LLC'
    ,'1313 Gorey Lane'
    ,NULL
    ,NULL
    ,'Reston'
    ,'VA'
    ,'USA'
    ,6879110044283978
    ,35000.00
);

INSERT INTO ap.vendors 
VALUES(
     104
    ,'Y'
    ,'Tri-Color Corn NA'
    ,'1414 Epicurean Blvd.'
    ,NULL
    ,NULL
    ,'San Francisco'
    ,'CA'
    ,'USA'
    ,5967100039131004
    ,22500.00
);

INSERT INTO ap.vendors 
VALUES(
     105
    ,'Y'
    ,'Superhero Costumes Corporation'
    ,'1505 Clark Kent Drive'
    ,'Suite 200'
    ,NULL
    ,'Metropolis'
    ,'IL'
    ,'USA'
    ,38271780012103
    ,100000.00
);

COMMIT;

ALTER SYSTEM SWITCH LOGFILE;

-----
-- Next, apply two UPDATE statements against the sample data
-----
SET SERVEROUTPUT ON
BEGIN
    DBMS_OUTPUT.PUT_LINE('Beginning SCN Range (before transactions): ' || DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER());
END;
/

SELECT TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') "Current Time" FROM DUAL;

SET SERVEROUTPUT ON
BEGIN
    -- Wait for 15 seconds
    DBMS_LOCK.SLEEP(15);

    UPDATE ap.vendors
       SET address_line_2 = 'c/o Vlad Dracul'
          ,address_line_3 = 'Suite 666'
     WHERE vendor_id = 101;
      
    UPDATE ap.vendors
       SET address_line_2 = 'c/o Victor Frahnkensteen'
          ,address_line_3 = 'Head Surgeon'
     WHERE vendor_id = 102;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('SCN for Transaction #1: ' || DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER());
    
END;
/

ALTER SYSTEM SWITCH LOGFILE;

SELECT TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') "Current Time" FROM DUAL;

/* 
|| Listing 3:
|| Viewing LogMiner information about potential candidate transactions for FTB
*/

-----
-- How many transactions have affected the AP schema for a limited 
-- specific SCN range?
------
TTITLE 'Summary of LogMiner Findings|(from V$LOGMNR_CONTENTS)'
COL scn         FORMAT 99999999     HEADING 'SCN'
COL xid                             HEADING 'TransXID'
COL seg_owner   FORMAT A08          HEADING 'Owner'
COL table_name  FORMAT A15          HEADING 'Table Name' 
COL sql_redo    FORMAT A40          HEADING 'SQL Stmt' WRAP
COL sql_undo    FORMAT A40          HEADING 'Reciprocal' WRAP
SELECT 
    seg_owner
   ,table_name
   ,xid
   ,scn 
   ,COUNT(*)
  FROM v$logmnr_contents
 WHERE seg_owner = 'AP' 
GROUP BY seg_owner, table_name, xid, scn 
 ORDER BY seg_owner, table_name, xid, scn 
;
TTITLE OFF

Sat Dec 20                                                                                                                           page
          Summary of LogMiner Findings
                   (from V$LOGMNR_CONTENTS)

Owner    Table Name      TransXID               SCN   COUNT(*)
-------- --------------- ---------------- --------- ----------
AP       VENDORS         01001E0010050000   1834296          1
AP       VENDORS         010020000F050000   1834348          5
AP       VENDORS         020001000B050000   1834285          1
AP       VENDORS         03000100AA040000   1834341          1
AP       VENDORS         05000F000D050000 1834362          2
AP       VENDORS         07000300A1040000   1834091          1
AP       VENDORS         0800000000050000   1834261          1
AP                       020001000B050000   1834282          1
AP                       02000B000B050000   1834144          1
AP                       090006007A060000   1834130          1

10 rows selected.

-----
-- What are the actual LOGMINER detailed contents?
------
TTITLE 'Detailed Transactions and Reciprocals|(from V$LOGMNR_CONTENTS)'
COL scn         FORMAT 99999999     HEADING 'SCN'
COL xid                             HEADING 'TransXID'
COL seg_owner   FORMAT A08          HEADING 'Owner'
COL table_name  FORMAT A15          HEADING 'Table Name' 
COL sql_redo    FORMAT A40          HEADING 'SQL Stmt' WRAP
COL sql_undo    FORMAT A40          HEADING 'Reciprocal' WRAP
SELECT 
    scn 
   ,xid
   ,seg_owner
   ,table_name
   ,sql_redo
   ,sql_undo
  FROM v$logmnr_contents
 WHERE seg_owner = 'AP' 
;
TTITLE OFF

                                                        Detailed Transactions and Reciprocals
                                                               (from V$LOGMNR_CONTENTS)

      SCN TransXID         Owner  Table Name      Operatio SQL Stmt                                 Reciprocal
--------- ---------------- ------ --------------- -------- ---------------------------------------- ----------------------------------------
  1834348 010020000F050000 AP     VENDORS         INSERT   insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
                                                           ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '101' and "ACTIVE_IND" = 'Y' and
                                                           RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'FacesRUs, Inc.' and "ADDRESS_L
                                                           ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM INE_1" = '1313 Mockingbird Lane' and "AD
                                                           IT") values ('101','Y','FacesRUs, Inc.', DRESS_LINE_2" IS NULL and "ADDRESS_LINE_
                                                           '1313 Mockingbird Lane',NULL,NULL,'Seatt 3" IS NULL and "CITY" = 'Seattle' and "S
                                                           le','WA','USA','3428179310003762','10000 TATE" = 'WA' and "COUNTRY" = 'USA' and "
                                                           0');                                     CREDIT_CARD" = '3428179310003762' and "C
                                                                                                    REDIT_LIMIT" = '100000' and ROWID = 'AAA
                                                                                                    SAbAAFAAAAG8AAA';

  1834348 010020000F050000 AP     VENDORS         INSERT   insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
                                                           ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '102' and "ACTIVE_IND" = 'Y' and
                                                           RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Scared Silly Makeup Gmbh' and
                                                           ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM "ADDRESS_LINE_1" = '1221 Frightful Plaza
                                                           IT") values ('102','Y','Scared Silly Mak ' and "ADDRESS_LINE_2" = 'Suite 221' and
                                                           eup Gmbh','1221 Frightful Plaza','Suite   "ADDRESS_LINE_3" IS NULL and "CITY" = '
                                                           221',NULL,'Redmond','CA','USA','59681000 Redmond' and "STATE" = 'CA' and "COUNTRY
                                                           71234567','75000');                      " = 'USA' and "CREDIT_CARD" = '596810007
                                                                                                    1234567' and "CREDIT_LIMIT" = '75000' an
                                                                                                    d ROWID = 'AAASAbAAFAAAAG8AAB';

  1834348 010020000F050000 AP     VENDORS         INSERT   insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
                                                           ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '103' and "ACTIVE_IND" = 'Y' and
                                                           RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Blood Imitation Products, LLC'
                                                           ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM  and "ADDRESS_LINE_1" = '1313 Gorey Lane
                                                           IT") values ('103','Y','Blood Imitation  ' and "ADDRESS_LINE_2" IS NULL and "ADDR
                                                           Products, LLC','1313 Gorey Lane',NULL,NU ESS_LINE_3" IS NULL and "CITY" = 'Reston
                                                           LL,'Reston','VA','USA','6879110044283978 ' and "STATE" = 'VA' and "COUNTRY" = 'US
                                                           ','35000');                              A' and "CREDIT_CARD" = '6879110044283978
                                                                                                    ' and "CREDIT_LIMIT" = '35000' and ROWID
                                                                                                     = 'AAASAbAAFAAAAG8AAC';

  1834348 010020000F050000 AP     VENDORS         INSERT   insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
                                                           ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '104' and "ACTIVE_IND" = 'Y' and
                                                           RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Tri-Color Corn NA' and "ADDRES
                                                           ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM S_LINE_1" = '1414 Epicurean Blvd.' and "
                                                           IT") values ('104','Y','Tri-Color Corn N ADDRESS_LINE_2" IS NULL and "ADDRESS_LIN
                                                           A','1414 Epicurean Blvd.',NULL,NULL,'San E_3" IS NULL and "CITY" = 'San Francisco
                                                            Francisco','CA','USA','5967100039131004 ' and "STATE" = 'CA' and "COUNTRY" = 'US
                                                           ','22500');                              A' and "CREDIT_CARD" = '5967100039131004
                                                                                                    ' and "CREDIT_LIMIT" = '22500' and ROWID
                                                                                                     = 'AAASAbAAFAAAAG8AAD';

  1834348 010020000F050000 AP     VENDORS         INSERT   insert into "AP"."VENDORS"("VENDOR_ID"," delete from "AP"."VENDORS" where "VENDOR
                                                           ACTIVE_IND","NAME","ADDRESS_LINE_1","ADD _ID" = '105' and "ACTIVE_IND" = 'Y' and
                                                           RESS_LINE_2","ADDRESS_LINE_3","CITY","ST "NAME" = 'Superhero Costumes Corporation
                                                           ATE","COUNTRY","CREDIT_CARD","CREDIT_LIM ' and "ADDRESS_LINE_1" = '1505 Clark Ken
                                                           IT") values ('105','Y','Superhero Costum t Drive' and "ADDRESS_LINE_2" = 'Suite 2
                                                           es Corporation','1505 Clark Kent Drive', 00' and "ADDRESS_LINE_3" IS NULL and "CI
                                                           'Suite 200',NULL,'Metropolis','IL','USA' TY" = 'Metropolis' and "STATE" = 'IL' an
                                                           ,'38271780012103','100000');             d "COUNTRY" = 'USA' and "CREDIT_CARD" =
                                                                                                    '38271780012103' and "CREDIT_LIMIT" = '1
                                                                                                    00000' and ROWID = 'AAASAbAAFAAAAG8AAE';

  1834362 05000F000D050000 AP     VENDORS         UPDATE   update "AP"."VENDORS" set "ADDRESS_LINE_ update "AP"."VENDORS" set "ADDRESS_LINE_
                                                           2" = 'c/o Vlad Dracul', "ADDRESS_LINE_3" 2" = NULL, "ADDRESS_LINE_3" = NULL where
                                                            = 'Suite 666' where "VENDOR_ID" = '101'  "VENDOR_ID" = '101' and "ADDRESS_LINE_2
                                                            and "ADDRESS_LINE_2" IS NULL and "ADDRE " = 'c/o Vlad Dracul' and "ADDRESS_LINE_
                                                           SS_LINE_3" IS NULL and ROWID = 'AAASAbAA 3" = 'Suite 666' and ROWID = 'AAASAbAAFA
                                                           FAAAAG8AAA';                             AAAG8AAA';

  1834362 05000F000D050000 AP     VENDORS         UPDATE   update "AP"."VENDORS" set "ADDRESS_LINE_ update "AP"."VENDORS" set "ADDRESS_LINE_
                                                           2" = 'c/o Victor Frahnkensteen', "ADDRES 2" = 'Suite 221', "ADDRESS_LINE_3" = NUL
                                                           S_LINE_3" = 'Head Surgeon' where "VENDOR L where "VENDOR_ID" = '102' and "ADDRESS
                                                           _ID" = '102' and "ADDRESS_LINE_2" = 'Sui _LINE_2" = 'c/o Victor Frahnkensteen' an
                                                           te 221' and "ADDRESS_LINE_3" IS NULL and d "ADDRESS_LINE_3" = 'Head Surgeon' and
                                                            ROWID = 'AAASAbAAFAAAAG8AAB';           ROWID = 'AAASAbAAFAAAAG8AAB';


/* 
|| Listing 4:
|| Viewing FTB Details
*/

TTITLE 'Flashback Transaction Backout Details|(From DBA_FLASHBACK_TXN_REPORT)'
COL txnname         FORMAT A35      HEADING "Compensating|Transaction|Name" WRAP
COL commit_dtm      FORMAT A11      HEADING "COMMIT|Timestamp" WRAP
COL xidrpt          FORMAT A60      HEADING "Compensating|Transaction|Details" WRAP
SELECT
      compensating_txn_name txnname  
     ,TO_CHAR(commit_time, 'yyyy-mm-dd hh24:mi:ss') commit_dtm
     ,TO_CHAR(xid_report) xidrpt
  FROM dba_flashback_txn_report
;
TTITLE OFF

                                                        Flashback Transaction Backout Details
                                                           (From DBA_FLASHBACK_TXN_REPORT)

Compensating                                    Compensating
Transaction                          COMMIT      Transaction
Name                                 Timestamp   Details
-----------------------------------  ----------- ------------------------------------------------------------
_SYS_COMP_TXN_7410033_TIM_1230612995 2008-12-29  <?xml version="1.0" encoding="ISO-8859-1"?>
                                     22:57:27    <COMP_XID_REPORT XID="0A001C00AD040000" NAME="_SYS_COMP_TXN_
                                                 7410033_TIM_1230612995">
                                                         <TRANSACTION XID="05000F000D050000">
                                                         <CHARACTERISTICS>
                                                         </CHARACTERISTICS>
                                                         <UNDO_SQL>
                                                                 <USQL exec="yes">
                                                                  update "AP"."VENDORS" set "ADDRESS_LINE_2" = 'Suite 221',
                                                  "ADDRESS_LINE_3" = NULL where "VENDOR_ID" = '102' and "ADDR
                                                 ESS_LINE_2" = 'c/o Victor Frahnkensteen' and "ADDRESS_LINE_3
                                                 " = 'Head Surgeon'
                                                                 </USQL>
                                                                 <USQL exec="yes">
                                                                  update "AP"."VENDORS" set "ADDRESS_LINE_2" = NULL, "ADDRE
                                                 SS_LINE_3" = NULL where "VENDOR_ID" = '101' and "ADDRESS_LIN
                                                 E_2" = 'c/o Vlad Dracul' and "ADDRESS_LINE_3" = 'Suite 666'
                                                                 </USQL>
                                                         </UNDO_SQL>
                                                         <DEPENDENT_XIDS>
                                                         </DEPENDENT_XIDS>
                                                         </TRANSACTION>
                                                 <EXECUTED_UNDO_SQL>
                                                 <EXEC_USQL>update "AP"."VENDORS" set "ADDRESS_LINE_2" = 'Sui
                                                 te 221', "ADDRESS_LINE_3" = NULL where "VENDOR_ID" = '102' a
                                                  nd "ADDRESS_LINE_2" = 'c/o Victor Frahnkensteen' and "ADDRES
                                                 S_LINE_3" = 'Head Surgeon'
                                                 </EXEC_USQL>
                                                 <EXEC_USQL>update "AP"."VENDORS" set "ADDRESS_LINE_2" = NULL
                                                 , "ADDRESS_LINE_3" = NULL where "VENDOR_ID" = '101' and "ADD
                                                 RESS_LINE_2" = 'c/o Vlad Dracul' and "ADDRESS_LINE_3" = 'Sui
                                                 te 666'
                                                 </EXEC_USQL>
                                                 </EXECUTED_UNDO_SQL>
                                                 </COMP_XID_REPORT>

TTITLE 'Current Flashback Transaction Backout State|(From DBA_FLASHBACK_TXN_STATE)'
COL cmp_xid                         HEADING "Compensating|XID"
COL trn_xid                         HEADING "Transaction|XID"
COL dpd_xid                         HEADING "Dependent|XID"
COL backout_mode    FORMAT A12      HEADING "Backout|Mode"
COL username        FORMAT A12      HEADING "UserName" 
SELECT 
     RAWTOHEX(compensating_xid) cmp_xid
    ,RAWTOHEX(xid) trn_xid
    ,RAWTOHEX(dependent_xid) dpd_xid
    ,backout_mode
    ,username
  FROM dba_flashback_txn_state
;
TTITLE OFF

                Current Flashback Transaction Backout State
                     (From DBA_FLASHBACK_TXN_STATE)

Compensating     Transaction      Dependent        Backout
XID              XID              XID              Mode         UserName
---------------- ---------------- ---------------- ------------ ------------
090014004D060000 09001B0049060000                  NONCONFLICT_ SYS
                                                   ONLY

0A001C00AD040000 05000F000D050000                  NONCONFLICT_ SYS
                                                   ONLY