How to find free space and resize and autoextend of tablespace , oracle database 12c/19c with autoextend enabled

How to find free space and resize and autoextend of tablespace , oracle database 12c/19c with autoextend enabled  

Use below SQL to find free space of all tablespaces:

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;

Use below SQL to find datafiles of a particular tablespace:

set line 200

col FILE_NAME for a65

select FILE_NAME,BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from dba_data_FILEs where TABLESPACE_NAME='&tbs';

Use below SQL to increase autoextend size of datafile of above tablespace:

SQL> alter database datafile '/<datafile path>/datafilename.dbf' autoextend on maxsize 14G; -->you can set max 30GB depending on yous OS limit (linux supports till 30GB max size)