Hello Everyone,
Many times we runs into issues where the tablespace has not enabled the autoextend feature and it runs out of space. One can use below script to check, what is the tablespace size and how much free space available
SQL> SELECT Total.name "Tablespace Name",
2 nvl(Free_space, 0) Free_space,
3 nvl(total_space-Free_space, 0) Used_space,
4 total_space
5 FROM
6 (select tablespace_name, sum(bytes/1024/1024) Free_Space
7 from sys.dba_free_space
8 group by tablespace_name
9 ) Free,
10 (select b.name, sum(bytes/1024/1024) TOTAL_SPACE
11 from sys.v_$datafile a, sys.v_$tablespace B
12 where a.ts# = b.ts#
13 group by b.name
14 ) Total
15 WHERE Free.Tablespace_name(+) = Total.name
16 ORDER BY Total.name
17 /
Tablespace Name FREE_SPACE USED_SPACE TOTAL_SPACE
------------------------------ ---------- ---------- -----------
ARSYSTEMMONTRE 240.5 59.5 300
ARSYSTEMV 1868.625 131.375 2000
ARSYSTEMVIGNESH 244.0625 55.9375 300
ARUNDOTBS 8.6875 1.3125 10
ATRIUMWS 458.8125 41.1875 500
SYSAUX 17.8125 362.1875 380
SYSTEM 3.3125 586.6875 590
UNDOTBS1 981.375 18.625 1000
USERS 4.5625 .4375 5
9 rows selected.
Happy Learning!!
Vaibhav
