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…
Apex uses different types of repositories: 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.
This table is really used for all kinds of uploads of files into an Apex environment.
SELECT o.security_group_id,
o.flow_id,
o.filename,
o.doc_size,
o.mime_type,
o.file_type,
o.content_type
FROM wwv_flow_file_objects$ o
ORDER BY 1,2
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)
