APEX and Tablespaces

Adrian, an internal employee, recently sent me the following issue/question:

I am the administrator and owner of an internal APEX workspace. Through time I made multiple requests to increase the storage size of this workspace each time by 10 MB. All these requests got approved and as a result the total table space should have been of at least 80-100 MB. However, it is of only 41 MB.

To see the current table space I went to Administration > Monitor Activity > Report Tablespace Utilization (popup) and I got the following report:

Tablespace Name	Bytes	Amount Used	Amount Free	Percentage Used
APEX_1193421002954380607	43,057,152	40,697,856	2,359,296	94.52

I then requested 10 additional MB to be added and the request was immediately approved. Then, I extracted again the above report, but the exact same values appear (total tablespace – 41MB) so the approved increase of 10 MB is not visible.

Could you please indicate if there is a limit of table space that I can request, if this is a bug or if I am no (sic) looking in the right place?

Good question. Adrian stumbled upon a bug in APEX, but this topic deserves some further explanation of how tablespaces are created in Oracle Application Express.

When requesting or creating a new workspace in Oracle Application Express, you can choose to associate the workspace with an existing schema (one that you or a DBA may have created beforehand), or you can ask for Application Express to provision a new database user and associated tablespace. This post is centered on the latter scenario - where APEX provisions the tablespace and user.

The actual location of the tablespace's associated data file is determined by:

1. If Oracle Managed Files are in use, then the name of the datafile and location are determined by the DBA via the OMF setting.
   
   
2. If Oracle Managed Files are not in use, then the datafile is created in the same physical database file system directory where Application Express is installed. For example, if Application Express is installed in the SYSAUX tablespace, and the location of a datafile for the SYSAUX tablespace is /d1/oracle/oradata/mydb/sysaux01.dbf, then the files for the newly provisioned tablespaces will also be created in /d1/oracle/oradata/mydb.

In Application Express 3.2.1 and earlier, the name of the tablespace is FLOW_x and the datafile is named FLOW_y.dbf, where x and y are an integer. In Application Express 4.0 and later, the name of the tablespace follows the pattern of APEX_x and data file name of APEX_y.dbf. In both APEX 3.2.1 and earlier and APEX 4.0, obviously, the data file name is generated by OMF when Oracle Managed Files is in use.

So how big will these files and tablespaces be when initially created? Good question. In APEX 3.2.1 and earlier, the data file for the provisioned tablespace is as big as the "storage request" for the workspace. If someone signed up for a workspace and requested a 100 MB workspace, APEX would create a tablespace and associated datafile of exactly 100 MB. The datafile of this tablespace would not autoextend. The actual size of the data file on the file system would be 100 MB. In our hosting of Application Express over the years, we learned two important facts:

1. People will always request the maximum permitted. If you let them choose 1 GB, they'll choose 1 GB even if they're only going to store 100 rows in a single table.
   
   
2. Most of the tablespaces on our hosted instances were full of "air" and occupied a lot of disk space unnecessarily.

In Application Express 4.0, we got a bit smarter. There is now an internal instance administration setting named Auto Extend Tablespaces, and in all APEX 4.0 and later instances, this is enabled by default. Now when a tablespace is created, the associated data file is set to autoextend up to a maximum of the requested size. The initial size of the data file will be 10% of the requested size, and autoextend up 10% at a time. So if you request a 100 MB workspace, the initial data file will be roughly 10 MB and autoextend 10 MB at a time up to 100 MB.

Workspace administrators can request additional storage, and in APEX 3.2.1 and earlier, when this type of request is processed, the tablespace data file is simply resized by the requested amount. But in APEX 4.0 and later, if the data file is set to autoextend, then when processing this type of request, the maximum size of the data file is adjusted but the data file itself is not increased in size until it needs to be (via the normal auto extend action of a tablespace).

So now getting back to Adrian's initial question, he stumbled upon a bug in Application Express 4.0. The report he was executing in Application Express showed the physical allocated size of the tablespace data file and did not take into account that the data file itself could grow. To prove this, I ran the following query:

SQL> select bytes, autoextensible, maxbytes 
       from dba_data_files 
      where tablespace_name = 'APEX_1193421002954380607';

     BYTES AUT   MAXBYTES
---------- --- ----------
  45154304 YES  320339968

Sure enough - Adrian's data file is only approximately 45 MB and can grow up to roughly 320 MB. Thus, I can conclude with the following:

1. This is a simple bug which will be fixed in Oracle Application Express 4.1.
   
2. Adrian - you can take a break from making so many workspace storage requests, please.