Apex: flows_files.wwv_flow_file_objects$ aka APEX_APPLICATION_FILES
End of last year, we had a strange problem in our test environment while testing an internal application for maintaining our library of IT-related books.
When adding a new book title, we got from time to time a “page not found error” with a reference to wwv_flow.accept in the URL. Strangely enough, sometimes it worked, sometimes it didn’t ! And this same page always worked in the development environment.
At first we thought it was a time-out problem of the http-server. We checked the Apache log files, but didn’t find any trace of an error.
The day after another colleague had the same problem when he tried to import an apex-application in another workspace.
So, the problem wasn’t application specific anymore, it happened also when doing standard apex-functionality.
We gradually understood that it had something to do with file-handling.
When processing the page that creates a book-entry in our library application, one of the steps is the upload of an image with the book-cover. We detected that when uploading a small image (e.g. an icon) the page was treated as it should, with big files it caused a problem.
So, we decided to run an audit of the database. Therefore we used Plato a free, handy tool for auditing and tuning an oracle database, developed by one of our Cronos colleagues — btw. you can donwnload the utility from dba-village –.
After running the script, we noticed the following warning in red !
So the problem was that the tablespace in which the schema FLOWS_FILES was created could not allocate extra space because the Maximum Size for auto extension (set to 200 Mb) was almost reached.
If we would have verified at day-1 the standard Oracle Alert file located on the database server, the problem would be clear immediately. Afterwards we noticed that for every attempt that caused load-error, there is an entry in that log file saying: “ORA-1691: unable to extend lobsegment flows_files.SYS_LOB0000053172C00017$$ in tablespace HTMLDB_FILES.”
To resolve the problem we could have decided to extent the tablespace, and problem would be fixed; but before doing that we wondered why that tablespace had reached those limits.
FLOWS_FILES is one of the 3 standard schema’s that are created when installing Apex. This schema contains only 1 table: WWV_FLOW_FILE_OBJECTS$. On top of this table are the views/synonyms HTMLDB_APPLICATION_FILES and APEX_APPLICATION_FILES created in THE FLOWS_020200 schema. This table contains ALL files that are uploaded in Apex, be it via a custom application, or be it via the Apex Development Environment.
We investigated the WWV_FLOW_FILE_OBJECTS$ table and we noticed that the table contained many and many entries that shouldn’t be there anymore.
The main reason was that some of the developers forgot to delete the (intermediate) files from APEX_APPLICATION_FILES once it was copied into their target table. So, we isolated the obsolete entries and deleted those records directly via SQL.
Another reason was that some people didn’t have the habitude to clean the Export Repository from time to time.
One of the coming days I will blog on the query we used to examine that table and on our findings related to the content of the table.