Technology

SYSAUX Cleanup Can Delete Your APEX SQL Workshop Scripts

By · · Updated

APEX stores saved SQL Workshop scripts in FLOWS_FILES, inside SYSAUX. An age-based purge deletes them without warning. Why it happens and how to prevent it.

SYSAUX Cleanup Can Delete Your APEX SQL Workshop Scripts

Oracle APEX stores every saved SQL Workshop script in FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$, a repository table that lives in the SYSAUX tablespace. When SYSAUX fills up and a DBA purges rows from that table by age, years of working developer scripts are deleted alongside the disposable CSV uploads the purge was aimed at. APEX gives no warning, and there is no undo inside the tool.

At a glance:

  • Saved SQL scripts, static application files and end-user attachments all share one table in SYSAUX.
  • An age-based purge cannot tell a baseline production script from a temporary export.
  • Reclaiming space with ALTER TABLE ... MOVE LOB leaves APEX imports broken until indexes are rebuilt.
  • Flashback Query is not a dependable recovery path once the rows are gone.

Where does Oracle APEX store SQL Workshop scripts?

APEX keeps saved SQL Workshop scripts, static application assets and end-user file attachments inside a single internal repository table: FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$. That table is part of the APEX engine, and the APEX engine schemas live in SYSAUX.

This matters because SYSAUX is the tablespace DBAs reach for first when they need space back. The usual top consumers are SM/AWR and SM/ADVISOR, and FLOWS_FILES frequently ranks alongside them on an APEX-heavy database. Purging the first two is routine housekeeping. Purging the third destroys developer work.

Why does an age-based purge delete active scripts?

Because APEX treats a saved SQL script as a workspace binary object, not as schema source code. The row carries a filename and a creation date, and very little that distinguishes intent.

A cleanup rule such as “delete everything older than 12 months” therefore cannot separate an obsolete CSV a developer uploaded once from a baseline production deployment script written three years ago and still run every quarter. Both are old rows in the same table. The purge removes both.

The scripts are also invisible to the checks a DBA would normally run. They are not in DBA_SOURCE, they are not objects in an application schema, and a schema-level export of the application schema does not contain them.

What else breaks during a SYSAUX reclaim?

Two secondary failures show up often enough to plan for.

Invalid indexes (ORA-01502). Deleting rows frees space inside the segment but does not lower the High Water Mark, so administrators commonly follow the delete with an ALTER TABLE ... MOVE LOB to actually return space to SYSAUX. That move invalidates the associated table indexes, including WWV_FLOW_FILE_OBJ_PK. Until those indexes are explicitly rebuilt, APEX application imports fail across the entire environment, on every workspace, not just the one being cleaned.

Flashback Query failures (ORA-01555 and ORA-01466). The instinct after an accidental delete is to flash the table back. In practice this often fails, either because the recovery window crosses a DDL boundary such as the LOB move, or because heavy UNDO consumption on a busy database has already overwritten what the query needs. Administrators are then pushed toward Data Pump import or RMAN point-in-time table recovery instead.

How do you clean up SYSAUX without losing APEX scripts?

Take a safety snapshot first. Before any DELETE or TRUNCATE against an APEX framework table, create a temporary in-database copy:

SQL
CREATE TABLE flows_files.wwv_flow_file_objects_bak 
AS SELECT * FROM flows_files.wwv_flow_file_objects$;

This costs a few minutes and turns an unrecoverable mistake into a simple insert back.

Verify indexes after any LOB move. Query DBA_INDEXES for UNUSABLE status immediately after the reorganization and rebuild anything that comes back, before you hand the environment back to developers. Discovering this through a failed application import days later wastes far more time than the check.

Export FLOWS_FILES on a schedule. A routine Data Pump schema export gives you a fast point-in-time recovery path that does not require restoring the whole database:

Bash
expdp \'sys/password as sysdba\' \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=apex_files_backup.dmp \
  SCHEMAS=FLOWS_FILES

Run it alongside your existing APEX application backup routine so the files repository is covered by the same cadence as the applications themselves.

How do you recover scripts that are already deleted?

Work through these in order.

  1. The snapshot table, if you took one. Insert the missing rows back and you are done.
  2. Flashback Query, attempted immediately. It is worth trying, but expect ORA-01555 or ORA-01466 if a LOB move happened after the delete or if UNDO has churned.
  3. Data Pump import from the most recent FLOWS_FILES export, restoring the table into a staging schema and copying back only the rows you need.
  4. RMAN point-in-time table recovery, which reconstructs the table as of a chosen SCN without restoring the production database in place.

If none of those exist, the scripts are gone. There is no APEX-side recycle bin for SQL Workshop content. That is the case worth avoiding, and the reason the snapshot in the previous section is not optional.

How should APEX teams store code instead?

The deeper fix is architectural. SQL Workshop is a convenient place to run a query. It is not a source control system, and it is not backed like one.

Move logic into compiled packages. Business logic and repeated SQL belong in PL/SQL packages owned by your application schema, for example APEXAPPS. Compiled source lives in DBA_SOURCE inside a data tablespace, well away from anything a SYSAUX purge will touch.

Keep DDL in Git. Manage schema change as versioned files and deploy them with Oracle SQLcl or Liquibase. A script in a repository survives a tablespace incident, a workspace deletion and a staff change equally well.

Treat SQL Workshop as scratch space. Ad-hoc queries, one-off investigations, quick exports. If losing it would hurt, it should not be the only copy.

Teams running APEX on infrastructure they do not administer themselves should confirm what their provider actually backs up. On Revion’s Oracle APEX hosting, the files repository is covered by the same schedule as the databases, and restoring an APEX application does not depend on the customer having kept their own copy.

Frequently asked questions

Where are Oracle APEX SQL Workshop scripts stored?

In the table FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$, which is part of the APEX engine and resides in the SYSAUX tablespace. The same table also holds static application files and end-user attachments.

Will deleting old rows from WWV_FLOW_FILE_OBJECTS$ delete saved SQL scripts?

Yes. Any saved script older than the age cutoff is deleted along with everything else that matches. APEX stores scripts as workspace binary objects, so the table offers no reliable way to exclude them from an age-based purge.

Why did APEX application imports stop working after I reclaimed SYSAUX space?

An ALTER TABLE ... MOVE LOB on WWV_FLOW_FILE_OBJECTS$ invalidates the table’s indexes, including WWV_FLOW_FILE_OBJ_PK, which produces ORA-01502. Imports fail across every workspace on the instance until those indexes are rebuilt.

Can Flashback Query recover deleted APEX SQL scripts?

Sometimes, but it is unreliable here. It commonly fails with ORA-01555 or ORA-01466 when the recovery window crosses a DDL boundary such as a LOB move, or when heavy UNDO consumption has overwritten the required data. Data Pump import or RMAN point-in-time table recovery is the dependable path.

How do I protect SQL Workshop scripts going forward?

Snapshot WWV_FLOW_FILE_OBJECTS$ into a backup table before any delete, schedule Data Pump exports of the FLOWS_FILES schema, and move anything you genuinely depend on into compiled PL/SQL packages and a Git repository rather than leaving it in SQL Workshop.