Note: These scripts runs with SYSDBA privileges
cpu_usage.sql – Show CPU usage
select a.sid,spid,status,substr(a.program,1,10) prog,a.terminal,osuser,value/60/100 value
from v$session a,
v$process b,
v$sesstat c
where c.statistic# = 12 and c.sid = a.sid and a.paddr = b.addr order by value desc
datafilesshow.sql – Show database datafiles
select tablespace_name,file_name,bytes from DBA_DATA_FILES order by tablespace_name, file_name
dictcacheshow.sql – Show dictionary cache
select sum(gets) "Dict. Gets", sum(getmisses) " Get Misses", sum(getmisses) /sum(gets)*100 "Ratio (Ideal < 15%)" from v$rowcache
sysstat.sql – Show system statistics
select * from v$sysstat order by class,statistic#
showusers.sql – Show all users;
select * from all_users;