Hello,

This example just made my day. ;=) Thanks a lot. It is a very useful example in showing the use of flashback queries, and I will pester everyone in our DB-DEV department with it today.

BUT, I sort of stumbled over an interesting thing concerning flashback when testing this. Apparently the db is committing a table when dropping it.

Example:

SQL> create table table_a (item number);

Table created.

SQL> insert into table_a values (1);
SQL> insert into table_a values (2);
SQL> commit;

Commit complete.

SQL> insert into table_a values (99);

1 row created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 3643295271

SQL> select * from table_a minus select * from table_a as of scn 3643295271;

      ITEM
----------
        99


So far, so good, I am just repeating your example above.

Now to the nice stuff:

SQL> drop table table_a;

Table dropped.

SQL> flashback table table_a to before drop;

Flashback complete.




So, now the table is back:

SQL> select * from table_a;

      ITEM
----------
         1
         2
        99




Using the OLD scn I got before the drop:

SQL> select * from table_a minus select * from table_a as of scn 3643295271;

      ITEM
----------
        99


That is my previously (uncommitted) row. BUT when I repeat the example to find uncommitted rows, I find that my table was committed during the drop:


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 3643295346

SQL> select * from table_a minus select * from table_a as of scn 3643295346;

no rows selected


SQL> rollback;

Rollback complete.

SQL> select * from table_a;

      ITEM
----------
         1
         2
        99



Wouldn't this make flashback table (to before drop) quite risky to use in a productive environmen?

Have a nice day!

//magnus

Hi again,

Had a talk to my guys in DEV and they told me that a DDL statement like drop will make an implicit commit, so this was an expected behavior.

To get back to my uncommitted state, I suggest:


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 3643301055

SQL> drop table a_table;


SQL> select object_name, original_name from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$OqELgSAEQnfgRAASeZ7cWg==$0 A_TABLE


  1* select * from "BIN$OqELgSAEQnfgRAASeZ7cWg==$0"
SQL> /

      ITEM
----------
         1
         2
        99

SQL> create table a_table as (select * from "BIN$OqELgSAEQnfgRAASeZ7cWg==$0" as of scn 3643301055);

Table created.

SQL> select * from a_table;

      ITEM
----------
         1
         2


Have a nice day!
//magnus

My question would be "why?" ... and a PK would be nice.

But otherwise, one could also use a "on commit" materialized view ...

gabe@XE> create table a (item  number);

Table created.

gabe@XE> create materialized view a_mv
  2  refresh on commit with rowid
  3  as select * from a;

Materialized view created.

gabe@XE> insert into a values (1);

1 row created.

gabe@XE> insert into a values (2);

1 row created.

gabe@XE> commit;

Commit complete.

gabe@XE> insert into a values (3);

1 row created.

gabe@XE> delete from a where item = 2;

1 row deleted.

gabe@XE> update a set item = 5 where item = 1;

1 row updated.

gabe@XE> col "On the way ..." format a14
gabe@XE>
gabe@XE> select item
  2        ,coalesce(case when count(f1)>0 then 'IN'  end
  3                 ,case when count(f2)>0 then 'OUT' end) "On the way ..."
  4  from (
  5  select 1 f1, to_number(null) f2, a.*
  6  from  a
  7  union all
  8  select to_number(null) f1, 2 f2, a_mv.*
  9  from  a_mv
 10  )
 11  group by item
 12  having count(f1) <> count(f2)
 13  ;

      ITEM On the way ...
---------- --------------
         1 OUT
         2 OUT
         3 IN
         5 IN

gabe@XE>

here's yet another way to identify uncommitted rows
sql > create table table_a (item  NUMBER primary key);

Table created.

sql > create type ntable is table of number;
  2  /

Type created.

sql > create function committed return ntable pipelined is
  2  pragma autonomous_transaction;
  3  begin
  4     for x in (select * from table_a) loop
  5        pipe row(x.item);
  6     end loop;
  7  end;
  8  /

Function created.

sql >
sql > insert into table_a values (1);

1 row created.

sql > insert into table_a values (2);

1 row created.

sql > commit;

Commit complete.

sql >
sql > insert into table_a values(3);

1 row created.

sql > select a.*, case when c.column_value is not null then 'X' end as "committed?"
  2  from table_a a, table(committed) c
  3  where c.column_value(+)=a.item;

      ITEM c
---------- -
         1 X
         2 X
         3

sql > commit;

Commit complete.

sql > select a.*, case when c.column_value is not null then 'X' end as "committed?"
  2  from table_a a, table(committed) c
  3  where c.column_value(+)=a.item;

      ITEM c
---------- -
         1 X
         2 X
         3 X

sql > insert into table_a values(4);

1 row created.

sql > insert into table_a values(5);

1 row created.

sql > select a.*, case when c.column_value is not null then 'X' end as "committed?"
  2  from table_a a, table(committed) c
  3  where c.column_value(+)=a.item;

      ITEM c
---------- -
         1 X
         2 X
         3 X
         5
         4