Recover accidentally deleted APEX components: Flashback Export
| Publish Date
|| APEX version
|| Database version
Accidentally deleting an Application Express page or shared component (or even a complete application)
can be a real pain for the developer. Even a simple component, like an LOV, might rely on a complex
SQL query, which is hard to recover manually.
When you now think "Oh, no problem, the operations department regularly backs up the APEX database",
hold on a moment: What would that mean? The DBA would have to recover the (physical) database backup
(to another system, because the APEX database must remain up and running).
Then this temporary database has to be started, one has to log into the Application Express workspace
and export the application. It's obvious that this is not only a lengthy but also a very
The good news is: If you act quickly, you can recover your application or component
yourself. The Flashback Option within the Application Express
export functionality, helps you with this. You can use it for Application, Page or Component exports.
Use the "Flashback Option" while exporting an APEX application or component
This functionality relies on the Flashback Query technology, which
is part of the Oracle database since Oracle9i back in 2001. Flashback Query allows to use the
AS OF clause in a SQL query executing that query "in the past".
Flashback Query gets this history data from the Undo Tablespace -
more detailed information about the technical background can be found in the
Oracle documentation: Overview of Oracle Flashback Technology.
Flashback query is limited to the historic data in the Undo Tablespace -
on a database installation on a private laptop, flashback queries can get back several hours into
the past - on a production system or on a central development instance, one should not expect more
than five to 15 minutes. So, to recover your accidentally deleted component using that technology,
you should act quickly.
The first thought, after the page has been deleted, is to navigate to another page and, from there,
navigate to Export Page. But as the following screenshot shows,
the page to be exported is chosen from a select list. The deleted page is, of course,
not part of that list any more.
A select list allows to choose the page to be exported
But there is a simple trick for that: Just create a new page
with the same page number as the
deleted one. Create it as a Blank Page and accept all defaults -
you want to get through this wizard as fast as possible. When the page has been created, navigate to
Export Page again,
choose the new "dummy" page, set the Flashback Time and export.
Export the new dummy page with the flashback option
After that, navigate to Import and import the page you just have
exported, back into the application.
Import the exported page, back into the application
Application Express will then tell you that the page you are about to import already exists.
Confirm to replace it.
Confirm to replace the dummy page with the imported page
After that, you have got your old page back - all regions are recovered.
If you also deleted Shared Components (list or breadcrumb entries)
together with your application page, these will not be recovered with this approach. To recover these
as well, it's best to export the whole application with the
flashback option - not just one page.
Export the whole application with the flashback option
You have two alternatives for importing your application back: One option is to import with the ID of the
existing application. You will again be prompted to confirm the application replacement. After that,
your application will be reset to the state it had at the time you have chosen with the flashback option.
All lost components are recovered. But intended changes will also be rolled back - which might not be
exactly what you wanted.
The second option is to import the application with a new ID. You will then have your original application
and a copy, as it was at flashback time - the "flashback application". Components
can be looked up in the flashback application, and re-created in the original application - either
manually or by creating components as a copy of an existing component.
Create a component - as a copy from the flashback application
Accidentally deleting a component in Application Express is not necessarily a disaster. If you
recognize your error quickly, it's very easy to get your work back - all you have to do is an application
or component export with the flashback option.
The optimal solution is, of course, to do application exports automatically at a regular schedule. A
daily job could export all applications and check the files into a source control system like
Subversion, CVS or Git. Developers then even don't need to be quick when they lost some work; they
just look up the last version from source control and import it back. That would allow to recover
components which have been deleted days or even months before.