APEX + Flashback + SQL Developer = Undo

Application Express doesn’t really have an undo button. Fortunately, since it runs in the database it can leverage one of my favorite database features: Flashback. I strongly urge every developer and DBA to get more familiar with flashback (OTN overview here). Some things to consider before you start working with flashback:

• The default value for UNDO_RETENTION parameter is 900 seconds (15 minutes). This is WAY TOO LOW in my opinion. If you have plenty of disk space, I’d aim for something more like 48 hours.
• You need to grant execute on dbms_flashback to schemas that need to use that package. APEX has the grant, but for the SQL Developer demos of this post, I had to grant execute on that package to the schema I was using in SQL Developer.
• The SQL Developer integration requires SQL Developer 1.2.1 and APEX 3.0.1.

The basic process of using flashback with APEX to recover from mistakes is:

1. Export a page or application “as of” some time in the past.
2. Import that component, either as a new application number, or overwriting your existing application.

Here’s a screenshot of the web interface to export an application “as of” a previous time:

The problem with the web interface is that if you delete an application, there’s no longer an interface to export that application. This is where SQL Developer 1.2.1 comes in. Below is a screenshot of SQL Developer after I’ve deleted my APEX application 104. As you can see there are no application in the list:

Now we can flashback our whole APEX session using the following code:
exec dbms_flashback.enable_at_time(SYSTIMESTAMP – INTERVAL ’10’ minute);

Notice that my deleted APEX application has magically reappeared since it’s now 10 minutes ago (and we didn’t need 1.21 gigawatts to do it).

I can then right-click on it and export it:

Just for fun, I tested this concept in 11g with Flashback Data Archive (Total Recall). I wrote a block of PL/SQL to loop over every table in APEX and add it to a Flashback Archive. Sure enough, it worked like a charm! Keep in mind this NOT supported (yet), but I did demo it to the APEX team, so we’ll see what happens in the future. This would allow you to view / export any application as of, say, a year ago! Here’s the code I used:

-- connect as sys
grant flashback archive administer to flows_030000
/

create tablespace apex_fb_db_arch1
datafile 'apex_fb_db_arch1.dbf'
size 100m reuse autoextend on next 1m maxsize 500m
nologging extent management local segment space management auto
/

alter user flows_030000 quota unlimited on apex_fb_db_arch1
/

alter session set current_schema=FLOWS_030000
/

create flashback archive apex_fb1
tablespace apex_fb_db_arch1
quota 500m
retention 1 year
/

declare
  fb_enabled        exception;
  pragma 			exception_init (fb_enabled, -55600);
begin
  for c1 in (select table_name from all_tables where owner='FLOWS_030000' and table_name like 'WWV_%')
  loop
    begin
      dbms_output.put_line(c1.table_name);
      execute immediate 'alter table '||c1.table_name||' flashback archive apex_fb1';
      exception
        -- ignore errors from any tables already in an archive, allow all other errors to raise
        when fb_enabled then null;
      end;
  end loop;
end;
/