Oracle Tablespace not using 100% of available space

oracletablespaces

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

Best Answer

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.

Related Question