Oracle database health checks

Checking for any down data files:

select file_name, tablespace_name, status, bytes/1000000 as mb, maxbytes/1000000 as mb_max 
from dba_data_files 
where status<>'AVAILABLE';

Checking for any down control files:

select name, status
from v$controlfile 
where status is not null;

Checking for any down tablespaces:

select tablespace_name, status, contents, extent_management
from dba_tablespaces
where status<>'ONLINE';

Checking to see if a tablespace is getting close to max size (less than 10% free):

select total.name as tblspace,
  nvl(free_space, 0) as free,
  nvl(total_space-free_space, 0) as used,
  total_space,
  round(nvl(free_space,0)/total_space,2)*100 as pct_free
from 
  (
    select tablespace_name, sum(bytes/1024/1024) as free_Space 
    from sys.dba_free_space 
    group by tablespace_name
  ) free, 
  (
    select b.name, sum(bytes/1024/1024) as total_space
    from sys.v_$datafile a, sys.v_$tablespace b 
    where a.ts# = b.ts#
    group by b.name
  ) total 
where free.tablespace_name(+) = total.name 
and total.name='TABLESPACE_NAME_HERE' 
and round(nvl(free_space, 0)/total_space,2)<=0.10;

Suspicious of someone stealing your system passwords and logging in remotely? Use this to check for system logins from machines you do not expect.

select username, osuser, program, machine 
from sys.v_$session 
where upper(machine) in('LIST_VALID_MACHINES_HERE','DBA_PC','BOSS_PC') 
and upper(username) in('SYS','SYSTEM');