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
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
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
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
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 ?
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
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
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
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
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
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>
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 :)
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
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.
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 ?
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
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
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.
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>
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."
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
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
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?
January 02, 2013 - 12:33 pm UTC
correct.