Apex: Housekeeping Your Flow Files

In my previous blog, I explained the problem we encountered with flows_files.wwv_flow_file_objects$ aka APEX_APPLICATION_FILES.

I did some further investigations on the content of that table to 1) detect the ‘big size spenders’ and to 2) see whether or not there exist some orphan entries. I also wanted to know if we could write a delete statement to remove those obsolete records.

My research was not that easy …and not really satisfying…

This table is really used for all kinds of uploads of files into an Apex environment.

Apex uses different types of repositories:

  • an export repository
  • an image repository
  • a css repository
  • a text data load repository (accessible via Utilities)
  • workspace import/export repository (via Internal)
  • SQL Workshop script repository

The main columns I concentrated on are:

By executing following query (as user FLOWS_020200), we can see which (uploaded) files exist and how much space they are taking.

SELECT o.security_group_id,
FROM wwv_flow_file_objects$ o

By sorting on DOC_SIZE DESC, we could easily isolate those files that took a lot of space in the database. Based on the type, the developer was asked to clean up his Apex repositories.

For a lot of entries, we could not that easy map to an existing repository.
We noticed very often that the value of FLOW_ID equals to 0.

At first we thought that we could remove all those records. When we uploaded a file via our custom application and did not delete it from the APEX_APPLICATION_FILES view, the entry remained in the files-table with flow_id = 0.

On the other hand when browsing the data in the table, we discovered based on the filename and mime type that workspace images and uploaded css-files are also stored that way.

And in some cases, not only the flow_id = 0, but also the security_group_id = 0.

Based on the filename and the content we could for almost 100% certainty say that all those entries were obsolete and could be deleted. We used the following statement:

DELETE FROM wwv_flow_file_objects$
WHERE security_group_id = 0
AND flow_id = 0
AND file_type IS NULL

The cleaning of other entries we did on a record-by-record base.

Lessons learned

  • when uploading a document/an image into your custom application ALWAYS delete the files in the apex_application_files when you have transferred it to your own table
  • check on a regular base your oracle alert file
  • clear the different repositories within Apex when you do not longer need that file !
  • when deleting manually records (check, double check and take a backup)


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

About Jan Huyzentruyt