Check Tablespace, Add Table Space to Oracle from SQLPLUS

First, checking the tablespace usage.

Put this in an sh script. Edit the first line.

sqlplus -silent username/password <<-EOF

set linesize 100;
set pagesize 100;
column Tablespace format a20;

select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKNOWN'))
"Tablespace",
kbytes_alloc "Allocated MB",
kbytes_alloc-nvl(kbytes_free,0)
"Used MB",
nvl(kbytes_free,0) "Free MB",
((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc) "Used",
data_files "Data Files"
from ( select sum(bytes)/1024/1024
Kbytes_free,
max(bytes)/1024/1024
largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024/1024
Kbytes_alloc,
tablespace_name,
count(*) data_files
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) =
b.tablespace_name
and ('' is null or 
instr(lower(b.tablespace_name),lower('')) > 0)
order by 1
/
EOF

Now to add tablespace to the users tablespace, simply type the following command as sysadmin in sqlplus:

alter tablespace users 
add datafile '/export/home/oracle/oradata/oracle/users08.dbf' 
SIZE 2048M;

More info