| Flashback query(!?) [message #297017] |
Tue, 29 January 2008 23:03  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
I am trying to use Flashback Query as Oracle documenation 's introduction, and I tested an example following:
SQL> drop table test purge;
Table dropped.
SQL> create table test as select * from scott.dept@test_meta;
Table created.
SQL> select * from test;
DEPTNO DNAME LOC
---------- -------------- -------------
50 accounting new york
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
According to Oracle document
| Quote: |
Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JOHN had been deleted from the employee table, and the DBA knows that at 9:30AM the data for JOHN was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data had been lost.
|
And I deleted one row from test
SQL> delete from test where deptno=40;
1 row deleted.
SQL> select * from test;
DEPTNO DNAME LOC
---------- -------------- ------------
50 accounting new york
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
SQL>
And then, I used a Flashback Query to define what happens at 11:55
SQL> select * from test
2 as of timestamp to_timestamp('2008-01-30 11:55:00','YYYY-MM-DD HH:MI:SS')
3 where deptno=40;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
SQL>
Recreate the row which was deleted
SQL> ed
Wrote file afiedt.buf
1 insert into test
2 (select * from test as of timestamp to_timestamp('2008-01-30 11:55:00','YYY
Y-MM-DD HH:MI:SS')
3* where deptno=40)
SQL> /
1 row created.
SQL> select * from test;
DEPTNO DNAME LOC
---------- -------------- -------------
50 accounting new york
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
One question which I wondered that is: How do I know about the time when a DML is corrupted/falied/forgot....? In this example, I tested with my Database, okie, but I knew the time.
Thank you for your reply!
Many thank to Michel Cadot who took an example using Flashback multiple tables 1 year ago!
|
|
|
|
| Re: Flashback query(!?) [message #297083 is a reply to message #297017] |
Wed, 30 January 2008 01:23  |
 |
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | How do I know about the time when a DML is corrupted/falied/forgot....?
|
Ask your users when it was correct and it was not.
Use flashback transaction query.
Use flashback query (as you did here).
Use LogMiner.
Regards
Michel
[Updated on: Wed, 30 January 2008 01:24] Report message to a moderator |
|
|
|
| Re: Flashback query(!?) [message #297092 is a reply to message #297083] |
Wed, 30 January 2008 02:14 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
This is the most useful utility I am carring about.
For more information, I would get some example, however, I did not get SCN from a past - time when a table was dropped. I tried to read more, but I could not.
May you have an example to use DBMS_LOGMNR to get SCN and recover point_in_time?
1/ Create a table Test
2/ Drop table Test
3/ Get SCN from archived_log_file before the table is dropped
4/ Incomplete recovery
And, with your Flashback demo to flashback multitables, would you like to explain why did you use DBMS_LOCK.Sleep()?
Thank you very much!
[Updated on: Wed, 30 January 2008 02:16] Report message to a moderator |
|
|
|
| Re: Flashback query(!?) [message #297100 is a reply to message #297092] |
Wed, 30 January 2008 02:45 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
You can use
FROM flash
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
and you can use the pseudo-columns:
VERSIONS_STARTTIME
VERSIONS_ENDTIME , VERSIONS_STARTSCN
VERSIONS_ENDSCN
VERSIONS_XID VERSIONS_OPERATION
To get more info about each row version.
|
|
|
|
|
|
| Re: Flashback query(!?) [message #297110 is a reply to message #297107] |
Wed, 30 January 2008 03:03 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Yes! This is the link
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53865178246302
Here's an example:
SQL> -- Create the tables and display their content --
SQL>
SQL> create table t1 enable row movement
2 as select level val from dual connect by level <=3
3 /
Table created.
SQL> create table t2 enable row movement
2 as select level val from dual connect by level <=3
3 /
Table created.
SQL> exec dbms_lock.sleep(10);
PL/SQL procedure successfully completed.
SQL> select * from t1;
VAL
-------------
1
2
3
3 rows selected.
SQL> select * from t2;
VAL
-------------
1
2
3
3 rows selected.
SQL> col scn new_value scn
SQL> SELECT dbms_flashback.get_system_change_number scn FROM DUAL;
SCN
-------------
4339478652078
1 row selected.
SQL> def scn
DEFINE SCN = 4339478652078 (NUMBER)
SQL>
SQL> -- Modify data --
SQL>
SQL> delete t1 where mod(val,2)=0;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> update t2 set val=val*val;
5 rows updated.
SQL> commit;
Commit complete.
SQL> select * from t1;
VAL
-------------
1
3
2 rows selected.
SQL> select * from t2;
VAL
-------------
1
4
9
3 rows selected.
SQL>
SQL> -- Flashback both tables at the same time --
SQL>
SQL> flashback table t1,t2 to scn &scn;
Flashback complete.
SQL> select * from t1;
VAL
-------------
1
2
3
3 rows selected.
SQL> select * from t2;
VAL
-------------
1
2
3
3 rows selected.
Regards
Michel
|
|
|
|
| Re: Flashback query(!?) [message #297111 is a reply to message #297110] |
Wed, 30 January 2008 03:07 |
|
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I used dbms_lock because there must be a minimum time (a couple of seconds) between the last DDL and the ability to use flashback query.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
