Useful sql scripts for Oracle DBA


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;

Similar Posts:


How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Leave a Comment

Your email address will not be published.

Scroll to Top