Oracle Flashback Version Query and Oracle Flashback Transaction Query
====================================================================
No need to db archive mode , recyclebin on
You are informed that the record empno=7499 is missing from
the scott.EMP table. You need to identify the following:
delete from emp where empno=7499;
commit ;
* The transaction identifier of the transaction that deleted the empno record
* The SQL statements necessary to undo the delete
* The user who executed the transaction
Which would you use?
A. Oracle Flashback Drop only
B. Oracle Flashback Version Query only
C. Oracle Flashback Version Query and Oracle Flashback Transaction Query
D. RMAN REPORT command only
Ans : C
Practical example :
-----------------
1. --SHOW all rows of emp table
select * from emp
2. --Delete one row
delete from emp where empno=7499
3. --commit the change
commit
4.----Oracle Flashback Version Query to find out the identified (history) of the transaction.
SELECT versions_xid, versions_operation
FROM emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno=7499;
5.---Oracle Flashback Transaction Query to see the all information (audit) as well as undo sql
SELECT XID, COMMIT_TIMESTAMP, LOGON_USER,OPERATION, TABLE_NAME, TABLE_OWNER, UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid=HEXTORAW('09002700CE180000');
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment