Oracle Database Tablespace Related Queries

Find data file details like Used & Free Size, Auto Extend, Allocated, and Maximum Size:

SELECT df.file_id,
       df.file_name,
       df.tablespace_name,
       ROUND(df.bytes/1024/1024/1024, 2) AS allocated_gb,
       ROUND(NVL(fs.free_bytes, 0)/1024/1024/1024, 2) AS free_gb,
       ROUND((df.bytes - NVL(fs.free_bytes, 0))/1024/1024/1024, 2) AS used_gb,
       ROUND((df.bytes - NVL(fs.free_bytes, 0)) / df.bytes * 100, 2) AS pct_used,
       df.autoextensible,
       ROUND(df.maxbytes/1024/1024/1024, 2) AS max_size_gb
FROM   dba_data_files df
       LEFT JOIN (
         SELECT file_id, SUM(bytes) AS free_bytes
         FROM   dba_free_space
         GROUP  BY file_id
       ) fs ON df.file_id = fs.file_id
WHERE  df.tablespace_name = 'TABLESPACE_NAME'
ORDER  BY allocated_gb DESC;

Free Space in Auto Extend Tablespace:

select
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

Increase Datafile Size with Auto Extend:

alter database datafile '+XXXX/DATAFILE/datafile01.1100.11003438874' AUTOEXTEND ON NEXT 104857600 MAXSIZE 20480M;

Add New Datafile:

alter tablespace TABLESPACE_NAME add datafile '+XXXX' size 1G AUTOEXTEND ON NEXT 1G MAXSIZE 10240M;

Diskgroup Space Details:

SELECT name,Sector_size,Block_size,Allocation_unit_Size/1024,state,type, total_mb/1024 as TOTAL_GB, free_mb/1024 as FREE_GB, free_mb/total_mb*100 as Free_percentage FROM v$asm_diskgroup;