Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nag.

Asked: January 27, 2002 - 5:06 pm UTC

Last updated: September 19, 2024 - 9:40 am UTC

Version: 8.1.7.2

Viewed 10K+ times! This question is

You Asked

Tom

1.Are flash back queries useful for the developer or the DBA.

2.I observe that one needs the SCN number to do a flash back query. How can I
as a developer get to know the SCN number of a transaction.

3.A single transaction can have multiple deletes and a single SCN number
identifying all of these deletes. What if I want to flash back only a single
individual delete.

Can yu explain with an example of flashback query.

Thank you

and Tom said...

1) both. its a tool, it has uses for both. eg: user says "i accidently deleted all of my data at noon". If user is developer, developer can flash back, recover data and fix the problem. If user is not database savy - DBA can flash back, recover data and fix the problem.

The developer can use it in their applications. "ok, here is the data BEFORE you updated vs after your update". A flash back query can help you there.


2) dbms_flashback.get_system_change_number returns the current system SCN. Log Miner can be used to look back in time at various events to find SCN's as well.

3) You would flash back to the SYSTEM (not your transactions) SCN at that point in time. The SYSTEM has an SCN, your transaction has an SCN. You care about the SYSTEM SCN with flashback, not your transactions SCN.


And now for the demo.....



We'll begin by creating a table of "keep scns"

As we run through our updates, we'll remember the scn in place when
when we began/ended. We can use this to flash back to various points
in time


tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> create table keep_scn( msg varchar2(25), scn number );

Table created.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue

=================================================================


Now, we'll update and commit the EMP table manmy times in a loop. We
remember the system change number before we start and when we are done.
this way, we can flash back to the beginning, middle or end

remember each commit in the system will up the SCN, its our "clock"


tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> begin
2 insert into keep_scn values ( 'start', dbms_flashback.get_system_change_number );
3
4 for i in 1 .. 100
5 loop
6 update SCOTT.EMP set sal = sal * 1.01;
7 commit;
8 end loop;
9
10 insert into keep_scn values ( 'stop', dbms_flashback.get_system_change_number );
11 commit;
12 end;
13 /

PL/SQL procedure successfully completed.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

Now, lets start flashing back. We'll start with a flash back to the
beginning first:


tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> variable x refcursor
tkyte@TKYTE9I.US.ORACLE.COM> set autoprint on
tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> declare
2 l_scn number;
3 begin
4 select scn into l_scn from keep_scn where msg = 'start';
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8 end;
9 /

PL/SQL procedure successfully completed.


ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5500
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

14 rows selected.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

Now we'll get the SCN that was in effect in the "middle" of update
and view the data at that point in time


tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> declare
2 l_scn number;
3 begin
4 select trunc(avg(scn)) into l_scn from keep_scn;
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8 end;
9 /

PL/SQL procedure successfully completed.


ENAME SAL
---------- ----------
SMITH 1289.77
ALLEN 2579.56
WARD 2015.32
JONES 4796.37
MARTIN 2015.32
BLAKE 4594.84
CLARK 3949.95
SCOTT 4836.67
KING 8867.24
TURNER 2418.33
ADAMS 1773.46
JAMES 1531.58
FORD 4836.67
MILLER 2095.89

14 rows selected.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

Now we'll get the SCN that was in effect at the end
and view the data at that point in time


tkyte@TKYTE9I.US.ORACLE.COM> declare
2 l_scn number;
3 begin
4 select scn into l_scn from keep_scn where msg = 'stop';
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8 end;
9 /

PL/SQL procedure successfully completed.


ENAME SAL
---------- ----------
SMITH 2163.82
ALLEN 4327.72
WARD 3381.09
JONES 8046.79
MARTIN 3381.09
BLAKE 7708.73
CLARK 6626.78
SCOTT 8114.43
KING 14876.49
TURNER 4057.24
ADAMS 2975.28
JAMES 2569.53
FORD 8114.43
MILLER 3516.27

14 rows selected.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

lastly, we'll flash back back to the start, open a cursor on the
data and then use that cursor to restore our data to that point in
time. we'll rollback the work in effect


tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> declare
2 cursor emp_cur is select empno, sal from SCOTT.emp;
3 l_rec emp_cur%rowtype;
4 l_scn number;
5 begin
6 select scn into l_scn from keep_scn where msg = 'start';
7 dbms_flashback.enable_at_system_change_number( l_scn );
8 open emp_cur;
9 dbms_flashback.disable;
10 loop
11 fetch emp_cur into l_rec;
12 exit when emp_cur%notfound;
13 update SCOTT.emp set sal = l_rec.sal where empno = l_rec.empno;
14 end loop;
15 close emp_cur;
16 commit;
17 end;
18 /

PL/SQL procedure successfully completed.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue

=================================================================

Just to see that it worked...


tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> select ename, sal from SCOTT.emp;

ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5500
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

14 rows selected.

tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM>
tkyte@TKYTE9I.US.ORACLE.COM> set echo off

Table dropped.





Rating

  (35 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reader

Reader, January 27, 2002 - 7:56 pm UTC

Tom,
In 9.0.1, I can get the system SCN number dynamically
from  dbms_flashback.get_system_change_number.

Example:
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                   79414

SQL> alter system switch logfile;

System altered.

SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                   79415

How can I get this system SCN number in earlier
RDBMS versions (8.1.7)

Thanks 

Tom Kyte
January 28, 2002 - 7:51 am UTC

totally, wholly undocumented and not supported method:

create global temporary table t ( x number );
create or replace function get_scn return number
as
pragma autonomous_transaction;
l_scn number;
begin
insert into t values ( userenv('commitscn') ) returning x into l_scn;
commit;
return l_scn;
end;
/
select get_scn from dual;


search www.google.com under the usenet newsgroups for commitscn to get more details on this quirky little function.

Tom, you are THE BEST

Suresh, January 27, 2002 - 9:52 pm UTC


Reader

Reader, January 28, 2002 - 10:00 am UTC

Thanks very much for the code and
the search URL as well

dbms_flashback

Chunhai He, July 20, 2002 - 6:17 pm UTC

I can not try your demo.
The details are as follow.
What is requirement in order to use dbms_flashback
I use Oracle 9i version 1. I grant dbms_flashback to scott
1 declare
2 l_scn number;
3 begin
4 select scn into l_scn from keep_scn where msg = 'start';
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8* end;
9 /
declare
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
ORA-06512: at line 6

Tom Kyte
July 20, 2002 - 6:33 pm UTC

looks like you just created the scott.emp table. wait for about 5 minutes.

Why is this 5 Mnt Wait ...

A reader, July 21, 2002 - 9:16 am UTC

Hi Tom,

Why Should i wait before i issue the Enable_At_time/scn ??

Regards,
Ganesh R

Tom Kyte
July 21, 2002 - 9:42 am UTC

No, you did some DDL on scott.emp, that is what that is saying. You are flashing back too far for that object. You don't have the entire sqlplus session (like I do) so I'm not sure what you really did or didn't do.

Why is this 5 Mnt Wait --

Chunhai He, July 21, 2002 - 4:02 pm UTC

Tom-
I did one more time. It still does not work.
This time I did not create any table. I did not have any
DDL.
I set  ALTER SYSTEM SET UNDO_RETENTION = 1800;
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
 

Tom Kyte
July 21, 2002 - 11:53 pm UTC

You are trying to flash back to before the table was created.

scn getting changed even no body is there..

Ramesh, August 23, 2002 - 8:45 pm UTC

after creating the get_scn function

even though except me no body is there, It is getting incremented without commit.. incremented not by 1 it is incremented by sometimes it is 5 or 6 or 8

Why ?
Is it expected behavior ?

Tom Kyte
August 24, 2002 - 1:33 pm UTC

somebody is commiting then. look to your job queues or other background processes

no body is committing except in get_scn function.

Ramesh, August 25, 2002 - 8:23 am UTC

No body is committing except  in get_scn function.
if only in get_scn funcion is getting committed then increment should be one.. Is there any Oracle Back groud process doing increment of SCN ?

08:23:17 sysadm@BWYHRDEM SQL> select username from v$session where status='ACTIVE'
08:23:45   2  and username is not null;

USERNAME
------------------------------
SYSADM

Elapsed: 00:00:00.00
08:23:52 sysadm@BWYHRDEM SQL> show parameters job

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
job_queue_interval                   integer 60
job_queue_processes                  integer 0
08:23:58 sysadm@BWYHRDEM SQL> 
08:23:58 sysadm@BWYHRDEM SQL> select get_scn from  dual;

   GET_SCN
----------
    852968

Elapsed: 00:00:00.01
08:24:03 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    852974

Elapsed: 00:00:00.02
08:24:05 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    852980

Elapsed: 00:00:00.01 

Tom Kyte
August 25, 2002 - 9:21 am UTC

Must be. and don't do just ACTIVE, look at all sessions. If you have other sessions connected and they just happen to commit, they'll bump the SCN (heck, just a session exiting from SQL*plus will bump it since plus commits on exit).

Why are you worried about it? The SCN is a number(38). It would take 3162315320785266140457397288 years to exhaust them -- even generating many hundreds per second (that is 1,000 / second forever ).

Only one session

Ramesh, August 25, 2002 - 2:40 pm UTC

I am not worrying about the SCN limit..
But the thing is , even though I am only man connected in the database ( No body is connected)..
HOw the SCN got incremented more than one.. ( Please try in your system)

14:41:39 sysadm@BWYHRDEM SQL> select username, status from v$session;

USERNAME                       STATUS
------------------------------ --------
                               ACTIVE
                               ACTIVE
                               ACTIVE
                               ACTIVE
                               ACTIVE
                               ACTIVE
SYSADM                         ACTIVE

7 rows selected.

Elapsed: 00:00:00.02
14:41:47 sysadm@BWYHRDEM SQL> select get_scn from dual;

   GET_SCN
----------
    852994

Elapsed: 00:00:00.04
14:41:55 sysadm@BWYHRDEM SQL> 
14:41:56 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    853000

Elapsed: 00:00:00.02
14:41:57 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    853006

Elapsed: 00:00:00.01
14:41:58 sysadm@BWYHRDEM SQL> /

   GET_SCN
----------
    853012

Elapsed: 00:00:00.02 

Tom Kyte
August 25, 2002 - 5:31 pm UTC

did you notice that it always goes by 6.

Mine goes by two (as I expect).

Not actually seeing the code you are running, not knowing the version, the OS, etc.... Nope, cannot answer you. I wouldn't worry about it personally (or you could use log miner yourself to see whats up)

scn incrementing 6

Ramesh, August 25, 2002 - 9:09 pm UTC

yes you are right..
It is getting incremented by 6.. Why ?

OS : Sun OS 5.8
DB 8.1.7.3

Theory says its shoud be incremented by 1.. But it is getting incremented by 6
appp02:BWYHRDEM:/homes/oracle> sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Sun Aug 25 21:06:21 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter user-name: sysadm
Enter password: 

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

21:06:27 sysadm@BWYHRDEM SQL> 
21:06:28 sysadm@BWYHRDEM SQL> drop table scn_t;

Table dropped.

Elapsed: 00:00:00.28
21:06:35 sysadm@BWYHRDEM SQL> create table scn_t( scn_n number, scn_d date);

Table created.

Elapsed: 00:00:00.03
21:06:54 sysadm@BWYHRDEM SQL> begin
21:07:00   2  for i in 1..10
21:07:13   3  loop
21:07:15   4  insert into scn_t select get_scn, sysdate from dual;
21:08:03   5  end loop;
21:08:07   6  end;
21:08:09   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12
21:08:11 sysadm@BWYHRDEM SQL> select scn_n, to_char(scn_d, 'hh:mi:ss') scn_time from scn_t;

     SCN_N SCN_TIME
---------- --------
    853737 09:08:10
    853743 09:08:10
    853749 09:08:10
    853755 09:08:11
    853761 09:08:11
    853767 09:08:11
    853773 09:08:11
    853779 09:08:11
    853785 09:08:11
    853791 09:08:11

10 rows selected.

Elapsed: 00:00:00.01

Even though SCN has very large limit.. Just curious why it is incremented by 6 

Tom Kyte
August 25, 2002 - 9:24 pm UTC

theory (and practice) says 2, but -- you are on 8173 - which I no longer have.  try it on another supported release like 8174 (what I used).

Still, haven't seen YOUR get_scn ;)  cut and paste -- if I had a nickel for everytime someone cut and pasted my code (but it is exactly the same they say) and they MODIFIED it somehow -- well, I'd be very rich.


still -- hardly curious myself but -- did you even try logminer???  you seem very curious yourself -- log miner might tell you LOTS.

8172:

ops$tkyte@ORA8I.WORLD> create global temporary table t ( x number );

Table created.

ops$tkyte@ORA8I.WORLD> create or replace function get_scn return number
  2  as
  3      pragma autonomous_transaction;
  4      l_scn number;
  5  begin
  6      insert into t values ( userenv('commitscn') ) returning x into l_scn;
  7      commit;
  8      return l_scn;
  9  end;
 10  /

Function created.

ops$tkyte@ORA8I.WORLD> select get_scn from dual;

       GET_SCN
--------------
 6539836230307

ops$tkyte@ORA8I.WORLD> select get_scn from dual;

       GET_SCN
--------------
 6539836230309

ops$tkyte@ORA8I.WORLD> select get_scn from dual;

       GET_SCN
--------------
 6539836230311

ops$tkyte@ORA8I.WORLD> select get_scn from dual;

       GET_SCN
--------------
 6539836230313

ops$tkyte@ORA8I.WORLD> select get_scn from dual;

       GET_SCN
--------------
 6539836230315

8174:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create global temporary table t ( x number );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function get_scn return number
  2  as
  3      pragma autonomous_transaction;
  4      l_scn number;
  5  begin
  6      insert into t values ( userenv('commitscn') ) returning x into l_scn;
  7      commit;
  8      return l_scn;
  9  end;
 10  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual;

           GET_SCN
------------------
     6532309038283

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual;

           GET_SCN
------------------
     6532309038285

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual;

           GET_SCN
------------------
     6532309038287

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual;

           GET_SCN
------------------
     6532309038289

ops$tkyte@ORA817DEV.US.ORACLE.COM> select get_scn from dual;

           GET_SCN
------------------
     6532309038291 

error while using dbms_flashback

Ashwani Singh, August 26, 2002 - 5:30 am UTC

Dear Tom,
The example given by u is great. I my case using dbms_flashback is ginving me an error.

"PLS-00201: identifier 'DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER' must be declared"

Kindly, provide some solutions for the above stated problem,

Regards and thanx in advance,

Ashwani

Tom Kyte
August 26, 2002 - 8:04 am UTC

sounds like you are using 8i and this is new 9i functionality.

Problem with DBMS_FLASHBACK

aa, August 26, 2002 - 6:46 am UTC

Hi Tom ,
I am using Oracle8i Enterprise Edition Release 8.1.7.0.0 .
WHEN i run this block of code

1 declare
2 l_scn number;
3 begin
4 select scn into l_scn from keep_scn where msg = 'start';
5 dbms_flashback.enable_at_system_change_number( l_scn );
6 open :x for select ename, sal from SCOTT.EMP;
7 dbms_flashback.disable;
8* end;
9 /
dbms_flashback.enable_at_system_change_number( l_scn );
*
ERROR at line 5:
ORA-06550: line 5, column 9:
PLS-00201: identifier 'DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER' must be declared
ORA-06550: line 5, column 9:
PL/SQL: Statement ignored
ORA-06550: line 7, column 9:
PLS-00201: identifier 'DBMS_FLASHBACK.DISABLE' must be declared
ORA-06550: line 7, column 9:
PL/SQL: Statement ignored
Get this error.
Does Oracle 8.1.7.0 support DBMS_FLASHBACK package.
I could not find out this package in Oracle Documentation(Oracle Supplied Packages).
Thanks & regards,
A Reader

Tom Kyte
August 26, 2002 - 8:13 am UTC

it is new with 9i (read the first comment in this article, i answer there how to get this info in 8i -- however FLASHBACK QUERY itself is new with 9i)

scn incrementing by 2

Ramesh, August 26, 2002 - 2:54 pm UTC

I tried with log_miner..
Now i am seeing increment of two only..
371720560 INSERT insert into "SYSADM"."SCN_T"("SCN_N","SCN_D") values (371720559,TO_DATE('26-AUG-2002 14:19:38', 'DD-MON-YYYY HH24:MI:SS'));

371720561 START set transaction read write;

371720561 INSERT insert into "SYSADM"."T"("X") values (371720561);

371720561 UPDATE update "SYSADM"."T" set "X" = 371720561 where ROWID = 'AAALIfAAjAAABRxAAQ';

371720562 COMMIT commit;
increment 1 for start and increment for commit.

thanks for your quick response..
You are the best..

Ramesh

why this number is getting increased when there is no dml taking place

A reader, October 30, 2002 - 6:26 am UTC

Hi Tom I've just a new session and queied scn number. It is getting incremented, It is only me who is connected to the database as it on my local PC.

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 30 14:28:16 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272452

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272453

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272453

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272453

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272453

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272454

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272455

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272455

SQL> /

GET_SYSTEM_CHANGE_NUMBER
------------------------
                19272455

SQL>  

Tom Kyte
October 31, 2002 - 4:23 pm UTC

job queues, advanced queues, background processes. They are all out there.

Simply Wonderful!

Jim, July 30, 2003 - 11:44 am UTC


possible cause of ORA-01466:

dzmnd, November 06, 2003 - 8:11 am UTC

The following information I found on METALINK might explain why some people get "ORA-01466: unable to read data - table definition has changed"

Limitations of Flashback query:
--------------------------------

1. Specifying a time will only find the flashback copy to the nearest five minute interval. This is also true of the get_system_change_number.

(</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=174425.1 <code>

Try waiting five minutes after creating the table.


dbms_flashback

SShah, June 29, 2004 - 5:14 pm UTC

In version 9i, Flashback is limited to Data Manipulation Language (DML) commands such as SELECT, INSERT, UPDATE, and DELETE.

Obviously I am able to get the contents of the table from before it was deleted using the flashback query. But when I try truncating the table and then try using the Flashback Query to display the contents of the table as it was 10 minutes ago before the table was truncated and data still existed I get ORA-01466 unable to read data- table defination has changed.

I would like to know how these two --deleting and truncating are different in terms of using with flashback query. Really appreciate your 2 cents on this :)




Tom Kyte
June 29, 2004 - 6:58 pm UTC

truncate doesn't generate any undo for the table.

truncate just cuts it all lose, lets the extents float away.

delete puts the deleted data into undo.

flashback query works on undo.

A reader, July 09, 2004 - 3:26 pm UTC


A reader, July 23, 2004 - 9:58 am UTC


Flashback Table to SCN is approximate?

Dave Anderson, August 23, 2004 - 4:14 pm UTC

I'm confused about SCN - it SEEMS that the SCN we have access to is not precise, i.e. that it too can be 5 minutes off (like flashback to time).  However, the flashback table comes up with the correct result.  puzzling.  
I've audited a flashback Table to see whats happening.  The flashback statement deletes and re-inserts rows that were not updated since the SCN!

SQL> drop table t_audit;

Table dropped.

SQL>
SQL> create table t_audit
  2   (opdate timestamp,
  3    username varchar2(30),
  4    operation varchar2(20),
  5    old_c1 number,
  6    new_c1 number);

Table created.

SQL>
SQL> create or replace trigger t_trigger
  2  after insert or update or delete on t
  3  for each row
  4  declare
  5     reason  varchar2(30);
  6  begin
  7  --   dbms_output.put_line('trigger fired');
  8
  9     if inserting then reason := 'insert';
 10     elsif updating then reason := 'update';
 11     else reason := 'delete';
 12     end if;
 13
 14     insert into t_audit
 15     (opdate, username, operation, old_c1, new_c1)
 16     values (systimestamp, user, reason, :old.c1, :new.c1);
 17
 18  end;
 19  /

Trigger created.

SQL>
SQL> -- Capture the current SCN:
SQL> col x new_value scn
SQL> --select current_scn x from v$database;
SQL> select dbms_flashback.get_system_change_number() x from dual;

         X
----------
   3280078

SQL>
SQL> -- Get a key value so we can update just one row for the test:
SQL> col y new_value c1
SQL> select min(c1) y from t;

         Y
----------
         1

SQL>
SQL> update t set c1 = c1 * 1.5 where c1 = &c1;
old   1: update t set c1 = c1 * 1.5 where c1 = &c1
new   1: update t set c1 = c1 * 1.5 where c1 =          1

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL> flashback table t to scn &scn enable triggers;
old   1: flashback table t to scn &scn enable triggers
new   1: flashback table t to scn    3280078 enable triggers

Flashback complete.

SQL>
SQL> spool off
SQL>
SQL> spool t_audit_after_flashback_table
SQL> col opdate format a30
SQL> col username format a15
SQL> select * from t_audit;

OPDATE                         USERNAME        OPERATION                OLD_C1     NEW_C1
------------------------------ --------------- -------------------- ---------- ----------
23-AUG-04 03.52.30.306000 PM   DAVE            update                        1        1.5
23-AUG-04 03.52.30.887000 PM   DAVE            delete                      1.5
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        2
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        4
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        5
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        6
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        7
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        8
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        9
23-AUG-04 03.52.30.887000 PM   DAVE            delete                       10
23-AUG-04 03.52.30.887000 PM   DAVE            delete                        3
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   1
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   2
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   4
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   5
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   6
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   7
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   8
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   9
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                  10
23-AUG-04 03.52.31.047000 PM   DAVE            insert                                   3

21 rows selected.

SQL> spool off
SQL> select scn_to_timestamp(3280078) from dual;

SCN_TO_TIMESTAMP(3280078)
---------------------------------------------------------------------------
23-AUG-04 03.52.29.000000000 PM
 

Tom Kyte
August 23, 2004 - 4:41 pm UTC

i don't see any flashback queries? or am i missing something?

the mapping of an SCN to wall clock time is imprecise, but the SCN itself is very precise.

the mapping should be within +- 3 seconds in 10g, 5 minutes in 9i...

10g Flashback Table needs up to 5 Minutes to get SCN to Mapping Table

Dave Anderson, August 24, 2004 - 10:14 am UTC

Tom,
Previous example contained a FLASHBACK TABLE statement, not flashback query.  
The 10g SQL Reference, under Flashback Table, states:
Note:
To allow time for the SCN to propagate to the mapping table used by the FLASHBACK TABLE statement, wait a minimum of 5 minutes prior to issuing the following statement. This wait would not be necessary if a previously existing table were being used in this example.

The component I was unaware of is the "mapping table".  So, it seems that the flashback table (possibly flashback query too) needs up to 5 minutes time before the SCN is written to the mapping table and thus is available to the statement.  I guess that explains this error":
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t
  2  (c1 number primary key,
  3   c2 char(1) )
  4  enable row movement;

Table created.

SQL>
SQL> --begin
SQL> --dbms_lock.sleep(5);
SQL> --end;
SQL> --/
SQL>
SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL> insert into t values (1, 'a');

1 row created.

SQL> insert into t values (2, 'a');

1 row created.

SQL> insert into t values (3, 'a');

1 row created.

SQL> insert into t values (4, 'a');

1 row created.

SQL> insert into t values (5, 'a');

1 row created.

SQL> insert into t values (6, 'a');

1 row created.

SQL> insert into t values (7, 'a');

1 row created.

SQL> insert into t values (8, 'a');

1 row created.

SQL> insert into t values (9, 'a');

1 row created.

SQL> insert into t values (10, 'a');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- Capture the SCN so we can flashback to here....
SQL> col x new_value scn
SQL> select dbms_flashback.get_system_change_number x from dual;

         X
----------
   3354209

SQL>
SQL>
SQL> update t set c2 = 'b' where c1 = 1;

1 row updated.

SQL> update t set c2 = 'c' where c1 = 2;

1 row updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> delete from t where c1 in (3,4,5);

3 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> insert into t values (3, 'x');

1 row created.

SQL> insert into t values (4, 'x');

1 row created.

SQL> insert into t values (5, 'x');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> alter session set tracefile_identifier='dave';

Session altered.

SQL>
SQL> alter session set sql_trace=true;

Session altered.

SQL>
SQL> flashback table t to scn &scn;
old   1: flashback table t to scn &scn
new   1: flashback table t to scn    3354209
flashback table t to scn    3354209
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL>
SQL> alter session set sql_trace=false;

Session altered.
 

Tom Kyte
August 24, 2004 - 10:38 am UTC

yes, that has always been true, even with flashback query.

Thanks Tom

Dave Anderson, August 24, 2004 - 11:31 am UTC


Tremendous display of technical excellency

Padmanabh Nimkar, December 24, 2004 - 4:41 am UTC

In this forum there is huge technical profiency displayed by Tom as an Oracle Guru, but from the users as well. Even reading throug discussions helps a lot.

"as of scn" and changes in same tx

Alberto Dell'Era, April 19, 2005 - 5:05 pm UTC

create table test (x varchar2(4));
create table target (x varchar2(10));

-- wait for 5 minutes to avoid "ORA-01466: unable to read data - table definition has changed"

declare
l_scn number;
begin
insert into test (x) values ('TEST');

l_scn := dbms_flashback.get_system_change_number ;

insert into target(x) select 'regular' from test;
insert into target(x) select 'as of scn' from test as of scn l_scn;

end;
/

On 9.2.0.6:
dellera@ORACLE9I> select * from target;

X
------------------------------
regular

On 10.1.0.3:
dellera@ORACLE10> select * from target;

X
------------------------------
regular

Since the 'TEST' row was there when i read the scn via dbms_flashback, i would have expected both insert-select stmts to see the row (same consistent image of the db).

Is this expected behaviour - documented somewhere ?
Or should I log a bug ?

Tom Kyte
April 19, 2005 - 7:51 pm UTC

I do not believe this to be a bug. When you flashback, you are saying "show me the commited state of the database as of this point in time". it was not committed.

Alberto Dell'Era, April 20, 2005 - 8:51 am UTC

> show me the committed state

Thanks, i didn't know the "committed" part ...

Batch Job backup

Goh, January 16, 2006 - 1:19 am UTC

Hi Tom,

We have a set of batch jobs (about 200 jobs) which trigger every night 2-3AM (not fixed time frame) depend on the trigger file send from the mainframe. How do I get the current scn number in case I need it for point in time recovery (Recover until the point before the batch job run). I am not sure if the current scn and bms_flashback.get_system_change_number can provides the exact scn for recovery or not if database recovery is needed. Appreciate if you can suggest us what is the best approach for this case. We are using 10.1.2. Thanks

Rgds
Goh

Tom Kyte
January 16, 2006 - 9:33 am UTC

if you are using 10.2 - you can set named savepoints.

in 10.1 and before (before till 9i) you can use dbms_flashback.get_system_change_number.

that SCN returned can be used to restore the database to the state it was in as of the point in time you called "get system change number" later (using flashback database or point in time recovery)

set savepoint vs flashback database for batch jobs

goh, January 17, 2006 - 4:01 am UTC

Hi Tom,

Thanks for your valuable inputs. If I understand correctly, set xx savepoint is applicable if all the batch jobs are running in the same sql session. Unfortunately, our batch jobs are in different sessions (autosys triggers the batch job one by one. the job can be running in parallel depend on the condition). Sorry for not making my statement clear early.

Do you think set savepoint in 10gR2 still applicable in this case or flashback database is a better option ? Thanks.

Rgds
Goh

Tom Kyte
January 17, 2006 - 8:52 am UTC

you'll really need to say what you need to be able to do??

Are you trying to say "each batch should be capable of independently rolling back only ITS work"

If so, then, well, simply *do not commit until your transaction, your unit of work is complete*.


Are you trying to say "I want to put the entire database back the way it was at point X in time"

If so, then flashback database


In any case, you'll need to state a bit more clearly what the goal is here.

Truncated table

Neeraj Bedi, December 26, 2007 - 7:57 am UTC

Hi,
I have a table t_updates. I truncated this table 2 days back; but now i require the data from that same table. I tried using DBMS_FLASHBACK.ENABLE AT_TIME and SELECT ... AS OF TIMESTAMP to 3 days back; but came up with the error :
ORA-01466
unable to read data - table definition has changed

In the posts, I read that flashback works on deleted/modified rows. I've also read that it can bring back the dropped tables. But, isnt there a way to bring back the truncated records.
If the answer is Yes, then this must be the most dangerous statement to use :)

Please reply and tell a way to bring back the records i accidently truncated.

Thanks in advance.
Tom Kyte
December 26, 2007 - 9:30 am UTC

"is not there a way to bring back the truncated records"

The answer is "YES"

Yes, there is NOT a way.... But that is probably not what you meant.


short of flashback database - put the ENTIRE DATABASE back to the point in time. But I doubt you have the flash recovery area sized for two days - so it is unlikely to work.

So, the other way to recover from this is

a) restore system, rollback and the tablespace that contained this table from a backup that was taken more than two days ago. do this on ANOTHER MACHINE.

b) perform a point in time recovery of this database to a point in time right before you truncated the table.

c) cancel the recovery - open this mini instance and export out the data you need (or transport that tablespace if that makes sense)


ORA-01466: unable to read data

Rajeshwaran, Jeyabal, May 29, 2012 - 2:40 am UTC

Tom:

Can you please help me to understand why i am getting this error (ORA-01466: unable to read data)? rather than getting x =1 for the below query ?

rajesh@ORA11GR2> create table t (x number);

Table created.
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into t values(1);

1 row created.
rajesh@ORA11GR2> commit;

Commit complete.
rajesh@ORA11GR2> column scn1 new_val scn_1
rajesh@ORA11GR2> select dbms_flashback.get_system_change_number as scn1
  2  from dual;

      SCN1
----------
 156089664
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_lock.sleep(4);

PL/SQL procedure successfully completed.
rajesh@ORA11GR2> update t set x = x + 1;

1 row updated.
rajesh@ORA11GR2> commit;

Commit complete.
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from t;

         X
----------
         2
rajesh@ORA11GR2> select * from t as of scn(&scn_1);
old   1: select * from t as of scn(&scn_1)
new   1: select * from t as of scn( 156089664)
select * from t as of scn( 156089664)
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

rajesh@ORA11GR2>
rajesh@ORA11GR2> column scn2 new_val scn_2
rajesh@ORA11GR2> select dbms_flashback.get_system_change_number as scn2
  2  from dual;

      SCN2
----------
 156089668
rajesh@ORA11GR2> exec dbms_lock.sleep(4);

PL/SQL procedure successfully completed.
rajesh@ORA11GR2> update t set x = x + 1;

1 row updated.
rajesh@ORA11GR2> commit;

Commit complete.
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from t;

         X
----------
         3
rajesh@ORA11GR2> select * from t as of scn(&scn_2);
old   1: select * from t as of scn(&scn_2)
new   1: select * from t as of scn( 156089668)

         X
----------
         2
rajesh@ORA11GR2> select * from t as of scn(&scn_1);
old   1: select * from t as of scn(&scn_1)
new   1: select * from t as of scn( 156089664)
select * from t as of scn( 156089664)
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
rajesh@ORA11GR2>

Tom Kyte
May 29, 2012 - 6:44 am UTC

wait a while, flashback query needs the object to have been around for a while before it can be used.

System/User SCN ???

Ahmad Al-Sallal, May 30, 2012 - 12:31 am UTC

Hi Tom,
Would you clarify this statement please?
""The SYSTEM has an SCN, your transaction has an SCN.""
"You care about the SYSTEM SCN with flashback, not your transactions SCN."
Tom Kyte
May 30, 2012 - 1:03 am UTC


the system has an scn, the scn is like a clock, it ticks at the end of every transaction (commit or rollback).

with flashback query, you care about a systems scn, your transaction doesn't have one unless and until it commits. when you flashback - you flashback to the committed state of the database at a very specific point in time - that system scn.

In short, when the original poster asked:

3.A single transaction can have multiple deletes and a single SCN number
identifying all of these deletes. What if I want to flash back only a single
individual delete.


the answer is "you cannot", you cannot flashback query into the middle of a transaction, you can only flashback query into some committed state of the database.

In order to remove a single delete - they could use flashback transaction queries (10g, not 9i, didn't exist back then).

OnkarNath Tiwary, December 05, 2012 - 12:47 am UTC

Tom,

Though we can not flashback one specific transaction from a group of transactions under one SCN, I believe there is a workaround. Here is what I have done:
SQL:SCOTT@tp11g>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                11218915

SQL:SCOTT@tp11g>select * from emp where empno in (7499,7900,1213,7654);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      1213 Onkar      DBA             7566 03-12-2012:13:00:19       3000                    30
      7499 ALLEN      SALESMAN        7698 20-02-1981:00:00:00       1760         20         30
      7654 MARTIN     SALESMAN        7698 28-09-1981:00:00:00       1250       1400         30
      7900 JAMES      CLERK           7698 03-12-1981:00:00:00        950                    30

SQL:SCOTT@tp11g>update emp set sal=0 where empno in (7499,7900,1213,7654);

4 rows updated.

SQL:SCOTT@tp11g>commit;

Commit complete.

SQL:SCOTT@tp11g>select * from emp where empno in (7499,7900,1213,7654);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      1213 Onkar      DBA             7566 03-12-2012:13:00:19          0                    30
      7499 ALLEN      SALESMAN        7698 20-02-1981:00:00:00          0         20         30
      7654 MARTIN     SALESMAN        7698 28-09-1981:00:00:00          0       1400         30
      7900 JAMES      CLERK           7698 03-12-1981:00:00:00          0                    30

SQL:SCOTT@tp11g>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                11218983

SQL:SCOTT@tp11g>select versions_xid,versions_operation,empno from emp versions between scn 11218915 and 11218983;

VERSIONS_XID     V      EMPNO
---------------- - ----------
04000500711C0000 U       1213
                         1213
                         7369
04000500711C0000 U       7499
                         7499
                         7521
                         7566
04000500711C0000 U       7654
                         7654
                         7698
                         7782
                         7788
                         7839
                         7844
                         7876
04000500711C0000 U       7900
                         7900
                         7902
                         7934
                         9991

20 rows selected.

SQL:SCOTT@tp11g>select undo_sql from flashback_transaction_query where xid in
  2  (select versions_xid from emp versions between scn 11218915 and 11218983);

UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '950' where ROWID = 'AAAQ+jAAEAAAAAeAAL';
update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAAQ+jAAEAAAAAeAAE';
update "SCOTT"."EMP" set "SAL" = '1760' where ROWID = 'AAAQ+jAAEAAAAAeAAB';
update "SCOTT"."EMP" set "SAL" = '3000' where ROWID = 'AAAQ+jAAEAAAAAfAAA';


SQL:SCOTT@tp11g>update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAAQ+jAAEAAAAAeAAE';

1 row updated.

SQL:SCOTT@tp11g>update "SCOTT"."EMP" set "SAL" = '1760' where ROWID = 'AAAQ+jAAEAAAAAeAAB';

1 row updated.

SQL:SCOTT@tp11g>commit;

Commit complete.

SQL:SCOTT@tp11g>select * from emp where empno in (7499,7900,1213,7654);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      1213 Onkar      DBA             7566 03-12-2012:13:00:19          0                    30
      7499 ALLEN      SALESMAN        7698 20-02-1981:00:00:00       1760         20         30
      7654 MARTIN     SALESMAN        7698 28-09-1981:00:00:00       1250       1400         30
      7900 JAMES      CLERK           7698 03-12-1981:00:00:00          0                    30

SQL:SCOTT@tp11g>


I understand that in my test case,we are not flash backing the transaction as there is no way to do it(flashing back one transaction out of the group of transactions) but we can certainly do it provided we have undo data.

I have one question about DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure. When I run below code as SYS it works but as SCOTT, it does not.
SQL:SCOTT@tp11g>exec dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('03001C0071260000'),options=>dbms_flashback.cascade);
BEGIN dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('03001C0071260000'),options=>dbms_flashback.cascade); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 1



I have run "grant execute on sys.xid_array to scott" as sys but it does not work. I am certainly missing something but what.

Thanks in advance.

Onkar
Tom Kyte
December 14, 2012 - 1:40 pm UTC

SCOTT would need SELECT ANY TRANSACTION granted to them.

OnkarNath Tiwary, December 16, 2012 - 12:18 am UTC

Tom,

SCOTT has SELECT ANY TRANSACTION privilege.
11:39:47 SQL:SCOTT@tp11g>select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
ANALYZE ANY
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SELECT ANY DICTIONARY
ADVISOR
SELECT ANY TRANSACTION

14 rows selected.

11:39:54 SQL:SCOTT@tp11g>select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
SQLT_USER_ROLE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE

11:40:31 SQL:SCOTT@tp11g>update emp set mgr=1215 where empno=9991;

1 row updated.

11:41:01 SQL:SCOTT@tp11g>commit;

Commit complete.

11:42:16 SQL:SCOTT@tp11g>select versions_xid,versions_operation,empno from emp versions between timestamp systimestamp -interval '5' minute
11:42:26   2  and systimestamp;

VERSIONS_XID     V      EMPNO
---------------- - ----------
                         1213
                         1214
                         1215
                         1655
                         7369
                         7499
                         7521
                         7566
                         7654
                         7698
                         7782
                         7788
                         7839
                         7844
                         7876
                         7900
                         7902
                         7934
01000E006D210000 U       9991
                         9991

Even after the permission, issue persists.

11:43:24 SQL:SCOTT@tp11g>exec dbms_flashback.transaction_backout(numtxns=>1,xids=>xid_array('01000E006D210000'),options=>dbms_flashback.nocascade_force);
BEGIN dbms_flashback.transaction_backout(numtxns=>1,xids=>xid_array('01000E006D210000'),options=>dbms_flashback.nocascade_force); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 1




-Onkar

Tom Kyte
December 18, 2012 - 11:10 am UTC

this shows the minimum set of privileges. note the direct grant of create table.

sys%ORA11GR2> create user a identified by a default tablespace users quota unlimited on users;

User created.

sys%ORA11GR2> 
sys%ORA11GR2> create role a_role;

Role created.

sys%ORA11GR2> grant create session, select any transaction to a_role;

Grant succeeded.

sys%ORA11GR2> grant execute on dbms_flashback to a_role;

Grant succeeded.

sys%ORA11GR2> 
sys%ORA11GR2> grant a_role to a;

Grant succeeded.

sys%ORA11GR2> grant create table to a;

Grant succeeded.

sys%ORA11GR2> pause

sys%ORA11GR2> 
sys%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> 
a%ORA11GR2> create table emp as select * from scott.emp;

Table created.

a%ORA11GR2> create table dept as select * from scott.dept;

Table created.

a%ORA11GR2> 
a%ORA11GR2> 
a%ORA11GR2> column start_time new_val START
a%ORA11GR2> column stop       new_val STOP
a%ORA11GR2> column localtimestamp format a40
a%ORA11GR2> 
a%ORA11GR2> exec dbms_lock.sleep(5)

PL/SQL procedure successfully completed.

a%ORA11GR2> 
a%ORA11GR2> 
a%ORA11GR2> select localtimestamp START_TIME from dual;

START_TIME
---------------------------------------------------------------------------
18-DEC-12 12.08.40.980548 PM

a%ORA11GR2> 
a%ORA11GR2> declare
  2      l_rec emp%rowtype;
  3  begin
  4      -- Perform a couple of transactions, each about 1 second apart on the same row...
  5      for i in 1 .. 5
  6      loop
  7          update emp set sal = sal*1.1 where empno = 7788;
  8                  dbms_output.put_line( sql%rowcount || ' rows updated' );
  9          commit;
 10          dbms_lock.sleep(1);
 11      end loop;
 12      delete from emp where empno = 7788;
 13          dbms_output.put_line( sql%rowcount || ' rows deleted' );
 14      update dept set dname = initcap(dname) where deptno = 10;
 15      commit;
 16      for i in 1 .. 10
 17      loop
 18          update emp set ename = ename where ename = 'KING';
 19          commit;
 20          dbms_lock.sleep(1);
 21      end loop;
 22  end;
 23  /
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows updated
1 rows deleted

PL/SQL procedure successfully completed.

a%ORA11GR2> select localtimestamp STOP from dual;

STOP
---------------------------------------------------------------------------
18-DEC-12 12.08.56.071698 PM

a%ORA11GR2> 
a%ORA11GR2> select ename, sal,
  2         versions_operation,
  3         versions_xid
  4    from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
  5   where empno = 7788
  6   order by versions_startscn nulls first
  7  /
old   4:   from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
new   4:   from emp versions between timestamp to_timestamp('18-DEC-12 12.08.40.980548 PM') and to_timestamp('18-DEC-12 12.08.56.071698 PM')

ENAME             SAL V VERSIONS_XID
---------- ---------- - ----------------
SCOTT            3300
SCOTT            3630 U A00D1800B53F0000
SCOTT            3993 U 9F0D1B00C5410000
SCOTT          4392.3 U A20D1A00DF1F0000
SCOTT         4831.53 U 9E0D10007A6C0000
SCOTT         4831.53 D A30D1800101C0000

6 rows selected.

a%ORA11GR2> 
a%ORA11GR2> column versions_xid new_val XID
a%ORA11GR2> select versions_xid
  2    from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
  3   where empno = 7788
  4     and versions_operation = 'D'
  5  /
old   2:   from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
new   2:   from emp versions between timestamp to_timestamp('18-DEC-12 12.08.40.980548 PM') and to_timestamp('18-DEC-12 12.08.56.071698 PM')

VERSIONS_XID
----------------
A30D1800101C0000

a%ORA11GR2> 
a%ORA11GR2> select *
  2    from flashback_transaction_query
  3   where xid = hextoraw( '&XID' )
  4  /
old   3:  where xid = hextoraw( '&XID' )
new   3:  where xid = hextoraw( 'A30D1800101C0000' )

XID               START_SCN START_TIM COMMIT_SCN COMMIT_TI
---------------- ---------- --------- ---------- ---------
LOGON_USER                     UNDO_CHANGE# OPERATION
------------------------------ ------------ --------------------------------
TABLE_NAME
-------------------------------------------------------------------------------
TABLE_OWNER                      ROW_ID
-------------------------------- -------------------
UNDO_SQL
-------------------------------------------------------------------------------
A30D1800101C0000  107804478 18-DEC-12  107804480 18-DEC-12
A                                         1 UPDATE
DEPT
A                                AAAgfXAAEAAAAlLAAA
update "A"."DEPT" set "DNAME" = 'ACCOUNTING' where ROWID = 'AAAgfXAAEAAAAlLAAA'
;

A30D1800101C0000  107804478 18-DEC-12  107804480 18-DEC-12
A                                         2 DELETE
EMP
A                                AAAgfWAAEAAAAlDAAA
insert into "A"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPT
NO") values ('7788','SCOTT','ANALYST','7566',TO_DATE('09-DEC-82', 'DD-MON-RR'),
'4831.53',NULL,'20');

A30D1800101C0000  107804478 18-DEC-12  107804480 18-DEC-12
A                                         3 BEGIN





a%ORA11GR2> exec dbms_lock.sleep(30);

PL/SQL procedure successfully completed.

a%ORA11GR2> 
a%ORA11GR2> exec sys.dbms_flashback.transaction_backout( numtxns => 1, xids => sys.xid_array('&XID'), options => sys.dbms_flashback.nocascade_force);

PL/SQL procedure successfully completed.

a%ORA11GR2> select * from emp where empno = 7788;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7788 SCOTT      ANALYST         7566 09-DEC-82    4831.53
        20


a%ORA11GR2> 


Onkar, December 21, 2012 - 2:21 am UTC

Tom,

You are great. One stop solution for everything. Thanks.

That means even though user has CREATE TABLE privilege , I need to assign CREATE TABLE privilege to the desired user explicitly apart from EXECUTE on DBMS_FLASHBACK and SELECT ANY TRANSACTION privileges. Then only he/she can execute DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure. Correct?


Tom Kyte
January 02, 2013 - 12:33 pm UTC

correct.

commit timestamp in oracle

Louis, September 05, 2024 - 4:47 am UTC

Is there way to get transaction commit timestamp in Oracle?


In DWH table, we are loading data in large batches using MERGE statement. Some of the biggest batches, take 5-10 minutes to execute the merge before we COMMIT. One of the columns ( RECORDED_DT_START) theoretically should have the timestamp of the time when the data is visible in the table, but because of the batch size sometimes there is a substantial amount of time between the moment when Oracle picks system time to store ( the column has current timestamp as default value) and the time when a COMMIT is performed and the entire batch is made visible to SELECT queries from other users.

Is there a way for us to identify the timestamp of the COMMIT itself and store it into some additional log table?


I was also searching on this topic, scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) is reliable to use ?



Thanks!




Connor McDonald
September 17, 2024 - 5:06 am UTC

Not really... if you see some posts about USERENV('COMMITSCN'), please ignore them. That is a bad idea and unsupported.

ORA_ROWSCN can give you an approximate timestamp but I would recommend a different way. Assuming you are doing things in large batches, I would do something like (in pseudo code)

1) create sequence BATCH_NUMBER
2) create table batch_commit_timestamp ( batch int, ts timestamp);

3) Then for your large, long transaction, you do:

- select batch_number.nextval from dual into "this_batch"
- All the records you load, update etc all have this batch number as a column.

4) When you are ready to commit, you do

insert into batch_commit_timestamp values ( this_batch, systimestamp);
commit;



commit timestamp in oracle

Louis, September 19, 2024 - 1:29 am UTC

Thanks, Connor for the suggestion and update.

we were thinking along the similar approach of batch_commit_timestamp table to get the commit timestamp of the transaction.
Chris Saxon
September 19, 2024 - 9:40 am UTC

You're welcome

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.