Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

APEX + Flashback + SQL Developer = Undo

Posted by Tyler Muth on November 17, 2007

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:

apex_export1

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:

sqldev_apex2-1

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

sqldev_apex2-2

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:

sqldev_apex2-3

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;
/

8 Responses to “APEX + Flashback + SQL Developer = Undo”

  1. John Scott said

    Tyler,

    Great post, Flashback is amongst one of the most under-used areas of APEX development, the ability to export your app ‘as-of’ some time in the past is just awesome when you compare it to other development environments.

    I’ve also shown clients how you can use flashback to easily regress a ‘failed’ application upgrade in production (along with associated DDL updates etc) in seconds/minutes compared to the usual headache that is involved when trying to regress back to your previous version of your application when using other development environments (along with config files, regressing schema changes etc).

    Excellent stuff,

    John.

  2. Patrick said

    Tyler,

    great tip how to access/restore an application when it gets accidentally deleted! Much better than a database restore.🙂

    Thanks for pointing that out.
    Patrick

  3. Nice tip Tyler!

    I would replace the when others exception as that would give a warning in 11g… If you’ve heard Tom Kyte speaking, you know what I mean😉

    Thanks,
    Dimitri

  4. tylermuth said

    Dimitri,

    My intention was to ignore the only exception I am expecting to get, but raise all others. I’ll ping Tom and see if he would write it differently.

    Tyler

  5. tylermuth said

    Dimitri,

    You were correct. After chatting with Tom, he pointed out that the “when others then raise” would mask the actual line number of the error. My intention was to make the code more readable by explicitly raising errors instead of allowing them to implicitly raise, but masking the line number of the error is a horrible trade-off for more readable code. Per your and Tom’s suggestion, I removed that line and simply added a comment to make the function of the exception block more clear.

    Thanks,
    Tyler

  6. Very nice idea indeed Tyler.

  7. Mark Lacaster said

    Hi Tyler

    Really enjoyed your post.
    Would you be interested in turning it into an article for AUSOUG’s Foresight magazine?

    Regards

    Mark

    AUSOUG QLD Branch President
    http://www.ausoug.org.au

  8. […] https://tylermuth.wordpress.com/2007/11/17/apex-flashback-sql-developer-undo/ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: