ODI 12c - database Free space check sql and resize datafile

Query to check the total space in mb, free space in mb and percent free for UNDO_9 tablespace.

Select

a.tablespace_name,

sum(a.bytes)/(1024*1024) total_space_MB,

round(b.free,2) Free_space_MB,

round(b.free/ (sum(a.bytes)/(1024*1024))* 100,2) percent_free

from dba_data_files a,

(Select tablespace_name, sum(bytes)/(1024*1024) free from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name ='UNDO_9'

group by a.tablespace_name, b.free;

Query to manually resize db datafile for tablespace undo_9

ALTER DATABSE DATAFILE '+Data/...path/datafile/undo_9...' RESIZE 800G

The above command resize the datafile to 800 gb.