Can you FLASHBACK an IDENTITY column?

September 26, 2022

3–5 minutes

I’ve just got back from the DOAG conference, and what a fantastic event it was. I know I’m considered a “long time presenter” but this was my very first DOAG conference (not counting the virtual one from last year) and I was blown away by the enthusiasm of the attendees and the organization of the event. I’ll probably do a little video of highlights soon, but today’s post is to answer a question from an attendee that I promised I would get onto as soon as I got home to Perth.

I mentioned in my Flashback talk that when you perform a flashback table command to rewind a data corruption, then in effect we are performing a sequence of DELETE and INSERT commands to put the impacted table back into a state as of the nominated SCN. For example, I traced a command:


SQL> flashback table scott.imm to scn 16330911765838;

Flashback complete.

and within the trace file, you can see the following statements (I’ve cleaned them up a little for readability)


--
-- populate a temporary table with rowids
--
INSERT INTO SYS_TEMP_FBT 
SELECT ROWID, SYS_FBT_INSDEL 
FROM SCOTT.IMM AS OF SCN 16330911768778

--
-- delete rows that should no longer be there
--
DELETE FROM 
(SELECT S.ROWID 
 FROM SYS_TEMP_FBT T, SCOTT.IMM S 
 WHERE T.RID = S.ROWID 
 AND T.ACTION = 'D' 
 AND T.OBJECT#  = : 1
) V

--
-- reinstate rows with a previous state based on the older SCN
--
INSERT INTO SCOTT.IMM 
SELECT S.* 
FROM SYS_TEMP_FBT T , 
     SCOTT.IMM AS OF SCN 16330911768778 S 
WHERE T.RID = S.ROWID 
AND T.ACTION = 'I' 
AND T.OBJECT# = :2

The question that from an attendee was “What if a table has an identity column?”. This is indeed an interesting question, because we can see that in normal operation, you are not allowed to run an INSERT that refers to an identity column


SQL> create table scott.imm
  2     ( x int generated always as identity, y varchar2(10) );

Table created.

SQL> insert into scott.imm values (0,'X');
insert into scott.imm values (0,'X')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

So what will happen when the internal INSERT command that is needed to run FLASHBACK is executed? Is there a bug lurking just waiting to be discovered?
Let’s find out!

First I’ll load some data into my table which are rows that will be kept after the flashback. Thus they represent the “PRE” state of the table.


SQL> insert into scott.imm (y) values ( 'PRE');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'PRE');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'PRE');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'PRE');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'PRE');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'PRE');

1 row created.

SQL> commit;

Commit complete.

Now I’ll take note of the SCN in the database, because this is where I will be flashing back to.


SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual;

                             SCN
--------------------------------
                  16330911765838

And now I’ll add some more rows, this time tagged as “POST”, or “after” the intended point of flashback.


SQL> insert into scott.imm (y) values ( 'POST');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'POST');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'POST');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'POST');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'POST');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.imm (y) values ( 'POST');

1 row created.

SQL> commit;

Commit complete.

In order to flashback a table, I need to allow rows to be relocated (because they are being deleted/re-inserted) so I’ll run an ENABLE ROW MOVEMENT command.


SQL> alter table scott.imm enable row movement;

Table altered.

And now I can run my flashback


SQL> flashback table scott.imm to scn 16330911765838;

Flashback complete.

As we can see, no error occurred, and the table now only contains values the represent the “PRE” flashback state of the table.


SQL> select * from scott.imm;

                               X Y
-------------------------------- ----------
                               1 PRE
                               2 PRE
                               3 PRE
                               4 PRE
                               5 PRE
                               6 PRE

6 rows selected.

So that’s a good result – an identity column does not cause any problems. But note that we flashed back the table data, not the entire database, so my identity column continues its standard sequential march forward.


SQL> insert into scott.imm (y) values ( 'PRE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.imm;

                               X Y
-------------------------------- ----------
                               1 PRE
                               2 PRE
                               3 PRE
                               4 PRE
                               5 PRE
                               6 PRE
                              13 PRE

7 rows selected.

We do not rewind the sequence back to “7”. We simply continue getting new sequence numbers.

So flashback and identity are perfectly happy together!

*UPDATE*: See the comments below – I spoke too soon!

6 responses to “Can you FLASHBACK an IDENTITY column?”

Jorgen Bosman

September 26, 2022 at 8:56 pm

If you delete some rows:

SQL> delete scott.imm where x>=4;
3 rows deleted
SQL> commit;
Commit complete

And then try to flashback:

SQL> flashback table scott.imm to scn 3317425918866;
flashback table scott.imm to scn 3317425918866
*
ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P00H
ORA-32795: cannot insert into a generated always identity column

Reply
1. Connor McDonald
  
  September 26, 2022 at 9:29 pm
  
  Oh stupid me – I didn’t even check the “rows processed” in my trace files, which would have revealed my example didn’t *require* a re-insertion of rows.
  
  Thanks for this – this was an existing bug in 12.2 and 18, and is fixed in current versions of 19c.
  
  But still – shame on me for not testing rigourously.
  
  Reply
Iudith Mentzel

September 26, 2022 at 9:31 pm

Hi Connor,

I guess that the trace file steps shown above for a FLASHBACK TABLE are only a subset of the steps that really happen.
For example, we only see an INSERT into the temporary table of the ROWID-s that existed at the chosen SCN.
So, for rows inserted AFTER that moment, their ROWID-s will NOT be in the temporary table,
but, however, the FLASHBACK operation will have to delete those rows.

Regarding the IDENTITY column “happiness” with FLASHBACK …
I remember that once Tom Kyte was asked a question regarding some action that apparently was not allowed to be performed directly, but, however, the database itself was performing it internally.
And, his answer was: “We (aka the database) can do anything, since in fact we are those who wrote it”
🙂 🙂

I am glad that you enjoyed DOAG … and, hope that you will also enjoy your next ILOUG conference as much 🙂

Best Regards & Further fun,
Iudith

Reply
Rajeshwaran Jeyabal (@rajeshwaranj)

September 27, 2022 at 11:10 am

But that “delete” statement doesn’t caused any error during Flashback execution – this was from 21c (21.3)

Is there was any Flaw with my test case here ?

demo@PDB1> set feedback off verify off demo@PDB1> create table imm ( x int generated always as identity, 2 y varchar2(10) ) 3 enable row movement; demo@PDB1> insert into imm (y) values ( 'PRE'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'PRE'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'PRE'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'PRE'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'PRE'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'POST'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'POST'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'POST'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'POST'); demo@PDB1> commit; demo@PDB1> insert into imm (y) values ( 'POST'); demo@PDB1> commit; demo@PDB1> demo@PDB1> select y, count(*) 2 from imm 3 group by y;
Y COUNT(*) ---------- ---------- PRE 5 POST 5 demo@PDB1> col n new_val n demo@PDB1> select dbms_flashback.get_system_change_number() n from dual ; N ---------- 16356588 demo@PDB1> delete from imm where x > 4; demo@PDB1> select y, count(*) 2 from imm 3 group by y; Y COUNT(*) ---------- ---------- PRE 4 demo@PDB1> commit; demo@PDB1> flashback table imm to scn &n; demo@PDB1> select y, count(*) 2 from imm 3 group by y; Y COUNT(*) ---------- ---------- PRE 5 POST 5 demo@PDB1> set feedback 6 demo@PDB1> select banner_full from v$version; BANNER_FULL -------------------------------------------------------------------------- Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0
demo@PDB1>

Reply
1. Connor McDonald
  
  September 27, 2022 at 11:26 am
  
  You *read* my comment right? 🙂
  
  Reply
  1. Rajeshwaran Jeyabal (@rajeshwaranj)
    
    September 27, 2022 at 1:28 pm
    
    yes… thanks for that.
    
    .... this was an existing bug in 12.2 and 18, and is fixed in current versions of 19c...
    
    Reply