Oracle Tablespace not using 100% of available space

Question

About a year ago, we added a new tablespace to our Oracle 11g RAC database and we are now using all of the space and are running out of room occasionally. I check on the space available with this query:

SELECT
 df.tablespace_name TABLESPACE_NAME,
 totalusedspace USED_MB,
 (df.totalspace - tu.totalusedspace) FREE_MB,
 df.totalspace TOTAL_MB,
 ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) PCT_FREE,
 ROUND(100 * ( (df.MaxMegBytes-df.TotalSpace)/df.MaxMegBytes)) PCT_ROOM
FROM
(SELECT
 tablespace_name,
 ROUND(SUM(bytes) / 1048576) TotalSpace,
 ROUND(SUM(maxbytes) /(1024*1024)) MaxMegBytes
 FROM dba_data_files
 WHERE tablespace_name like 'MY_TABLESPACES%'
 GROUP BY tablespace_name) df,
(SELECT
 ROUND(SUM(bytes)/(1024*1024)) totalusedspace, tablespace_name
 FROM dba_segments
 GROUP BY tablespace_name) tu
WHERE
 df.tablespace_name = tu.tablespace_name and df.totalspace <>0
ORDER BY
 df.tablespace_name;

and it reports:

TABLESPACE_NAME USED_MB FREE_MB TOTAL_MB PCT_FREE PCT_ROOM
------------------------------ ---------- ---------- ---------- ---------- ----------
MOB_BIGDATA3 47486837 1543307 49030144 3 6

When we were first adding data to this tablespace, the total MB was creeping up and the Percent Free was creeping down, except we are now stuck at 6%. The Free MB can go to zero and the percent room stays at 6%.

I should add that the other tablespaces have zero percent room.

Could anybody suggest what to do to use this last bit of space?

Update: It just happens that in my case, there is no need to sum the values since there is only one per tablespace. With this query:

SELECT
 tablespace_name,
 bytes TotalSpace,
 maxbytes MaxBytes,
 maxbytes -bytes room,
 100*(maxbytes -bytes)/maxbytes percent_Room
 FROM dba_data_files
 WHERE tablespace_name like 'MY_TABL%';

I just got:

TABLESPACE_NAME TOTALSPACE MAXBYTES ROOM PERCENT_ROOM
------------------ -------------------- -------------------- ------------------- ------------
MY_TABLESPA3 51,411,832,274,944 54,975,581,388,800 3,563,749,113,856 6.48242

• eckes: This might be due to fragmentation. Is this a uniform allocation tablespace or Lmt Assm? I would not worry again a gig and a half. Your work time is more expensive and it’s certainly not a good idea to work with this small margin of error.
• user1683793: Look closely. That's six percent of 51 TB, about 3 TB. I would prefer to use as much of that as possible. Fragmentation? Good point. I will do some digging to see about that.

Answer 1

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.

• eckes: The pct_room sentence misses a noun, I guess it’s maximum allowed file size?