Oracle Tablespace not using 100% of available space
I do not see any issue other than using an unnecessary an incorrect query for monitoring instead of DBA_TABLESPACE_USAGE_METRICS and possibly misunderstanding its output.

PCT_FREE is the ratio of the free space compared to the allocated size.

PCT_ROOM is the ratio of the free space compared to the.

When PCT_FREE reaches 0, that means all your datafiles are full of data up to their allocated size. The autoextensible datafiles in the tablespace can still grow 6% up to maxbytes. At this point, if more data is inserted, PCT_FREE will remain 0 and PCT_ROOM will start decreasing. But keep in mind, 1% here is 490 GB, so it could take a while for PCT_ROOM to move from 6 to 5.

The query is incorrect, because a datafile that is not autoextensible will have its maxbytes value at 0. This part simply does not account for that:

ROUND(SUM(maxbytes) /(1024*1024)) MaxMegBytes


It should be:

ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) /(1024*1024)) MaxMegBytes


With non-autoextensible datafiles, the above query returns inaccurate results, it may even return negative values.

If the concept of autoextensible datafiles is confusing, just simply preallocate all your datafiles to the maximum size. That way PCT_ROOM will be 0, and PCT_FREE will represent the actual free space percentage. You can even disable autoextend on the datafiles after doing this, but for that, you need to fix the query as I have point out above.

Or just simply throw all this out and use DBA_TABLESPACE_USAGE_METRICS. Also, apply the latest patches as this view does not work a 100% correctly without them.