Tablespace-Alerts-activity

 to check the tablespace size (used and free):

sql> select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space", round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files where tablespace_name like '%&1%' group by tablespace_name) a, (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space where tablespace_name like '%&1%' group by tablespace_name) b Where a.tbl=b.tblsp;

To check How many datafiles there in a tablespace:

sql> col FILE_NAME for a40;

      set lines 200;

      select TABLESPACE_NAME,FILE_NAME,bytes/1024/1024,maxbytes/1024/1024,STATUS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME like '%&1%' order by file_name;

To see whether datafile existed or not:

col FILE_NAME for a50;

set lines 200;

select 

TABLESPACE_NAME,FILE_NAME,bytes/1024/1024,maxbytes/1024/1024,STATUS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME like 'DATA' and FILE_NAME like '%40%' order by file_name;