Search

Top 60 Oracle Blogs

Recent comments

Flashback cursors

Flashback query allows to get data as of required point in time. It’s a nice feature. It’s strange in the implementation though. If you try to use AS OF SCN query with bind variables, it won’t help you to keep number of child cursors low: each execution (independently of the incoming SCN value) will cause a new child cursor to appear with FLASHBACK_CURSOR as the cause. I don’t understand why Oracle is doing it this way.

In the OakTable mailing list there was an idea that it could be due to some additional data stored alongside with the child cursor which may be used by the query, such as index validness or object statistics as of different time. This is just an idea, which doesn’t seem correct to me since with normal queries it’s very easy to get a re-parse on execute if something changes between the parse and execute calls. Like this:

drop table t cascade constraints purge;
create table t as select * from all_objects;
create index t_indx on t(object_id);
exec dbms_stats.gather_table_stats(user, 't', cascade=>true, no_invalidate=>false)

exec dbms_session.session_trace_enable

var x number
var n varchar2(100)
begin
    </pre></div>

    	  	<div class=