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