DB : Tablespace Free Size + Total Size + Datafiles in TBS + add datafile

Solution:

------ * FREE SIZE *---------
select tablespace_name,round(sum(bytes/1024/1024)) from dba_free_space where tablespace_name in('APPS_TS_SEED')group by tablespace_name order by tablespace_name;

------ * TOTAL SIZE *--------
select tablespace_name,round(sum(bytes/1024/1024)) from dba_data_files where tablespace_name in('APPS_TS_SEED')group by tablespace_name order by tablespace_name;

------ * DATAFILES *--------
column name format a40

SELECT name, bytes/1024/1024 FROM v$datafile WHERE ts# = (SELECT ts# FROM v$tablespace WHERE name = 'APPS_TS_SEED');

------ * Resize DATAFILE *--------

alter database datafile '/files/VIS/db/apps_st/data/reference1.dbf' resize 1024M;

------ * ADD DATAFILE *--------

alter tablespace SYSAUX add datafile '/files/VIS/db/apps_st/data/sysaux01.dbf' size 2000M;

------ * Tablespace free + used + total Size *--------
set lines 200
set pages 100
column "Tablespace" format a20
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999

select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
((df.totalspace - fs.freespace)*100/ df.totalspace)"Used Percentage",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name
order by 6;


------ * Database Used Space + DB Size + Free Size *--------

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select     round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,     round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
     round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,     round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select     bytes
     from     v$datafile
     union     all
     select     bytes
     from      v$tempfile
     union      all
     select      bytes
     from      v$log) used
,     (select sum(bytes) as p
     from dba_free_space) free
group by free.p
/