TableSpace / Storage Management Queries

REFERENCE:
The great PSOUG library on data files: http://www.psoug.org/reference/datafiles.html

Select all tablespaces with 10% or less free space: http://dba.ipbhost.com/lofiversion/index.php/t4461.html

ORACLE METADATA VIEWS:
*) Data file related queries:
--raw query with all data on data files
select * from MGMT$DB_DATAFILES

--polished query with calc cols on free space
select file_name,tablespace_name,status,autoextensible,max_file_size/(1024*1024) max_size_mb,round(file_size/max_file_size,2) pct_full, round(1-file_size/max_file_size,2) pct_free
from MGMT$DB_DATAFILES
order by tablespace_name,file_name;


SOLUTIONS:
*) Data files:
--resize an existing data file:
alter database datafile '/u01/oradata/instance/file01.dbf' resize 1024MB;

--add a new data file to an existing tablespace:
alter tablespace add datafile '/u01/oradata/instance/file01.dbf' size 1024M autoextend off maxsize 1024M;